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.
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
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!~
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 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 am trying to convert this query to slq server 2000 and I cant figureout how to get rid of the IIF statements and make them case statements.If anyone could help I would greatly appreciate it!Thanks!spafaSELECT Jeopardy.Main, Jeopardy.Name, Jeopardy.COMMENTS2,Jeopardy.STATUS, Jeopardy.DENTAL_STATUS, Jeopardy.HLTH_INC,Jeopardy.DNTL_INC, Jeopardy.COMP_HLTH, Jeopardy.COMP_HLTH_DISC,Jeopardy.COMP_PLAN_DESIGN, Jeopardy.COMP_DNTL, Jeopardy.COMP_DNTL_DISC,Jeopardy.OUT_TO_BID, IIf([COMP_HLTH]="Mass BlueCross","YES",IIf([COMP_HLTH]="Out of State BlueCross","YES",IIf([COMP_HLTH]="CT BlueCross","YES",IIf([COMP_HLTH]="Empire Blue Cross","YES","NO"))))AS OTHER_BC_PLAN, IIf([other_bc_plan]="yes",[COMP_HLTH],"") ASBC_PLAN, Jeopardy.LG_RANKING, Jeopardy.LG_SCORE, Jeopardy.DATE_NOTIFIED,Jeopardy.DATE_UPDATED, Now()-([Jeopardy]![DATE_UPDATED]) AS DATEDIFF,Now()-([Jeopardy]![DATE_ADDED]) AS DATEDIFF2,IIf([DateDiff]<8,"*",Null) AS CHANGE, IIf([DateDiff2]<8,"+",Null) AS[ADD], Jeopardy.Rep_Id, Jeopardy.Rep_Name, tblIRIP_QA_NAMES.ADMIN_NAMEAS MSS, AccountOwnership.ANALYST_NAME, AccountOwnership.UND_NAME,AccountOwnership.DNTL_UND_NAME, AccountOwnership.SERVICE_REP,AccountOwnership.SIZE, AccountOwnership.SIZE2, Jeopardy.CYCLE,Jeopardy.DENTAL_CYCLE, IIf([Jeopardy]![cycle] Is Null,[Jeopardy]![DENTA-L_CYCLE],IIf([Jeopardy]![cycle]="N/A",[Jeopardy]![DENTAL_CYCLE],[Jeop-ardy]![cycle])) AS CYCLE2, AccountOwnership.Canc_Date,AccountOwnership.Dntl_Canc_Date, AccountOwnership.EFFDATE,AccountOwnership.Dntl_EFFDATE, AccountOwnership.TOTALHLTH,AccountOwnership.TOTALDNTL, [healthmate]+[classic] AS TotalCross,AccountOwnership.HEALTHMATE, AccountOwnership.CHIP,AccountOwnership.CLASSIC, AccountOwnership.BROKER,AccountOwnership.HLTH_BROKER_1, AccountOwnership_DSC.DISPOSITION,AccountOwnership_DSC.DISPOSITION_MONTH, IIf([DISPOSITION_month] Is NotNull,"YES","NO") AS OC, IIf([DISPOSITION_month] Is NotNull,[DISPOSITION_month],Null) AS OC_MONTHFROM ((AccountOwnership_DSC RIGHT JOIN AccountOwnership ONAccountOwnership_DSC.Main = AccountOwnership.Main) RIGHT JOIN JeopardyON AccountOwnership.Main = Jeopardy.Main) LEFT JOIN tblIRIP_QA_NAMES ONAccountOwnership.REP_ID = tblIRIP_QA_NAMES.Rep_Id ORDER BYJeopardy.DATE_UPDATED DESC; " );--Posted via http://dbforums.com
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
An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.
I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.
If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:
ErrorID ErrorType DateLogged PolicyNumber --------------------------------------------------------------- 1 Test 08/08/2012 Multiple 2 Test 08/08/2012 Multiple
this would suggest the original syntax is close to being accurate but I can not get it to work.
I have experienced problem while trying to use variable with expression based on several other variables in tasks running parallel.
The details are as following:
There is a SSIS package with simple Control flow: one Script Task which actually do nothing and two Execute Process Tasks, they run after Script Task in parallel. Then there are three simple (EvaluateAsExpression = False) string variables ServerName, Folder and JobNumber with values ServerName = €œ\test€?, Folder = €œtest€? and JobNumber = €œ12345€?. And there is one variable FullPath with expression @[User:: ServerName] + "\" + @[User::Folder] + "_" + @[User::JobNumber]. All the variables are of the Package scope. Then in Execute Process Tasks I have similar expressions based on FullPath variable: Execute Process Task 1 has expression @[User::FullPath] + "\date.bat" and Execute Process Task 2 has @[User::FullPath] + "\time.bat" one. As you understand these expressions define what exactly task should execute.
Then I€™m going to execute package from command line so appropriate XML configuration file has been created. The file contains following values for variables described above: ServerName = €œ\LiveServer€?, Folder = €œJob€? and JobNumber = €œ33091€?.
After series of consequent executions I have got following log file:
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\Test est_12345date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?
€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?
€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?
€¦
As you can see one of Execute Process Tasks usually receive correct value of the expression (based on values of variables from the configuration file) while another - incorrect one (based on €œdefault€? values of variables set directly in package). Sometimes wrong value appears in Task 1, next time in Task 2. Situations when both expressions in tasks evaluated correctly are very rare.
Then if you add some more Execute Process Tasks with similar expressions in the package (for ex. simply by copying existing tasks) you€™ll get a good chance to catch error like this:
OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073450774,0x,Reading the variable "User::FullPath" failed with error code 0xC0047084.
OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073647613,0x,The expression "@[User::FullPath] + "\time.bat"" on property "Executable" cannot be evaluated. Modify the expression to be valid.
Seems variable with expression FullPath is locked during evaluation by one of the parallel tasks in such a way that another task can€™t read it value correctly. Can someone help me with the issue? Maybe there are some options I missed which could prevent such behavior of application? Please let me know how I can make the package work correctly.
I am a newbie to SSIS and looking for answer to a simple search/replace style question.
I am building a derived column and looking for some expression syntax that can search for several strings in one column and set a value in a new column.
A bit like a case statement:
CASE ProductLine WHEN 'R1' THEN 'Road' WHEN 'M1' THEN 'Mountain' WHEN 'T1' THEN 'Touring' WHEN 'S1' THEN 'Other sale items' ELSE 'Not for sale' END,
The twist is that 'R1','M1','T1','S1' could feature anywhere in the string that is ProductLine.
I am looking for some syntax to help me with a traditional search/replace style requirement. I am wanting to examine the contents of one column and populate another.
similar to this SQL case statement
CASE ProductLine WHEN 'R1' THEN 'Road' WHEN 'M1' THEN 'Mountain' WHEN 'T1' THEN 'Touring' WHEN 'S2' THEN 'Other sale items' ELSE 'Not for sale' END,
the twist is that R1, M1 etc. can appear anywhere in the ProductLine column.
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Is it possible to dynamically set the font weight (bold vs normal) for a matrix row label? I always get an invalid value message box from the expression editor. For example:
