How To Select From A List Of Values
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 Complete Forum Thread with Replies
Related Forum Messages:
Select Statement Using Multi-list Box Values For WHERE IN SQL Clause
I have a gridview that is based on the selection(s) in a listbox. The gridview renders fine if I only select one value from the listbox. I recive this error though when I select more that one value from the listbox: Syntax error converting the nvarchar value '4,1' to a column of data type int. If, however, I hard code 4,1 in place of @ListSelection (see below selectCommand WHERE and IN Clauses) the gridview renders perfectly. <asp:SqlDataSource ID="SqlDataSourceAll" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT dbo.Contacts.Title, dbo.Contacts.FirstName, dbo.Contacts.MI, dbo.Contacts.LastName, dbo.Contacts.Suffix, dbo.Contacts.Dear, dbo.Contacts.Honorific, dbo.Contacts.Address, dbo.Contacts.Address2, dbo.Contacts.City, dbo.Contacts.StateOrProvince, dbo.Contacts.PostalCode FROM dbo.Contacts INNER JOIN dbo.tblListSelection ON dbo.Contacts.ContactID = dbo.tblListSelection.contactID INNER JOIN dbo.ListDescriptions ON dbo.tblListSelection.selListID = dbo.ListDescriptions.ID WHERE (dbo.tblListSelection.selListID IN (@ListSelection)) AND (dbo.Contacts.StateOrProvince LIKE '%') ORDER BY dbo.Contacts.LastName"> <SelectParameters> <asp:Parameter Name="ListSelection" DefaultValue="1"/> </SelectParameters> </asp:SqlDataSource> The selListID column is type integer in the database. I'm using the ListBox1_selectedIndexChanged in the code behind like this where I've tried using setting my selectparameter using the label1.text value and the Requst.From(ListBox1.UniqueID) value with the same result: Protected Sub ListBox1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ListBox1.SelectedIndexChanged Dim Item As ListItem For Each Item In ListBox1.Items If Item.Selected Then If Label1.Text <> "" Then Label1.Text = Label1.Text + Item.Value + "," Else Label1.Text = Item.Value + "," End If End If Next Label1.Text = Label1.Text.TrimEnd(",") SqlDataSourceAll.SelectParameters("ListSelection").DefaultValue = Request.Form(ListBox1.UniqueID) End Sub What am I doing wrong here? Thanks!
View Replies !
Select List Contains More Items Than Insert List
I have a select list of fields that I need to select to get the results I need, however, I would like to insert only a chosen few of these fields into a table. I am getting the error, "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns." How can I do this? Insert Query: insert into tsi_payments (PPID, PTICKETNUM, PLINENUM, PAMOUNT, PPATPAY, PDEPOSITDATE, PENTRYDATE, PHCPCCODE) SELECT DISTINCT tri_IDENT.IDA AS PPID, tri_Ldg_Tran.CLM_ID AS PTicketNum, tri_ClaimChg.Line_No AS PLineNum, tri_Ldg_Tran.Tran_Amount AS PAmount, CASE WHEN tln_PaymentTypeMappings.PTMMarsPaymentTypeCode = 'PATPMT' THEN tri_ldg_tran.tran_amount * tln_PaymentTypeMappings.PTMMultiplier ELSE 0 END AS PPatPay, tri_Ldg_Tran.Create_Date AS PDepositDate, tri_Ldg_Tran.Tran_Date AS PEntryDate, tri_ClaimChg.Hsp_Code AS PHCPCCode, tri_Ldg_Tran.Adj_Type, tri_Ldg_Tran.PRS_ID, tri_Ldg_Tran.Create_Time, tri_Ldg_Tran.Adj_Group, tri_Ldg_Tran.Payer_ID, tri_Ldg_Tran.TRN_ID, tri_ClaimChg.Primary_Claim, tri_IDENT.Version FROM [AO2AO2].MARS_SYS.DBO.tln_PaymentTypeMappings tln_PaymentTypeMappings RIGHT OUTER JOIN qs_new_pmt_type ON tln_PaymentTypeMappings.PTMClientPaymentDesc = qs_new_pmt_type.New_Pmt_Type RIGHT OUTER JOIN tri_Ldg_Tran RIGHT OUTER JOIN tri_IDENT LEFT OUTER JOIN tri_ClaimChg ON tri_IDENT.Pat_Id1 = tri_ClaimChg.Pat_ID1 ON tri_Ldg_Tran.PRS_ID = tri_ClaimChg.PRS_ID AND tri_Ldg_Tran.Chg_TRN_ID = tri_ClaimChg.Chg_TRN_ID AND tri_Ldg_Tran.Pat_ID1 = tri_IDENT.Pat_Id1 LEFT OUTER JOIN tri_Payer ON tri_Ldg_Tran.Payer_ID = tri_Payer.Payer_ID ON qs_new_pmt_type.Pay_Type = tri_Ldg_Tran.Pay_Type AND qs_new_pmt_type.Tran_Type = tri_Ldg_Tran.Tran_Type WHERE (tln_PaymentTypeMappings.PTMMarsPaymentTypeCode <> N'Chg') AND (tln_PaymentTypeMappings.PTMClientCode = 'SR') AND (tri_ClaimChg.Primary_Claim = 1) AND (tri_IDENT.Version = 0)
View Replies !
Dropdown List Values
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 Replies !
DropDown List Values
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 Replies !
List Of AccessMode Values
Does anyone know a reference that identifies the different component property access modes for SQL? Along the lines of: mySrcDTInstance.SetComponentProperty("AccessMode", 2); I suppose this would apply to OLEDB and ADO.NET, etc. Thanks! DB
View Replies !
SQL Statement - Query A List Of Values
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 Replies !
Set A Attribute To Random Values In A Given List
Hi,I like to have a SQL script which could update a table to set one attributeto a random value picked from a given list. The prototyping code is asbelow:select @value_list = ('John', 'David', 'Mathew', 'Paul')....update EMP set emp_name = @random_namewhere ...where random name is randomly got from the given list. Of course there willbe a cursor so that different row may have different random name but notnecessary unique. Also the attribute and the list could be any valid commonSQL data typesThanks!Ximing
View Replies !
List Of Values That Make SQL Blow
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 Replies !
How To ORDER BY Designated List Of Key Values
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 Replies !
List Of Values In Input Parameter
Hello, I have the following issue: I have the following statement into a function: select a,b form T where c IN @parameter t is the table c is datatype= integer @parameter is a input parameter in the function, the @parameter contains more values and passed as a string. Running the statement above I got an error due to conversion type. How can I pass a list of parameters in the @parameter variable to make the statement works? Thank in advance.
View Replies !
Concatenate List Of Values Under A Group
I have the following dataset: State ZIP Homes Schools WA 98007 2000 4 WA 98052 3000 5 WA 98079 2000 3 Now if I have set the group by expression on State but as display if I want to show it as €œ[98007, 98052, 98079]€? how can I accomplish this. My report needs to show: State Homes Schools [98007, 98052, 98079] 7000 12 Any help will be greatly appreciated
View Replies !
List All Minutes Between Two DATETIME Values
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 Replies !
Stored Procedure To List Values From Different Tables
I have several tables that are related with the same primary key that I need to make a consolidated list of values for. The schema of each table is similar but some have more or less fields than others. I want to make a list of all table names, field names, and the value of that field. I can select all tables included from sysobjects. The existence of the key is probable but not guaranteed in all tables. The basic table format is: Table1------ID Field1 Field2 Field31 0.0 4.1 3.92 0.5 1.3 0.23 7.1 8.8 9.3 Table2------ID Field10 0.41 3.32 2.73 5.7 Table3------ID Field1 Field22 2.4 4.63 4.3 8.1 Format of the result set:(specifying ID = 2) Table_Name Table_Field ValueTable1 Field1 0.5Table1 Field2 1.3Table1 Field3 0.2Table2 Field1 2.7Table3 Field1 2.4Table3 Field2 4.6
View Replies !
Passing List Values In Drill Through! URGENT
I have a custom list(say a list for account numbers, account numbers starting with 13 form a group, then starting with 14 form a group and like that) and then there are tables inside this list. the report will be displayed based on the values of this custom list. i also have a drill through report for this. now when i pass fields!accountnumber.uniquename for this list only the first of the group values say 130001 is selected.. what shud i do to select the whole grp?? thanks!
View Replies !
List Of All Status Values Of ExecutionLog In SSRS
Hi, I need to know all the possible 'Status' values of the ExecutionLog in repoting services. Is there any way to find it? presently when i am using..... select distinct Status from ExecutionLog the result I get is rsJobWasCanceled rsInternalError rsReportParameterValueNotSet rsParameterError rsReportTimeoutExpired rsProcessingAborted rsSuccess rrRenderingError But I want to get all the possible list. Is there any master table for this?
View Replies !
Creating A Form With A Drop Down List Of The Foreign Key Values
I have the following tables in an SQL Server database. Contact ======= NameID TitleID Name JobTitle ======== TitleID Title I'm trying to make a form to add entries to the Contact table that lets me associate a job title to a name by choosing it from a drop down list. I have been able to make it so I get a drop down list of TitleID but I need the Title field displayed. I haven't been able to do it. Does anyone have any tips on how to do this?
View Replies !
Select Where 'in' A List
I have done queries before where you have something like this: Select name From people Where uid in (Select UID from employees where salary > 500000) as an example. I have to use stored procedures, I can't connect directly... I have a datatable of records that I select from a database. In the database table there is a flag 'checkedOut' (bit field) wich I want to set to true (1) for each of these records. Since I have to use stored procedures I think I am limited to passing in parameters. I wrote a function using a stringbuilder to concatonate the key fields into a comma delimited list "1, 55, 98" etc. and thought to use it as the clause in the 'in' sample ie:Update ServiceRequests set CheckedOut = 1 Where UID in (@UIDList) I have tried a few different things, none works. Any ideas how you might accomplish this with stored procedures? The records are passed to a thread to be dealt with and it's possible the next time it runs it will pick up the same records for processing if the first thread isn't through. I needed the 'CheckedOut' to ensure I don't accidentally do that. Thanks.
View Replies !
SELECT Using All Items From List
I need to create a stored procedure that takes a list of product numbers(541, 456, CE6050,...) and selects only the customers that have all product numbers in the list. I have a product table that has the following schema: rowid numeric(18,0),productNumber numeric(5,0),customerNumber numeric(5,0) and a customer table: customerNumber numeric(5,0),address varchar(50),contact varchar(50) So a customer can have more than one product, but I need a select statement that takes the product numbers and selects them against the product table and returns only the customerNumbers that have the entire list of product numbers. I have tried doing a join on the product list and productNumber, then join with the customer table on customerNumber, but that gets any entires found in the list not the entries that have all the product numbers. Thanks in advance for any help.
View Replies !
How To List The Records Of A Select
Hi world,Normally we receive the results of a query in several or thousands of rows. Select * From Clients-------------------------Row1. Client1Row2. Client2....Which is the the way to have everything on the same row separated by commas?Row1. Client1, Client2...thxDavid
View Replies !
SELECT IN List Query
I have a table that has a list of skills, ie "HP One", "HP Two" I need to pass the these skills from my applications search page to a stored procedure, hence I have a snippet of the SP below. DECLARE @SkillSet NVarChar (200) SET @SkillSet = '-HP One-,-HP Two-' SET @SkillSet = Replace(@SkillSet, '-', '''') SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN (@SkillSet) The following sp does not return any results, but when i set the last line to .. SELECT * FROM CPSkillMatrixLevels WHERE SkillMatrixLevelName IN ('HP One', 'HP Two') It returns a set of results. Also when I do a select on the variable @SkillSet, ie SELECT @SkillSet, it displays 'HP One', 'HP Two' Can enybody help me here, i Know i'm doing something wrong, but I cant think of what it is. Thanks
View Replies !
Select With From Database With A List Of Parameters
Hi, i have a big problem. I´m having trouble with the select statement in SQL query language. The problem is that I need to retrieve various data from database and the input object is a list. The simple way of doing this is:SELECT <return values>FROM <datatable name>WHERE (<select data parameter>)My problem is that my where parameter needs to be an array list. The simple way of solving the problem would be using a for statement in my c# code and call my store procedure various times, but my array list can be too long and take a long time to connect and search data for each statement, so I need to access the database once.Can anybody help; I would appreciate it very much thx, Malcolm
View Replies !
Creare A SP With A List Of Insert Using A Select
Hi, I would like to create a Stored Procedure for obtain a list of insert using the information of a select queryI have to do X insert for days, using my start selectConstant : start dayx insert for each day if in my select I obtain 550 records and x = 100I've to do 100 insert for day with information of the selectplease note that in the final day I have to insert only 50 records and not 100 (500 and 100 are not mutiples) Thanks
View Replies !
Select Statement For Dropdown List
I have a dropdown list that is populated by two columns in a database.select (firstname+surname) AS Fullname from table where id = 'id'; It works fine but i want to know how i would get a space between the firstname and the surname because at the moment all the values come back as JoeBloggs....without any spaces
View Replies !
Comparison Operator In Select List
I want to create a column alias to represent the comparison of twocolumns (ie a boolean result of True or False). A simple example is:Select VehicleFinanceID, SalePrice > PurchasePrice As isProfit[color=blue]>From VehicleFinance[/color]but I get an error 'Incorrect syntax near >'Books online states that the select_list can contain column_name orexpressionAn expression is a column name, constant, function, any combination ofcolumn names, constants and functions connected by an operator[color=blue]> is a binary operator[/color]So why do I get this error.Incidentally, if I use an arithmetic operator such as +, there is noproblem.
View Replies !
ORDER BY Columns In SELECT List?
According to BOL, columns in an ORDER BY clause do not have to be in the SELECTcolumn list unless the SELECT includes DISTINCT, or the UNION operator.Is this a SQL Server thing, or SQL standard behavior? That is, if I were to writeabsolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECTlist?
View Replies !
How To Select A Default Value From A List In A Parameter
I have a report with a dataset/parameter to select the salesperson. SELECT DISTINCT [DatabaseName$Sales Shipment Header].[Salesperson Code], [DatabaseName$Salesperson_Purchaser].Name, 1 AS SortID FROM [DatabaseName$Salesperson_Purchaser] RIGHT OUTER JOIN [DatabaseName$Sales Shipment Header] ON [DatabaseName$Salesperson_Purchaser].Code = [DatabaseName$Sales Shipment Header].[Salesperson Code] UNION ALL SELECT NULL AS [Salesperson Code], 'Alle salespersons' AS Name, 0 AS SortID ORDER BY SortID, [DatabaseName$Salesperson_Purchaser].Name In the preview mode the "All salespersons" is selected. When I deploy I see in the web user interface "<Select a Value>". The second choice is "All salespersons". Is there a way to see "All salespersons" in the web user interface? How do I select a default value from my dataset of salespersons?
View Replies !
List Format Of Select Result
Hello, As a MS SQL newbee, I may be asking stupid questions. By default, when I run select * from atable, here is the format I get: column1 column2 --------------------------- value11 value12 value21 value22 If the columns and the rows are too many, it is not easy to know a value belongs to which column. What I'd like to have is a list format looks like this: column1:value11 column2:value12 column1:value21 column2:value22 The name of the columns shouldn't be embedded in the select statement because it's changing. Thanks for any help, webcliff
View Replies !
Partial Duplicates And Select List
I have a table with DiscNo, Artist, Title and other fields. I would like to find all duplicate records with the same artist/title and with the first 3 characters of the discnumber. e.g. SELECT Artist, Title Into #TempArtistTitle FROM MediaFile GROUP BY Artist, Title HAVING COUNT(SubString(DiscNo, 0, 3)) > 1 SELECT MediaFile.DiscNo, MediaFile.Artist, MediaFile.Title FROM MediaFile RIGHT OUTER JOIN #TempArtistTitle ON MediaFile.Title = #TempArtistTitle.Title AND MediaFile.Artist = #TempArtistTitle.Artist ORDER BY Artist, Title, DiscNo Drop TABLE #TempArtistTitle GO See, if the first 3 characters of the disc number is the same, it is the same manufacturer. This query works somewhat, although it returns records that the discnumber is unique too. Like below, the LG disc number shouldn't be returned, as there is only one record for that artist/title. SC8151-10 - Garth Brooks - Friends In Low Places SC8125-04 - Garth Brooks - Friends In Low Places LG5003-07 - Garth Brooks - Friends In Low Places Could someone help me please?
View Replies !
NULL Values In A SELECT In Another SELECT
Hi,I have a query like this :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...The problem is that I don't want to return the results where x3 isNULL.Writing :SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ... AND x3 IS NOT NULLdoesn't work.The only solution I found is to write :SELECT * FROM((SELECTx1,x2,( SELECT ... FROM ... WHERE ...UNIONSELECT ... FROM ... WHERE ...) as x3FROM ...WHERE ...) AS R1)WHERE R1.x3 IS NOT NULLIs there a better solution? Can I use an EXISTS clause somewhere totest if x3 is null without having to have a 3rd SELECT statement?There's probably a very simple solution to do this, but I didn't findit.Thanks
View Replies !
How Do I SELECT A Column That STRICTLY Matches A List
Hello there, I have the following table: ROOMTYPE AMENITY ========= ======= R001 1 R001 2 R001 3 R002 1 R002 2 R002 4 R003 1 Let's say I want to get the ROOMTYPE which contains AMENITY 1,2,4 only. If I do this: SELECT ROOMTYPE FROM TABLE WHERE AMENITY IN (1,2,4) I get all the 3 RoomTypes because the IN acts like an OR but I want to get the column which contains ALL of the items in the list, i.e I want the ROOMTYPE that has ALL of the 1,2,4 but not just 1 or 1,2, etc...In this case, I want only R002 to be returned because the other RoomTypes do not contain all of the 1,2,4 NOTE: The data and list above is an example only as the table contents as well as the list will change over due course. e.g. the list can be (2,6,8,10,20,..100) etc.. So, I would need a query which can cater for any list contents...(x,y,z,...) and the query should return me the RoomTypes which have ALL elements in that particular list. If one of the RoomTypes do not have an element, it should NOT be returned. Can anyone help me on this? Kush
View Replies !
SELECT With GROUP BY And Build A List From Vales Not Shown
Hard to write a subject line to describe this one. Anayway, I have a table with names and address plus an extra field noting a part number of product. I'd like to build a SELECT string that will return one result for each name/address (uniques only in other words) and build a comma delimited field of all the part numbers for that name/address. Example: NAME ADDRESS PART NUMBER John Smith 555 Main st., los angeles, ca 90003 5000 John Smith 555 Main st., los angeles, ca 90003 6650 Mike Jones 8569 West 18th Ave., San Diego, ca 1255 John Smith 555 Main st., los angeles, ca 90003 5144 Mike Jones 8569 West 18th Ave., San Diego, ca 2399 So I'd like my results to look like this: NAME ADDRESS PARTS John Smith 555 Main st., los angeles, ca 90003 5000,6650,5144 Mike Jones 8569 West 18th Ave., San Diego, ca 1255,2399 THanks in advance for any suggestions! Raul
View Replies !
How To Select Multiple Rows As Comma Separated List?
Hi, I have a table of users, a table of categories, and a many-to-many table linking users to categories. My problem is that I want to select all the users with an extra column containing a comma-separated list of the categories they belong to. Here is a stripped-down version of the table fields: tbl_User UserId, Email tbl_Category CatId, CatName tbl_User_Category UserId, CatId I have tried using the coalesce function to build a string, but can only get this to work for one row at a time: DECLARE @list nvarchar(100) SELECT @list = COALESCE(@list + ', ', '') + CAST(CatId AS varchar(4)) FROM tbl_User_Category WHERE UserId = @UserId SELECT @list as List Any ideas on how to add to this to get it to do each row in tbl_Page? Or am I attacking this from the wrong angle????? Any help would be fantastic! thanks, Rob
View Replies !
Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far: SELECT l.loanid, p.investorid, l.duedate, case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate, pc.interestrate FROM loan l inner join participation p on p.loanid = l.loanid inner join paymentchange pc on pc.loanid = l.loanid where p.investorid = '12345' and RateDueDate is not null order by l.loanid, pc.duedate I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated. Thanks!
View Replies !
Problem With Group By When Using Case Statements In The Select List.
I am using SQL Server 2005 and fairly new at using SQL Server. I am having problems using a Case statements in the select list while have a group by line. The SQL will parse successfully but when I try to execute the statement I get the following error twice : Column 'dbo.REDEMPTIONHISTORY.QUANTITY' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Below is the my sql statement: SELECT dbo.DateOnly(TH.TransactionDate) AS RptDate, RH.Item, ItemRef = Case when RH.Quantity < 0 then Sum(RH.Quantity) when RH.Quantity >= 0 then Sum(0) end FROM dbo.RHISTORY AS RH INNER JOIN dbo.TRANSHISTORY AS TH ON RH.TRANSACTIONID = TH.TransactionID WHERE (dbo.DateOnly(TH.TransactionDate) BETWEEN '10-1-2007' AND '10-5-2007') AND (RH.TransactionCode IN (13, 14, 15, 16)) Group by dbo.DateOnly(TH.TransactionDate), RH.Item The TransHistory table contains, primary key transactionid, TransactionDate and the RHistory contains all the details about the transaction, the RHistory table is joined to the TransHistory table by foreign key TransactionID. I am trying to get totals for same item on the same day. Any help will be greatly appreciated. I am also having trouble using If..Then statements in a select list and can not fin the correct syntax to use for that.
View Replies !
Error: Only One Expression Can Be Specified In The Select List When The Subquery Is Not Introduced With EXISTS.
Hi, When i try to save my stored procedure.. i am getting the above error and this is my sproc 1 INSERT INTO Statement..ClientSources 2 ( 3 ClientId, 4 ClientSourceId, 5 SourceName 6 ) 7 Select Distinct 8 @ClientId, 9 SOURCE_NUM, 10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME 11 WHEN 2 Then SRC2NAME 12 WHEN 3 THEN SRC3NAME 13 WHEN 4 THEN SRC4NAME 14 WHEN 5 THEN SRC5NAME 15 WHEN 6 THEN SRC6NAME 16 WHEN 7 THEN SRC7NAME 17 WHEN 8 THEN SRC8NAME 18 WHEN 9 THEN SRC9NAME 19 WHEN 10 THEN SRC10NAME 20 WHEN 11 THEN SRC11NAME 21 WHEN 12 THEN SRC12NAME 22 WHEN 13 THEN SRC13NAME 23 WHEN 14 THEN SRC14NAME 24 WHEN 15 THEN SRC15NAME 25 END 26 FROM 27 PlanDBF p 28 Where 29 p.PLAN_NUM = s.PLAN_NUM 30 ) as SourceName 31 FROM 32 SourceDBF s 33 Where 34 SOURCE_NUM NOT IN ( 35 SELECT DISTINCT 36 ClientSourceId 37 --SourceName 38 FROM 39 Statement..ClientSources 40 Where 41 ClientId = @ClientId 42 ) I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Any help will be appreciated. Regards Karen
View Replies !
Is It Possible To Dynamically Populate A Parameter List With Values Based On Another Parameter Value?
Is it possible to fill a parameter list with values based on another parameter value? Here's what I have so far (which hasn't worked)... I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school. I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table). I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school. Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up... The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'. The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid. ...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter? Any help you can give me would be great!! Thank you
View Replies !
Select Some Values From A Row And Insert On Another Row
I have a table wih multiple records and some has common values. For example, new child row need to inherit some of parent's data. Here is how I want to do: Select [parent data1], [parent data2] from [TABEL] where id = parent Insert INTO [TABLE] ([child id], [child data1], [child data2] ) VALUES [child id] [parent id] [parent id]........... I was thinking about using Selected and Inserting events but not sure how. Please help? Thanks,
View Replies !
|
TRACKER
