IF Statement In SELECT WHERE Clause
Hi Everyone,
I have the following stored procedure, I would like to use
IF statement or something of the sort in the where clause i.e.
The last line in the SP is: AND (category.categoryID = @categoryID),
I only want to check this, if @categoryID is not = 12.
So can I do something like this:
IF @categoryID <> 12
AND (category.categoryID = @categoryID)
STORED PROCEDURE:
CREATE PROCEDURE sp_get_total_risk_patients
@categoryID int
AS
SELECT COUNT(DISTINCT patient.patientID) AS total_patients
FROM patient
INNER JOIN patient_record ON patient.patientID = patient_record.patientID
INNER JOIN sub_category ON sub_category.sub_categoryID = patient.sub_categoryID
INNER JOIN category ON category.categoryID = sub_category.categoryID
WHERE risk = 6
AND (completed_date = '' OR completed_date IS NULL)
AND (category.categoryID = @categoryID)
View Complete Forum Thread with Replies
Related Forum Messages:
Use A Variable Along With The FROM Clause In SELECT Statement
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts. What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*). This is the code in my procedure.. DECLARE @tab_list CURSOR set @tab_list = CURSOR FOR select * from table_list OPEN @tab_list DECLARE @tab_name varchar(256) DECLARE @rec_cnt int FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt select count(*) from @tab_name This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table. Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there. Please help me to construct the select statement that is similiar to x=<table name> select * from x where x is a variable and the table name gets substituted. what is the syntax for it ?
View Replies !
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 !
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 !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement
Greetings, I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered. How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column. ALTER PROCEDURE [dbo].[MyProcedure] AS BEGIN SELECT DISTINCT A.Name, A.index FROM ... ... ORDER BY A.[Index], A.Name ASC END ALTER PROCEDURE [dbo].[MyProcedure] AS BEGIN SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index] END Thanks
View Replies !
Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile... Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69 Incorrect syntax near the keyword 'ORDER'. However the select statement itself runs perfectly well as a query, no errors. The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs. What gives with this? Thanks in advance R. The code: Code Snippet -- ================================================ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF object_id('InsertImportedReportData ') IS NOT NULL DROP PROCEDURE InsertImportedReportData GO -- ============================================= -- Author: ----- -- Create date: -- Description: inserts imported records, marking as duplicates if possible -- ============================================= CREATE PROCEDURE InsertImportedReportData -- Add the parameters for the stored procedure here @importedReportID int, @authCode varchar(12) AS BEGIN DECLARE @errmsg VARCHAR(80); -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; --IF (@authCode <> 'TX-TEC') --BEGIN -- SET @errmsg = 'Unsupported reporting format:' + @authCode -- RAISERROR(@errmsg, 11, 1); --END DECLARE srcRecsCursor CURSOR LOCAL FOR (SELECT ImportedRecordID ,ImportedReportID ,AuthorityCode ,[ID] ,[Field1] AS RecordType ,[Field2] AS FormType ,[Field3] AS ItemID ,[Field4] AS EntityCode ,[Field5] AS LastName ,[Field6] AS FirstMiddleNames ,[Field7] AS Title ,[Field8] AS Suffix ,[Field9] AS AddressLine1 ,[Field10] AS AddressLine2 ,[Field11] AS City ,[Field12] AS [State] ,[Field13] AS ZipFull ,[Field14] AS OutOfStatePAC ,[Field15] AS FecID ,[Field16] AS Date ,[Field17] AS Amount ,[Field18] AS [Description] ,[Field19] AS Employer ,[Field20] AS Occupation ,[Field21] AS AttorneyJob ,[Field22] AS SpouseEmployer ,[Field23] As ChildParentEmployer1 ,[Field24] AS ChildParentEmployer2 ,[Field25] AS InKindTravel ,[Field26] AS TravellerLastName ,[Field27] AS TravellerFirstMiddleNames ,[Field28] AS TravellerTitle ,[Field29] AS TravellerSuffix ,[Field30] AS TravelMode ,[Field31] As DptCity ,[Field32] AS DptDate ,[Field33] AS ArvCity ,[Field34] AS ArvDate ,[Field35] AS TravelPurpose ,[Field36] AS TravelRecordBackReference FROM ImportedNativeRecords WHERE ImportedReportID IS NOT NULL AND ReportType IN ('RCPT','PLDG') ORDER BY ImportedRecordID -- this should work but gives syntax error! ); END
View Replies !
Is It Possible To Re-reference A Column Alias From A Select Clause In Another Column Of The Same Select Clause?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
SELECT Then DELETE Versus Extra Clause In SELECT
Far below (in section "original 3 steps"), you see the following:1. a temp table is created2. some data is inserted into this table3. some of the inserted data is removed based on a join with the sametable that the original select was made fromIn my opinion, there is no way that the join could produce more rowsthan were originally retrieved from viewD. Hence, we could get rid ofthe DELETE step by simply changing the query to be:INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )AND INMESS NOT LIKE '2__' ---- the added line===== original 3 steps (mentioned above) =====CREATE TABLE #details (rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,orig VARCHAR(35) NULL,bene VARCHAR(35) NULL,orig_corr TINYINT NULL,bene_corr TINYINT NULL)INSERT INTO #details ( rec_id, orig_corr, bene_corr )SELECT rec_id, 0, 0FROM viewDWHERE SOURCE_SYS NOT IN ( 'G', 'K' )AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )DELETE dFROM #details dJOIN viewD v ON ( d.rec_id = v.rec_id )WHERE INMESS LIKE '2__'
View Replies !
Expression Defined In SELECT Clause Overwrites Column Defined In FROM Clause
2 examples: 1) Rows ordered using textual id rather than numeric id Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id Result set is ordered as: 1, 11, 2 I expect: 1,2,11 if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. 2) SQL server reject query below with next message Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique. Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine. It reproducible on Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) and Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) In both cases database collation is SQL_Latin1_General_CP1251_CS_AS If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again. Could someone clarify - is it bug or expected behaviour?
View Replies !
IF ELSE Statement In WHERE Clause
actually i need to do a sql report... there are 3 parameters (date from. date to, issue_user_id) that are required in order to retrieve the data...now i am facing one problem, if i just enter the date_from and date_to, and leave the issue_user_id blank, i wish to retrieve all the data, but i can't do it. i have try to use: if @IssueUserID = ' ' Select....(blah blah ) else select (blah blah)...it is long and work in Query analyzer but no working in .NET Reporting services.... :confused: is it possible to enter IF ..ELSE statement in WHERE clause?hope that u all can help..... ================================================== == SELECT REPLACE(REPLACE(REPLACE(REPLACE(tbl_Receipt.Paymen tRefID, '1', 'Deposit for Admission'), '2', 'Consultation Services'), '3', 'Medical Fees'), '4','Others') AS PaymentRefID, tbl_Receipt.ReferenceNo, tbl_Receipt.ReceiptDate, CONVERT(varchar(10), tbl_Receipt.ReceiptDate, 103) AS ReceiptDt, tbl_Receipt.TotalAmountPaid, tbl_Receipt.Payee, tbl_ReceiptDT.PaymentTypeID, tbl_PaymentType.PaymentType, tbl_ReceiptDT.Amount, tbl_User.Name, tbl_Receipt.IssueUserID, tbl_User.Designation FROM tbl_Receipt WITH (nolock) INNER JOIN tbl_ReceiptDT WITH (nolock) ON tbl_Receipt.ReceiptID = tbl_ReceiptDT.ReceiptID INNER JOIN tbl_PaymentType WITH (nolock) ON tbl_ReceiptDT.PaymentTypeID = tbl_PaymentType.PaymentTypeID INNER JOIN tbl_User ON tbl_Receipt.IssueUserID = tbl_User.UserID WHERE (tbl_Receipt.ReceiptDate BETWEEN CONVERT(datetime, @DateFrom, 103) AND CONVERT(datetime, @DateTo, 103)) AND (tbl_Receipt.IssueUserID = @IssueUserID) ORDER BY tbl_User.UserID, tbl_ReceiptDT.PaymentTypeID
View Replies !
Right Use Of Like In An Or Clause Of Where Statement
I have the below where clause on a select where substring(pa.other_id_number,5,1) = @monthid and pa2.df_patstatus like 'act%' and ((pa2.df_institutionname not like ('ACT%') or pa2.df_institutionname not like ('county%'))) It returns records that have either one of the not like statements. If I comment out the line and use below it will work for one of the statements where substring(pa.other_id_number,5,1) = @monthid and pa2.df_patstatus like 'act%' and pa2.df_institutionname not like ('ACT%') How can I get the two to work together. I thought the parenthesis would work but no luck.
View Replies !
LIKE Clause In IF Statement
Can the like clause be used in IF statement or only applicable to SELECT statement ? e.g. IF (@lADDRESS LIKE '%P.O. BOX') BEGIN PRINT 'IGNORE ADDRESS' END Thank you Yog
View Replies !
Using Case Statement With WHERE Clause...
Hi, I need help to use CASE Statement within my WHERE Clause. I want to change the WHERE Clause based on my condition as following: SELECT ... FROM ... WHERE (condition) AND (condition) AND (condition) AND ( CASE Table.Category WHEN 'Drinks' THEN Table1.Field1 = 1 -- Problem line ELSE Table1.Field1 = 1 AND Table1.Field2 = 1 -- Problem line END ) In the above case, my WHERE Clause is dependent on one of the fields in a table. If it has a certain value then only Table1.Field1 is used otherwise Table1.Field1 & Field2 come into action. I am getting error on the mentioned line (Problem Line). Since CASE is an expression and does not execute a statement, can anyone help me to get my WHERE Clause working... Thanks in advance...
View Replies !
If/Case Statement In Having/Where Clause - Please Help Me.
Hi!I want to make search engine and I have problem with query for this search. User can write username to search or text to search or both. So at first I made query for each event individually: ALTER PROCEDURE [dbo].[Show_Search_Topics] @username varchar(200), @search_text varchar(200), @days int AS DECLARE @date DATETIME SET @date = DATEADD(day,@days,GETDATE()) IF @username IS NOT NULL AND @search_text IS NULL BEGIN SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total FROM dbo.forum_topics INNER JOIN dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa HAVING (forum_topics_1.post_parrent_id = 0) AND (dbo.aspnet_Users.UserName = @username) AND (forum_topics_1.post_current_date >= @date) ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC END ELSE IF @username IS NULL AND @search_text IS NOT NULL BEGIN SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total FROM dbo.forum_topics INNER JOIN dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%') ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC END ELSE IF @username IS NOT NULL AND @search_text IS NOT NULL BEGIN SELECT COUNT(dbo.forum_topics.post_user_id) AS UserPosts, forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa, COUNT(DISTINCT forum_topics_2.post_id) + 1 AS post_total FROM dbo.forum_topics INNER JOIN dbo.aspnet_Users ON dbo.forum_topics.post_user_id = dbo.aspnet_Users.uID INNER JOIN dbo.forum_topics AS forum_topics_1 ON dbo.aspnet_Users.uID = forum_topics_1.post_user_id INNER JOIN dbo.forum_kategorie ON forum_topics_1.post_cat_id = dbo.forum_kategorie.forum_kat_id LEFT OUTER JOIN dbo.forum_topics AS forum_topics_2 ON forum_topics_1.post_id = forum_topics_2.post_parrent_id GROUP BY forum_topics_1.post_title, dbo.aspnet_Users.UserName, forum_topics_1.post_parrent_id, forum_topics_1.post_id, forum_topics_1.post_current_date, forum_topics_1.post_stick, forum_topics_1.post_user_id, forum_topics_1.post_cat_id, dbo.forum_kategorie.forum_kat_kolor, dbo.forum_kategorie.forum_kat_nazwa HAVING (forum_topics_1.post_parrent_id = 0) AND (forum_topics_1.post_current_date >= @date) AND (forum_topics_1.post_title LIKE '%' + @search_text + '%') AND (dbo.aspnet_Users.UserName = @username) ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC END RETURN This 3 queries are different only by Having clause. So I want to put If/Case in Having clause, but I have problem. Can anyone help me?Also I want to make paging from SQL level, so if anyone will be so helpful and make working this query with this: ALTER PROCEDURE [dbo].[Show_Search_Topics] @username varchar(200), @search_text varchar(200), @days int, @page int, @page_size int AS WITH Results As ( //QUERY) ) SELECT * FROM Results WHERE RowNumber BETWEEN (@page_size * @page + 1) AND (@page_size * (@page + 1)) ORDER BY forum_topics_1.post_stick DESC, forum_topics_1.post_current_date DESC RETURN I will be grateful :-)
View Replies !
Case Statement In Where Clause
I need a SQL statement that selects a specific year (@yr type int) in the "createddate" column...if this @yr is equal to 0 then I want to select ALL columns regardless of the year...This is what I have so far, but it doesnt work...SELECT * FROM tblUsersWHERE year(CreatedDate)=CASEWHEN @yr<>'0' THEN @yrELSE NOT NULLEND
View Replies !
Need Help With Query Statement 'IN' Clause.
I have a column in the database that stored moduleId that are seperated by '|' (pipes). For Example: '527|343|454' I need to add a where clause to a query that pulls the data based on a ModuleId. For Example: select * from table where 527 in [column above] Does anyone know how I can do this in a query? Normally I could use an IN statement, ex: select * from table where 527 in (527,343,454) How can I get the column in that format? Thanks for the help in advance, KM
View Replies !
Case Statement In Where Clause
If you could help me with my syntax i would really appreciateit. I'm trying to change the where clause on the fly, but it'sgiving me a syntax error.Hopefully from what I have below you can tell what i'm afterthe first part should evaluate a boolean expression, then if true,search using one field, otherwisesearch using a different fieldWHERECase WHEN @myCompany = 933THEN tblClient.companycode = 933 --problem lineELSEtblCase.clientcode = @myClient --problem lineENDAnd tblCase.status = 'Active'thank you!!
View Replies !
CASE Statement In Where Clause?
Can anyone tell me if it's possible to use a Case statement in a Whereclause, and if so, the proper syntax?J.R.Largo SQL ToolsThe Finest Collection of SQL Tools Availablehttp://www.largosqltools.com
View Replies !
Case Statement In Where Clause
Hello I want to put a case statement into a where clause but it's not working. Can anybody help, or tell me a better way of doing this Thanks very much declare @param varchar (100) select @param = 'mytext select colA ,colB ,colC from mytable where (case when @param is null then colA = 'group' else colA = 'single' end)
View Replies !
HAVING Clause Is A Case Statement???
i have wrote a query which compares two tables, returning anywhere the qty is not the same in each table: (simple ex) Select order_no from table1 inner join table2 on table1.order_no = table2.order_no group by order_no having table1.Qty<> table2.Qty BUT... I need to add a table3, where there maybe (or may not be enters - thus a left join). If there is an entry in table3 then use qty in table3 and not table1... so having becomes: CASE WHEN table1.Qty<> table3.Qty THEN table3.Qty<> table2.Qty ELSE table1.Qty<> table2.Qty END but how do i actually write this?
View Replies !
Case Statement In Where Clause
can i use case statement in where clause. The scenario is as follow declare @param int select * from table1 where column1 = 'asdf' column2= @param In the above sql, if @param is '' then i don't want to include it in the where clause.I can use "like" statement for that, but i want exact value not partial value. can i use case in where clause so that if @param is '' then i will not include in the where clause
View Replies !
Case Statement In Where Clause?
Can I do the following? Keep getting an error stopping at the first < of the where clause. declare @mon as int, @yr as int, @myDate as varChar(20) set @yr=2006 set @mon=1 set @mydate='01/31/2006 23:59:59' select 0 as DTAP, 0 as DT, 0 as TD, 0 as HIB, 0 as IPV, 0 as MMR, 0 as HEPB, 0 as _VAR, count(v.procedureKey) as FLU, 0 as PPV23, 0 as PCV7, v.chartID, max(rs1.dateService) as dateService from dbo.tbl1 v, (select distinct dateService, chartID, procedureKey from fhc.dbo.tbl1 where (datePart(year,dateService)=@yr and datePart(month,dateService)=@mon) and (procedureKey='90657' or procedureKey='90658')) as rs1 where (v.chartID=rs1.chartID) and (v.procedureKey=rs1.procedureKey) and (case when @mon=1 then dateDiff(month,dateService,@myDate)< 216 when @mon=2 then dateDiff(month,dateService,@myDate)<244 when @mon=3 then dateDiff(month,dateService,@myDate)<275 when @mon=4 then dateDiff(month,dateService,@myDate)<305 when @mon=5 then dateDiff(month,dateService,@myDate)<336 when @mon=6 then dateDiff(month,dateService,@myDate)<366 when @mon=7 then dateDiff(month,dateService,@myDate)<32 when @mon=8 then dateDiff(month,dateService,@myDate)<63 when @mon=9 then dateDiff(month,dateService,@myDate)<93 when @mon=10 then dateDiff(month,dateService,@myDate)<124 when @mon=11 then dateDiff(month,dateService,@myDate)<154 when @mon=12 then dateDiff(month,dateService,@myDate)<185 end) group by v.chartID, rs1.procedureKey
View Replies !
Using CASE Statement In A WHERE Clause
Is it possible to use CASE within a WHERE? I have a query which is something like this, but it returns an error: SELECT * FROM tablex WHERE CASE WHEN 'sexec' IS NOT NULL THEN dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' and dm_sexec LIKE 'sexec' ELSE dm_marque = 'foo' AND year(dm_date) LIKE 'pyear' AND dm_month LIKE 'pmonth' AND dm_weekno LIKE 'pweek' END GROUP BY dm_marque In this case sexec is a form parameter, if something is passed then I need to include it in the where statement, if it isn't I need to include something else. I am using CASE because there are three of these parameters and I want it to stop evaluating as soon as it matches as more than one may match but I only want to apply one. Many thanks Karen
View Replies !
WHERE Clause Of An INSERT INTO Statement
Hi, I have the following bit of SQL: Code Block INSERT INTO [INTRANETSQLEXPRESS].Trapped.dbo.TBL_Debtsolv (Debtsolv_ID, Payment_Amount, Payment_Status, Client_Status) SELECT Client.ID AS ClientID, InitialInstallment.OffsetAmountExpected AS FirstExpectedPayment , CASE WHEN Payment.TotalPaid <= 0 OR LatestPaymentDate IS NULL THEN 'No Money Paid' WHEN Payment.TotalPaid < InitialInstallment.OffsetAmountExpected AND Payment.TotalPaid >0 AND InitialInstallment.DateExpected + ISNULL(Schedule.DayOffset, 0) >GETDATE() THEN 'Still Awaiting - Part Paid' WHEN Payment.TotalPaid >= InitialInstallment.OffsetAmountExpected THEN '1st Payment Made' WHEN Payment.TotalPaid < InitialInstallment.OffsetAmountExpected AND InitialInstallment.DateExpected + ISNULL(Schedule.DayOffset, 0) <GETDATE() THEN 'Late - First Payment Not Made' ELSE 'Status Unclear' END AS Status , CASE WHEN Client.Status IN(0,1,2,3,4,5,6,7) THEN 'In Preparation' WHEN Client.Status IN(8,9) THEN 'Active' ELSE 'Inactive' END AS ClientStatus FROM SALEEN.Debtsolv.dbo.Client_Contact as Client LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Payment_Schedule AS Schedule ON Client.ID = Schedule.ClientID INNER JOIN SALEEN.Debtsolv.dbo.Client_LeadData CLD ON Client.ID = CLD.Client_ID INNER JOIN SALEEN.Debtsolv.dbo.Type_Client_Status TCS ON Client.Status = TCS.ID LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Type_Lead_Source TLS ON CLD.SourceID = TLS.ID LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Type_Lead_Source_Group AS LeadGroup ON TLS.[Group] = LeadGroup.ID LEFT OUTER JOIN SALEEN.Debtsolv.dbo.Users [User] ON CLD.Counsellor = [User].ID LEFT OUTER JOIN (SELECT ClientID , SUM(CAST((Amount + ISNULL(AmountOffset, 0))AS Money) / 100) AS OffsetAmountExpected , MAX(DateExpected) AS DateExpected FROM SALEEN.Debtsolv.dbo.Payment_Schedule AS PS WHERE (SequenceID <=(SELECT CASE WHEN NumInitialInstalments = 0 THEN 1 ELSE IsNull(NumInitialInstalments, 1) END AS NumInitialInstalments FROM SALEEN.Debtsolv.dbo.Client_LeadData AS LD WHERE (Client_ID = PS.ClientID))) GROUP BY ClientID ) AS InitialInstallment ON Client.ID = InitialInstallment.ClientID LEFT OUTER JOIN (SELECT Receipt.ClientID , SUM(CAST(Receipt.Amount AS Money)) / 100 AS TotalPaid FROM SALEEN.Debtsolv.dbo.Payment_Receipt Receipt INNER JOIN SALEEN.Debtsolv.dbo.Type_Payment_Status Type ON Receipt.Status = Type.ID WHERE (Receipt.Status = 5) GROUP BY ClientID) AS Payment ON Client.ID = Payment.ClientID LEFT OUTER JOIN (SELECT ClientID , MAX(ID) AS MaxPSID , MAX(LastPayment) AS LatestPaymentDate FROM SALEEN.Debtsolv.dbo.Payment_Schedule Schedule WHERE (NOT (LastPayment IS NULL) AND LastPayment > CONVERT(DATETIME, '1980-01-01 00:00:00', 102)) GROUP BY ClientID) AS LatestPaymentDate ON Client.ID = LatestPaymentDate.ClientID INNER JOIN (SELECT ClientID , MAX(SequenceID) AS LFPDSequenceID FROM SALEEN.Debtsolv.dbo.Payment_Schedule PS WHERE (PaymentType = 1) GROUP BY ClientID) AS LastPayment ON Client.ID = LastPayment.ClientID AND Schedule.ClientID = LastPayment.ClientID AND Schedule.SequenceID = LastPayment.LFPDSequenceID WHERE Client.ID = TBL_Debtsolv.Debtsolv_ID Executing this statement gives the following error: Code Block Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "TBL_Debtsolv.Debtsolv_ID" could not be bound. I think thsi is soemthing to do with referencing another table in the INSERT into command as if I put: Code Block WHERE Client.ID = '12065' The statement executes with no problem. I think I have read in some other threads that you cannot reference another table in the WHERE part of an INSERT INTO clause but cannot find out how I can make this work. Most of the statement comes from a pre-made report from one of our systems and I have slighlty changed it to insert the data into another database that we have. Any suggestions would be greatly appreciated Thanks
View Replies !
Where Clause In Insert Statement
Hello all, I've got a question. I have an array that I am inserting into a table. The code is in classic asp and looks like this: SQL1 = "INSERT INTO transactionz (classid, userid) values (" & listOfAnswers(i) & ",8) The problem is that I want to only insert values that have the value "1". listofAnsers is an array that returns values of "1" and "2". If there a way to limit the inserts to values of "1". Another words, I want to just insert the values of "1" into the table. I know that you experts would say I should control this on the code side and thats true, however, I can't (because I don't control the code side) so is there a way to do this via SQL? Am I making any sense? I was thinking that I needed to add a "where" clause at the end of the code, but I don't know where to go from here.
View Replies !
CASE Statement On A WHERE Clause?
I am trying to create a condition if the value of a parameter is null then pass a certain WHERE condition to my query. I keep on getting this error: Line 15: Incorrect syntax near '='. this is my query that lies on a PROC: SELECT count(a.pEngr_Item_ID) as assembly_count FROM Engr_Item a INNER JOIN Engr_BOM_Control b ON a.pEngr_Item_ID=b.fEngr_Item_ID WHERE b.Bill_Type=@v_Bill_Type and a.Item_No=@v_Item_No and case when @v_Rev_Lett is not null then a.Item_No=@v_Rev_Lett else a.Item_No=@v_Rev_Lett end
View Replies !
CASE Statement On A WHERE Clause?
I need to have a CASE statement inside a WHERE clause. Is this possible? Here is my WEHRE clause. Any suggestions are appreciated: WHERE --r.pBOM_ID=d.fEngr_BOM_ID and r.fItem_ID=a.pEngr_Item_ID and r.level<=@v_level+1 and case when @v_showrootlevel=1 then r.level>1 else r.level>1 end order by r.pID
View Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
Returning An IN Clause From A CASE Statement
Hi all, I am passing in a variable into a CASE statement. Based on the value, I want to return a set of values in an IN clause. Here is an example: (The where clause will use a field called 'Location') DECLARE @strTest as Varchar(50) SET @strTest = 'HI' SELECT * FROM [SomeTable] WHERE CASE @strTest WHEN 'HI' THEN Location IN('1', '2', '3') END ORDER BY Location Is this possible to do? Sanctos
View Replies !
'Case' Statement Inside 'Where' Clause
Hi I've been trying to put a simple case statement into my 'where' clause but having no luck, is there another way to do the following? DECLARE @searchCriteria Int SET @searchCriteria = 2 SELECT column1, column2 FROM TABLE WHERE CASE @searchCriteria WHEN 1 THEN (column3 = 1000100) WHEN 2 THEN (column3 = 1000101) END CASE ...cheers
View Replies !
Can We Have An Inner Join Clause In An Update Statement
Hi, I'm trying to inner join an update statement. Something like this: update #point_connection_temp AS a inner join #point_connection_temp_two as b on a.key_fld = b.key_fld set a.next_point = b.next_point where #point_connection_temp.next_point is null order by a.key_fld I'm getting an error message:Incorrect syntax error near AS Any help will be greatly appreciated.Thank you!!!!!!!!!1
View Replies !
Conditional Where Clause W/ Case Statement Possible?
Greetings, After many hours search many forums and many failed experiments, I figure it's time to turn to the experts. I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls. SELECT uniqueID, lob, xdate FROM mytable WHERE CASE WHEN @myparam = 'ALL' THEN xdate >= '2007-09-01' ELSE xdate >= '2007-09-01' or lob = @myparm END I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank. I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much. Any ideas? Rob
View Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
Case Statement On Where Clause If Parameter =NULL
I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right: SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr FROM dbo.Engr_BOM_Control bom WITH (nolock) INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock) ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID CASE WHEN @v_Bill_Type IS NOT NULL THEN AND bom.Bill_Type=@v_Bill_Type END
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Select With IN Clause
Hi SQL Experts, i have a strange problem i have a variable which stores some values(ID) with single quote (so that i can use directlt inside the IN Clause of SELECT Declare @DMSIDs AS VARCHAR(1000) -- variable declare, Select @DMSIDs = '''DMS00046847'',''DMS00048305''' -- for test putting 2 correct values with escape characters Select * from issue where id in (@DMSIDs) -- valid statment, but does not return any data Select * from issue where id in ('DMS00046847','DMS00048305') -- same above constant value this returns data, but putting the values in varaible then trying fails. The reason is i have a master table called issue and have another table [delta] where a particular column will store all the ID's of the issue table comma separated with single quote and i wanted to use something like below in my actual application Select * from issue where ID in (Select distinct delta_ID from Delta_branch where date = getdate()) but since the above example with variable is not returning any data i wonder if such is possible in any other ways. thank you for reading and helping me.
View Replies !
Select To Clause
I'm having trouble using the To clause in my select statement. The following errs out with Incorrect syntax near the keyword 'to'. use pubs select * from view_Rates to 'c: est.csv' I also tried this and got same error: use pubs select * to 'c: est.csv' from view_Rates
View Replies !
Using A Field Alias For A CASE Statement In A GROUP BY Clause
When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement: ie, SELECT fieldname1 = CASE WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' ELSE thisandthat END , fieldname2 , fieldname3 FROM tablename1 GROUP BY CASE WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' WHEN condition THEN 'blah blah' ELSE thisandthat END, , fieldname2, fieldname3 etc. The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression". Regards, Dave
View Replies !
SQL Statement: Group By And Where Clause Do Not Display Null Rows
The following is a simplified version of my SQL statement. I am attempting to do a simple count(*) with two groupings and a where clause. This is Select command for a GridView. However, I am unable to display zeros. The rows are completely missing and I would greatly appreciate someone's help. I have already tried Group By All, but that, unfortunately, does not work. Here is my SQL statement: "SELECT [GENDER], [RACE], COUNT(*) FROM [TABLE] WHERE ([COLUMNNAME] ='SOMETHING') GROUP BY [GENDER], [RACE]" Thanks for the help in advance!
View Replies !
How SELECT In WHERE Clause Works?
Please help I'm trying to do a select command but doesn't return any record I have two tables one is "lists" another one is "list_records" in the liss table I have 4 records and in the list_records I have only one record which is tell who is already visited the site so I created a query to get people who is NOT visited the site Here is my query (I got 0 record return) SELECT *FROM lists list_reccords WHERE NOT EXISTS (SELECT * FROM lists, list_records WHERE list_records.is_visited = 1 AND lists.list_id = list_records.list_id)
View Replies !
Insert Into ....... Select Clause
Hi, I have a question about Insert into ..... Select clause in a SP. I need to insert some rows into a temperary table in a specific order. For example, insert into #TempTable { ....... ........ } Select * from products order by @SortBy @SortDirection. // First of all, the order by clause does not take variables. I have tried to use declare @query varchar (1000) set @query = 'Select * from products order by " + @SortBy + ' ' + @SortDirection exec (@query) // However, I get an error message because I should supply a Select statement. How can I solve this problem?? Thanks for your help!
View Replies !
Using @column For Where Clause In Select
Hi, I want to do something like "select range from mytable where @ColumnToUse = 1" then "select range from mytable where @ColumnToUse = 2" etc. and @columnToUse lists a different column heading each time. I know I can put this into an "exec ('select range...')" type statement, but it's really helpful if I don't evaluate all my variables at this stage in my scripts. Can anyone help? THanks Dan
View Replies !
Problems With Select Clause With Except?
Hi there, I'm preparing a query and am having problems displaying the right content. There is only one table i am dealing with, however there are 2 fields that a check needs to be made on to see if they exist or not. Working portion of the query: SELECT * FROM tblVehicles WHERE (Make = 'audi') AND (Model = 'A4') AND (YearRange = '02-05') AND (SubModel = '3.0L') AND (Factory_Wheel_Diameter = '17') this returns 2 records: record 1 74, AUDI, A4, 3.0L, 02-05, fwd and 4X4, 17, 5-112, 16,X43659,19, null, null, null, 16, AVW659A(9), 219 record 2 73, AUDI, A4, 3.0L, 02-05, fwd and 4X4, 17, 5-112, null, null, null, null, null,null,17, AVW759A(9), 219 the second record is the one i need to have eliminated. i have tired adding: and (MAW_Wheel is not null) and (OEMWheel is not null) to my sql statement, but this returns no records. I have also tried using the except, not in, etc.. Seems like i need to combine those 2 values and select records when those 2 values are not both null only, but no matter what I try, i get it wrong. Can anyone point me in the right direction? Thanks,
View Replies !
|
TRACKER
