Set A Attribute To Random Values In A Given List
Jul 20, 2005
Hi,
I like to have a SQL script which could update a table to set one attribute
to a random value picked from a given list. The prototyping code is as
below:
select @value_list = ('John', 'David', 'Mathew', 'Paul')
....
update EMP set emp_name = @random_name
where ...
where random name is randomly got from the given list. Of course there will
be a cursor so that different row may have different random name but not
necessary unique. Also the attribute and the list could be any valid common
SQL data types
Thanks!
Ximing
View 1 Replies
ADVERTISEMENT
Jan 9, 2004
I have a customer that wants to be able to generate a random list...
-----------------------
The list is case numbers, and I can get those easy enough by saying
SELECT
dbo.table_case.id_number
FROM
dbo.table_case
Now, what I want to do is take that list and extract 1 out of every 1000, picking out random id_numbers to display.
Is this something I can do via a SQL query?
Thanks,
Dan
View 1 Replies
View Related
Dec 11, 2006
Hi -
I am new to TSQL and any help on the following would be appreciated.
I have a table that looks like this
security Message
2345 Good
2346 Good
uoas Bad
fsaf More
sdad More
asdd More
ndgs More
I have to pull in a list of distinct messages with a count and any 3 cusips that belong to the message.
I have written, something like this
SELECT MESSAGE_TEXT, COUNT(*) AS Expr1, MIN(security) AS Expr2, MAX(SECURITY) AS Expr3
FROM dbo.DerivedSIACDataMessages
GROUP BY MESSAGE_TEXT
ORDER BY COUNT(*) DESC
However, this lets me pull only 2 securities per message and I want to pull in 4 cusips.
Any help would be apprecioated
Thanks.
View 4 Replies
View Related
Dec 7, 2007
I have a units conversion table. I'd like to populate the parameter drop down with the name of the unit and have the value selected be the numrical value found in a different attribute.
Here is my current MDX:
WITH MEMBER [Measures].[ParameterCaption] AS '[Dim Units].[Units].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Dim Units].[Units].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Dim Units].[Units].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Dim Units].[Units].ALLMEMBERS ON ROWS FROM ( SELECT ( STRTOSET(@DimSalesTypeDescription, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW])
Units Parameter Caption Parameter Value Parameter Level
(null) All [Dim Units].[Units].[All] 0
Gross Ton Gross Ton [Dim Units].[Units].&[Gross Ton] 1
Hundred Weight Hundred Weight [Dim Units].[Units].&[Hundred Weight] 1
I can't seem to get different attribute to show up for ParameterValue.
Also, is there a way to NOT include the "all" member? For what I am trying to do it would have no meaning.
Thanks.
View 2 Replies
View Related
Jun 15, 2015
So at the moment, I don't have a function by the name CONCATENATE. What I like to do is to list all those different values that go with a single CASE_ID to appear as a a comma separate list. You might have a better way of doing without even writing a function
So the output would look like :
CASE_ID VARIABLE
=====================
1 [ABC],[HDR],[GHHHHH]
2 [ABCSS],[CCHDR],[XXGHHVVVHHH],[KKKJU],[KLK]
SELECT
preop.Case_ID,
dbo.Concatenate( '[' + CAST(preop.value_text AS VARCHAR) + ']' ) as variable
FROM
dbo.TBL_Preop preop
WHERE
preop.Deleted_CD = 0
GROUP BY
preop.Case_ID
View 8 Replies
View Related
Nov 17, 2015
I need the Rate to be of a value from 1-5 to indicate the stars for Rating attribute in my database.
View 2 Replies
View Related
Dec 24, 2001
Is there a way to write an SQL statement to choose random values from a particular select statement..for example:
select * from address
I was thinking, if there was a way to use an include statement for it like:
select * from address where id IN ('generates some random values')
thanks in advance
View 2 Replies
View Related
Jan 1, 2005
Hello guys , I am new here,
Well, I am moving my greetings script from MS Access to MS SQL, here is what I was using in MS Access.
See this screenshot: Click Here
Well, I've imported the data from access database, but I don't know how to do the AutoNumber in MS SQL,
Also I don't know how can I add that Random thing *See the arrow in the screenshot*
Thanks in advance
View 2 Replies
View Related
Mar 8, 2005
I have a table with 3 fields SiteID (PK), SiteCode, SiteName.
For each SiteCode I have several rows with different SiteName.
For example: 1 327 a
2 327 b
3 327 c
4 328 aa
5 328 bb......
I need to retrieve each time SiteCode with random SiteName.
For example: 327 b , 327 a , 327 c
328 aa 328 bb 328 aa.
I need it very urgent, so any ideas will be very helpful. Thanks to all,
View 1 Replies
View Related
Dec 4, 2007
Hello,
i want to anonymous data by multiplicating with a random generated value. After a little bit research i know that is only solvable with a script task, but i dont have seen a working example script. Does anybody knows where i can found a little scriptlet or post something? Thank you!
Jonas
View 5 Replies
View Related
Apr 16, 2015
I have a parent Child attribute in my dimension. I am currently displaying the correct ID value as the business wants. So now they can see the rollup of the ID(intOrgNodeID ) values.They would also like to see the same rollup of the Name (vcharOrgNodeName) for this ID.However they do not want it concatenated. They want to be able to see them separate.You cannot create two parent child attibutes in one dimension so not sure if there is some simple trick to make this work? It seems like there should be some simple trick for this.
My dimension table looks something like this
intdimOrgNodeID int Key (surreget key)
intOrgNodeID int (Actual ID)
intDimParentOrgNodeID
vcharOrgNodeName
In the Propertys I have set this below.
KeyColumns = tbldimOrgNode.intDimParentOrgNodeID
NameColumn = tbldimOrgNode.intOrgNodeID
View 8 Replies
View Related
Dec 14, 2007
Hi all!
I want to fill a table with random values. In each line should be a different value - "independet" from the value the row above.
what i tried didnt work, it always produced the same value.
Maybe you have an idea
Thankx in advance and greetings from Vienna
Landau
View 2 Replies
View Related
Jan 14, 2008
Hi I am using VS 2005 with SQL 2005.
I had a Datagrid and i Need to bind data into it.
I need to show Random Unique ID's based on the Parameter i pass.
I wrote select top 5 * from Employee order by NEWID()
for this i had created a Stored Procedure as
create Proc Demo(@N int)as beginselect top @N * from Employee order by NEWID()end
Its showing a syntax error near @N. How can i pass the parameter for Select Top @N * from .......
View 6 Replies
View Related
Feb 11, 2015
For a new project. I need to create random alphanumeric characters as primary key values when inserting a record.
eg: cmSbXsFE3l8
It can start from 4 digit characters and can grow to 6, 7 as required
The database will involve more than 10000 concurrent users.
I don't want to use guid or auto increment integer or sequence.
View 9 Replies
View Related
Oct 14, 2015
I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
View 2 Replies
View Related
Aug 10, 2015
I want to insert attibute att1 in field F1 . Value for this attribute is content of another field in this table (F2).
My query :
update MyTable
set F1.modify
('insert attribute att1 {sql:column("F2")} into (/ROOT/Node1)[1]')
Where F1 Is not null
But I get this error :
XML well-formedness check: Duplicate attribute 'att1'. Rewrite your XQuery so it returns well-formed XML.
How do I check the douplicate attribute ?
View 0 Replies
View Related
May 16, 2008
HI,
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
thanks
MJ
View 3 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Jul 20, 2005
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
View 1 Replies
View Related
Jun 21, 2006
Hi all. I need to create a select query in my program that will select from a list of values that are stored in a dataset. Let see this example:
selectcmd = "Select * from mytable where myfile =" ???????
cmd = New SqlCommand(selectCmd, da.SelectCommand.Connection)
The values I need to put on ????? are stored in a dataset. For example if the dataset is populated with the following values:
A
B
C
D
E
I would like to build a query like that:
selectcmd = "Select * from mytable where myfile = ‘A’ or ‘B’ or ‘C’ or ‘D’ or ‘E’ “
How can I do that?
Thanks.jsn
View 6 Replies
View Related
Apr 28, 2007
Dear Readers of this post,
I have a dropdownlist that is populated with an sqldatasource as follows:
SELECT [Project_ID], [Title] FROM [Projects] WHERE [Username] = @Username AND Hide ='false'
The Datavalue vield of the DDL is populated with the [Title].
When the user submits the form [including the value of the of the drop down list] i want to be able to add the Project ID and the Title Values into another database table.
any ideas
Sat
View 3 Replies
View Related
Nov 22, 2005
For a controlParameter in the ASP code, how do I retreive the selectedValue of the drop down list?Would this work?
<asp:controlParameter Name="InvoiceNumber" Type="String" ControlID="ddAdSize.Value" />
View 1 Replies
View Related
May 16, 2006
While experimenting I got this message:
[Flat File Source [212]] Error: The "output column "Column 0" (237)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 0" (237)" specifies failure on error. An error occurred on the specified object of the specified component"
I note under BIDS, Properties tab, that the Flat File is assigned an ID of 212. But is the number (237) and ID or an error value? How can I list all "ID's" used in a package?
TIA,
Barker
View 1 Replies
View Related
Oct 31, 2006
Hello I have a newbie question. If I have a table of the form:Table1{id, name} with the valuesid: 9 , name: test1,id: 7 , name: test2,id: 3 , name: test3,id: 15 , name: test4, id: 5 , name: test5,id: 13 , name: test6,.........If I have a list generated from user selection ( LIST{1, 7, 8, 15} ,) will I in a way be able to use this list in a query of the form, thus only having to make one query to the database: SELECT id, nameFROM Table1WHERE Table1.id in LIST Or is the solution to make multiple queries to the database, one for each member of the list, of the form:SELECT id, nameFROM Table1WHERE ID = @IDThanks in advance /dresen
View 4 Replies
View Related
Apr 20, 2006
I need to be able to order the results of a SELECT query by the order of a specific list of key IDs provided in the WHERE IN statement.
So my query looks like:
SELECT *
FROM TableName
WHERE KeyID IN (3,104,43,22,345)
ORDER BY ????
I need the results returned in the order provided in the IN list (3,104,43,22,345).
Thanks in advance!
pr0
View 5 Replies
View Related
Sep 27, 2006
I am allowing users to assign values in a table to field names in a table
for
EAV table transpostion
Does anyone know where I can find a list of ascii valeus or characters that sql server does not like in fieldnames.
IE (ticks, #, :, -,)
I need to allow the user to define fieldnames for values which then I will alter existing tables with that FieldName that they assign.
I know this is a nightmare but it is the contraint I am working under.
View 14 Replies
View Related
Jul 12, 2012
I would like to write a select statement where I specify a list of values in the 'Select' line, and would like the output to have one line for each element.
I tried using Case with no success.
For example:
Select a.id, a.timestamp, ('rowA','rowB') as 'Tag' from tableOne a where a.id = '1'
So the 'where' line would produce one row, however, the overall statement would produce two.
ID TimeStamp Tag
--------------------------------
1 2012-12-12 rowA
1 2012-12-12 rowB
View 4 Replies
View Related
Sep 7, 2012
My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2
then run an sql statement and should return you the following
Period
1
2
4
12
31
View 3 Replies
View Related
Apr 16, 2014
I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.
I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.
View 7 Replies
View Related
May 22, 2008
Hi, does anyone know a SQL statement that will take two DATETIME values and list all minutes in between them like so:
START VALUE: 5/22/2008 10:00
END VALUE: 5/22/2008 10:10
RESULT:
5/22/2008 10:00
5/22/2008 10:01
5/22/2008 10:02
5/22/2008 10:03
5/22/2008 10:04
5/22/2008 10:05
5/22/2008 10:06
5/22/2008 10:07
5/22/2008 10:08
5/22/2008 10:09
5/22/2008 10:10
Thanks!
View 8 Replies
View Related
Nov 3, 2014
select pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 24420
select distinct pr.birthdate, pr.operationdate from patientrecord pr
Total no.of rows = 23528
It seems there are some rows with same birthdate and operationdate.
I want to get the list of rows(with all columns) that has got same birthdate and operationdate
View 1 Replies
View Related
Feb 3, 2015
We're using MS Access 2010 as a frontend to an SQL server back-end. From Access, I can run read-only queries and pass-through queries. I'd like to use a local Access table as part of a join to server data. As a non-pass-through query, it's slow; about 5 min to join to 2 other tables.
I could use VBA to turn the local table into part of a pass-through query, with a large in() statement, or several where x='' ors but the local table may have 50000 entries in it. Is there a good or right way to pass this data in the query if I don't have write access to the server?
View 2 Replies
View Related
Mar 24, 2015
SELECT
c.CustomerId,
c.CustomerName,
co.OrderKey,
co.OrderNumber,
co.CustomerPO,
co.DueDate,
[code]....
sb.CarrierId data is Sec.
Carriers.ExcludeCarriers I have value as 'Sec,QB' list of values.
I am trying to display sb.Carrierid that is not equal to Carriers.excludeCarriers list of values. It fails now and displays sec values in resultset.
View 1 Replies
View Related
