I have a scenario where the End User is going to select two parameters. @AgentID and @Location If the End User selects @AgentID parameter, in my WHERE clause, I do not want to check the location condition. By default, the agent knows their location already.
If the End User select @Location parameter, in my WHERE clause, I still want to check the Agent condition.
@AgentID parameter would populate either with "All" or their actual agent# (76754 for example).
@Location parameter would populate either with "All" or their actual location (Birmingham or Bessemer for example)Sample of my current WHERE clause:
WHERE
gl.ReceiptDate >= @BeginDate and gl.ReceiptDate < DATEADD(D,1,@EndDate)
AND gl.AgentId = CASE WHEN @AgentID = 'All' THEN gl.AgentID ELSE @AgentID END
AND gl.Location = @Location
I have used the below update query. However, its updating only the first value. Like its updating AB with volume when c.Type = ABC, similarly for CD. Its not updating based on the 2nd or the next case condition.
Update XYZ Set AB = a.Amt * (CASE WHEN c.Type = 'ABC' THEN (c.volume) WHEN c.TYPE = 'DEF' THEN (c.volume) WHEN c.Type = 'GHI' THEN (c.volume) Else 0 END), CD = CASE WHEN c.Type = 'MARGIN' THEN '4105.31' WHEN c.Type = 'ABC' THEN '123.1' WHEN c.Type = 'DEF' THEN '234.2' WHEN c.Type = 'GHI' THEN '567.1' END from table1 a join table2 b on a.Cust = b.Customer join table3 c on b.account = c.account and b.channel =c.channel
Why its not working properly? But if i use Select statement instead of update query its working properly.
I cannot seem to find the syntax to combine IN + CASE in a WHERE clause
WHERE ses.BK_MS_SESSION <= '2015-03' AND vis.CAT_DRAW_STATUS = (CASE ses.BK_MS_SESSION WHEN '2015-03' THEN vis.CAT_DRAW_STATUS ELSE CASE stat.BK_MS_VISIT_STATUS WHEN 'T' THEN 'X' ELSE vis.CAT_DRAW_STATUS END END ) IN ('D','R')
I have scenario where i have to pick one particular value from where condition. Here is the example:A store can have different types i-e A or B , A and B or either A or B.
Store Type Sales 11 A 1000 23 A 1980 23 B 50 5 B 560
I want to filter the store in "where clause" where
1)- if the store has type A and B, then assign only A 2)- if the store has type A associated with it then assign A 3)- if the store has type B associated with it, then assign B.
Select Store, sum(sales), Type from table1 where (TYPE]= (case when [TYPE] in ('A','B') then 'A' when [TYPE]='A' then 'A' else 'B'end)) GROUP BY [store], [TYPE]
The above statement is not working for when store has only Type B associated with it.
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?
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
Hello:Is it possible to use CASE expression in AND condition? i.e.------------------------------------------CREATE PROC spBlah( @id INT, @val INT)ASSELECT * FROM aTableWHERE tableID = @idAND ( CASE @val WHEN 1 THEN otherCol = someValue END CASE @val WHEN 2 THEN otherCol != someOtherVlaue END )-------------------------------------------
I there a way to differ the filtering field through a Case expression in the Where claus? ex: Where Case When @StuNum = '' Then S.SSN = @SSN Else S.StuNum = @StuNum End And ... The actual field to filter by differs, but I can't seem to find the right syntax to do this. Any help is appreciated : )
The SUBSTRING function returns @domain.com. I don't want any results to be displayed from my sql statement if it equals @myemail.com. I have other cases too that are not relevant to my question. I'm getting an error that says invalid syntax near <
SELECT DISTINCT CASE CUST_EMLAD_TX WHEN SUBSTRING(CUST_EMLAD_TX, CHARINDEX('@',CUST_EMLAD_TX), LEN(CUST_EMLAD_TX)) <> '@myemail.com' THEN CUST_EMLAD_TX END FROM ...
In the Portal1 case expression in the script at the bottom I would liketo replace where the result 1 is returned, with the substring functionreturned as Portal{SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',Field1) - 8) - (CHARINDEX('tonep', Field1) + 4))}However, I am experiencing errors. I think it is because The substringfunction will not return a number as the case expression expects so Imust incorporate cast or convert, but do not know how. Can you help???SELECT portal1 = CASE WHEN len(Field1) > 5 THEN 1 ELSE '' END,SUBSTRING(Field1, CHARINDEX('tonep', Field1) + 4, (CHARINDEX('.txt',Field1) - 8)- (CHARINDEX('tonep', Field1) + 4)) AS portal,Table.*FROM Table
I'm trying to write a case expression similar to the expression listed below. I basically want to compare the Anniversary date to the current date. Whenever I write the expression like this I get a syntax error.
Msg 102, Level 15, State 1, Line 147
Incorrect syntax near '<'.
Case AnniversaryDate
WHEN AnniversaryDate < GETDATE() Then 'Not Yet'
WHEN AnniversaryDate = GETDATE() Then 'Now"
ELSE 'You Missed it'
END AS Anniversary
Could someone please guide me in the correct syntax for this expression. Thanks
I would appreciate assistance as to how I should correct the following CASE expression::
BilledCarrier = CASE LEN(cur_billed_carrier) WHEN >0 THEN cur_billed_carrier ELSE 'Not Billed' END
I want to return the cur_billed_carrier when its length is >0 but I can't find the correct syntax. My attempt returns an error at the '>' operator. Thank you.
ALTER PROCEDURE dbo.TEST_TOTALCALLS ( @varDate as varchar (255), @StartDate as datetime, @EndDate as datetime ) AS
SELECT CASE @varDate WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME) WHEN 'Month' Then DATENAME(mm, CALLSTARTTIME) END, COUNT(*) as 'Total Calls' FROM CALLMASTER WHERE (COMMERCIALS = '1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <= @EndDate)
GROUP BY CASE @varDate WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME) WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME), DATENAME(mm, CALLSTARTTIME) ' <---this part gave me an error, because of the comma, END ORDER BY CASE @varDate WHEN 'Year' Then DATEPART(yy, CALLSTARTTIME) WHEN 'Quarter' Then DATENAME(qq, CALLSTARTTIME) WHEN 'Month' Then DATEPART(mm, CALLSTARTTIME) END
The month case is giving me an error. I think it has to do with two expressions in one line. Anyone know how to combine that into 1 expression? or is there away to work around it? As I would like to display the month as Name, but group and sort by number. Thx!~
I have to use the Case expression in my query, so I search arround the web and got the following: SELECT title, price, Budget = CASE price WHEN price > 20.00 THEN 'Expensive' WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate' WHEN price < 10.00 THEN 'Inexpensive' ELSE 'Unknown' END,FROM titles It should run OK base on my research in the internet. But my SQL Server gave me error: syntax error arround '>'.I did several search and many people can use the ">" sign or "<" sign in the Case expression, but I just can't use it in my SQL Server, I can't even use any boolean expression, I can only use values.can anyone help me out? My SQL Server Version is SQL Server 2000 Sevice Pack 4.Thanks!
Hi, Maybe this is just totally wrong but this is what I'm trying to do. I want to dynamically insert a value using a CASE expression while executing an external SP from my current SP. EXEC uspAddMessage @UserId, CASE @MyAction WHEN 'DELETE' THEN 'DELETED TRANSACTION.' WHEN 'APPROVE' THEN 'TRANSACTION APPROVED.' WHEN 'REJECT' THEN 'TRANSACTION REJECTED.' END, @DateStamp Keep in mind that this is in my stored proc that is doing the processing. I'm calling this common proc when I complete my transactions. Thanks, S
Hello everyone, is there anyway to use an update sub query in a case expression , something like this for exampleselect username,(case when password ='606' then (update users set username = 'me' where id= '3') else password end) from users i have been googling this issue just with no usefull resultsthanks for any replyMahmoud Manasrah
I am one very frustrated beginner. If it were not for wonderful information in this forum I would have taken early retirement by now!
Table contains information about new and departing computer and phone users in several departments which we support. This is an existing table which I'm trying to clean up. The essential part:
CREATE TABLE [dbo].[HelpDesk_NewUser]( [AutoNumber] [int] IDENTITY(1,1) NOT NULL, [SubmittedDate] [datetime] NULL, [FirstName] [varchar](100) NOT NULL, [LastName] [varchar](100) NOT NULL, [Department] [varchar](100) NOT NULL, [StartDate] [datetime] NULL, [DepartDate] [datetime] NULL [RequestedBy] [varchar](100) NOT NULL, [UpdatedBy] [varchar](16) NOT NULL, [CurrentStatus] [varchar](10) NOT NULL , [DateCurrentStatus] [datetime] NULL, [FormType] [varchar](10) NOT NULL, ) ON [PRIMARY]
There can be more than one record per FirstName, LastName. FormType can be N for new or D for departing. I want to do this for each record: Read the FormType and Department from the record with the most recent activity (SubmittedDate) for each user Convert Department to a 4 character department number Update CurrentStatus with (FormType concatenated with the 4 character dept number) in all records for that user.
I have created another table called UserMostRecent which contains the most recent record for each user.
I have written a query to do this by brute force (read a record, set local variables, update a record), but I would like learn a simpler way to do it. I don't understand the syntax of CASE because it seems to change depending on where it is used.
Here is what I have tried that does not work. Error is "Incorrect syntax near word CASE"
UPDATE HelpDesk_NewUser SET DateCurrentStatus = b.DateMostRecent, CurrentStatus = (b.FormType + a.Department CASE WHEN 'Roads Department' THEN '3000' WHEN 'Engineering and Survey Services' THEN '1900' WHEN 'Waste Management' THEN '8999' WHEN 'Kern Air Pollution Control District' THEN '9149' WHEN 'Environmental Health' THEN '4113' WHEN 'Building Inspection' THEN '2625' WHEN 'Animal Control' THEN '2760' WHEN 'Planning Department' THEN '2750' WHEN 'Community and Economic Development' THEN '5940' WHEN 'Resource Management Agency' THEN '2730' WHEN 'Code Compliance' THEN '2620' WHEN 'Roads Kern Regional Transit' THEN '8998' END) FROM HelpDesk_NewUser a JOIN UserMostRecent b ON (a.LastName = b.LastName and a.FirstName = b.FirstName)
Is it possible? I have a request to create a stored proc that willdynamically add a range to a WHERE clause based on a numeric value of acomment type. If the incoming comment type request is say 10, thewhere clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR ifa 20 is passed in the clause would read IN(20,21.....)So I was thinking that a CASE expression within the proc would be thebest way to go, but have had no luck in finding an example or any otherrelated information regarding CASE exp in a proc.TIABill
I am working to move an application from MySQL to SQL Server. The person who developed the MySQL application has little database experience, and took some shortcuts that the lax nature of MySQL allows. One query with which I am struggling looks something like this:
SELECT StartTime + Offset, min(Sensor), max(Sensor) FROM SensorData WHERE SensorID = @SensorID AND StartTime + Offset > @BeginTime AND StartTime + Offset < @EndTime GROUP BY (StartTime + Offset) / 100 ORDER BY StartTime + Offset
What we are trying to accomplish is to return minimum and maximum sensor values over a number of periods between the BeginTime and EndTime. When I run this query in MySQL on a sample dataset, it returns a small number of rows, with each one being the min/max values for a portion of the overall period.
Under MS SQL Server 2008, SP3, I get the two following error messages: Column 'SensorData.StartTime' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Column 'SensorData.Offset' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
How would I convert an expression like on of these to all upper case first letters with remaining letters lower case? VB has a function for that but sql doesn't seem to. I thought about having a loop go through each character to check for spaces. I've written a couple of similar pieces of code in VB when a while ago, but is there a better way? Thanks :)
Just a couple of typical examples of how the data should appear ~
Dear all, I am using sql server 2005. I have two field firstname and lastname. Actual requirment is if category is doctor then display name as Dr. name else name but when I use following sql stament Whenever one of the name is null it show name as null that is whenever firstname or lastname is null I get name as null
select @DOCNAME = CASE WHEN DR_CUST_CATEGORY = 1 THEN 'Dr.' + ' ' + dr_cust_name + dr_cust_lastname ELSE dr_cust_name + dr_cust_lastname END FROM DOCTOR_MASTER WHERE DR_CUST_DOCTORCODE =@DOCCODE
Please suggest some ideas or any other idea please suggest Thank you.
I'm trying to generate the data for a 2-column table, where both columns are defined as NOT NULL and the second column is a uniqueidentifier.
In SQL Server Management Studio, this works fine:insert into table_3(column_a, column_b) select table_1.column_a, (case when table_2.column_b is NULL then newid() else table_2.column_b end) as column_b from table_1 left outer join table_2 on table_1.column_c = table_2.column_c
That is, column_b of the SELECT result has no NULL values, and all 35,986 rows are successfully inserted into a previously empty table_3. (If I comment out the INSERT INTO clause and project table_2.column_b instead of "(case ... end) as column_b", the SELECT result includes 380 rows with a NULL in column_b, so I know the case expression plus the outer join are working as expected.)
But when I use the SELECT query as the SQL command in an OLE DB Source component that is connected directly to the OLE DB Destination for the result table, I get this error:There was an error with input column "column_b" (445) on input "OLE DB Destination Input" (420 The column status returned was: "The value violated the integrity constraints for the column.".
And sure enough, when I modify the result table to allow NULL in column_b, truncate it, and re-run the data flow, it inserts the exact same 380 rows with a NULL in column_b among the 35,986 rows.
So what is SSIS doing to screw up the results of the SELECT command???
I have been trying to get the following Selects to work using a case expression in the order by section.
I know I can easily separate out the two statements but I want to do a select using the case statement below ; however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.
If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.
Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.
Thanks J declare @date set @date = '2001'
select internalID from section_data_v3
union
select internalID from section_data_v4
order by case when (@date = '2001') then internalID end desc
This is a CASE statement that I am using in the WHERE clause of my query. SQL Syntax checker keeps returning an error (Incorrect syntax near '='). Can anyone help me figure out what I need to do to get this to work?
Case When (@Weekday=-1 and @Saturday=0 and @Sunday=0) Then (L1.[Weekday]=-1 AND L2.[Weekday]=-1 AND L3.[Weekday]=-1 AND L4.[Weekday]=-1) When (@Weekday=0 and @Saturday=-1 and @Sunday=0) Then (L1.Saturday=-1 AND L2.Saturday=-1 AND L3.Saturday=-1 AND L4.Saturday=-1) When (@Weekday=0 and @Saturday=0 and @Sunday=-1) Then (L1.Sunday=-1 AND L2.Sunday=-1 AND L3.Sunday=-1 AND L4.Sunday=-1) When (@Weekday=-1 and @Saturday=-1 and @Sunday=-0) Then (L1.Sunday=0 AND L2.Sunday=0 AND L3.Sunday=0 AND L4.Sunday=0) When (@Weekday=-1 and @Saturday=0 and @Sunday=-1) Then (L1.Saturday=0 AND L2.Saturday=0 AND L3.Saturday=0 AND L4.Saturday=0) When (@Weekday=0 and @Saturday=-1 and @Sunday=-1) Then (L1.[Weekday]=0 AND L2.[Weekday]=0 AND L3.[Weekday]=0 AND L4.[Weekday]=0) Else ((L1.[Weekday]=-1 AND L2.[Weekday]=-1 AND L3.[Weekday]=-1 AND L4.[Weekday]=-1) OR (L1.Saturday=-1 AND L2.Saturday=-1 AND L3.Saturday=-1 AND L4.Saturday=-1) OR (L1.Sunday=-1 AND L2.Sunday=-1 AND L3.Sunday=-1 AND L4.Sunday=-1)) End
I am stumped trying to use case/if type conditions in having clause. Not sure if it's possible or my syntax or both.
Trying to do something like this:
CASE WHEN (dbo.t_COT_Summary.TCD >= dbo.ReportDate(CONVERT(nvarchar(30), GETDATE(), 101))) THEN
HAVING dbo.t_COT_AP_Exclude.Primary_ID IS NOT NULL
ELSE
HAVING dbo.t_COT_AP_Exclude.Primary_ID IS NULL
END
dbo.t_COT_Summary.TCD = Target Completion Date dbo.ReportDate = Previous Business Day Function dbo.t_COT_AP_Exclude.Primary_ID = A left joined ID value I wish to exclude or include in the main query's having.
I hope this makes sense... any suggestions on a better way to do this would be greatly appreciated!
A deveoper just asked me if there is a way to use a case in a where clause. Is this feasible or will we have to do some dynamic sql
where (cr.cb_routine = 1 or cr.cb_urgent = 1 or cr.cb_emergency_room = 1 or cr.cb_on_site_clinic = 1 or cr.cb_retro_request = 1 or cr.cb_initial = 1 or cr.cb_followup = 1 or cr.cb_in_person = 1 or cr.cb_telemed = 1 or cr.df_within is not null or cr.df_provider is not null or cr.df_proc_test_spec is not null or cr.df_provider_area is not null) and p.privacy_level = 10 and pe.Location_ID = @Location and case when @status = 'Pended' then cr.cb_supp_info_need1 = 1 case when @status = 'Criteria for service not met' then cr.cb_criteria_not_1 = 1 case when @status = 'Other' then cr.cb_other_1 = 1 case when @status = 'All' then
WHERE weekdayname(weekday(sfa_admin_sbaccount.add_time)) = case when "Monday" then (((SFA_ADMIN_SBACCOUNT.ADD_TIME)>=Date()-3 And (SFA_ADMIN_SBACCOUNT.ADD_TIME)<Date())) else (((SFA_ADMIN_SBACCOUNT.ADD_TIME)>=Date()-1 And (SFA_ADMIN_SBACCOUNT.ADD_TIME)<Date()))); end
This keeps telling me I'm missing an operator. What I want the query to do is to evaluate the current day's date, then use that to determine whether it needs to set a WHERE clause that goes back 3 days (if it's a Monday) or one day (if it's not a Monday)
