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:
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
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.
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.
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.
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
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')
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,
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!
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
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 .......
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.
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.
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.
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
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’ “
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.
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" />
[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?
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
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
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.
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?