Solved

CASE Expressions in Sql server

Posted on 2009-05-20
13
199 Views
Last Modified: 2013-11-25
Hi i need to select a column and check if that column has value 'PS', if it has then i need to change the other column value to 'VNE' else WTE'. Here's my code , but iam getting the error message as invalid syntax near '=' . Please help me on this.

SELECT
 Eml.Fileno, eml.loc, eml.cmp=
 CASE
         WHEN eml.loc=='PS'
         THEN 'VNE' ELSE 'WTE' end
 
FROM
 Emp
  LEFT JOIN Eml ON Eml.Emp = Emp.Emp
  LEFT JOIN Audtrl ON Audtrl.srcid = emp.empid
 
WHERE

 Audtrl.AudtrlDt <= '20090515' and eml.fileno is not null)

also i want to do another query where eml.loc should not be selected,  but still need a CASE statement for the column eml.cmp  for the above mentioned functionality. Hope you understood my question.

thanks
0
Comment
Question by:gladstonesheeba
  • 7
  • 6
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24435789
SELECT
 Eml.Fileno, eml.loc, eml.cmp=
 CASE
         WHEN eml.loc ='PS'
         THEN 'VNE' ELSE 'WTE' end
 
FROM
 Emp
  LEFT JOIN Eml ON Eml.Emp = Emp.Emp
  LEFT JOIN Audtrl ON Audtrl.srcid = emp.empid
 
WHERE

 Audtrl.AudtrlDt <= '20090515' and eml.fileno is not null)
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 500 total points
ID: 24435812
try this


SELECT
 Eml.Fileno, eml.loc,
 cmp= CASE WHEN eml.loc='PS'THEN 'VNE' ELSE 'WTE' end
FROM Emp
LEFT JOIN Eml ON Eml.Emp = Emp.Emp
LEFT JOIN Audtrl ON Audtrl.srcid = emp.empid
WHERE Audtrl.AudtrlDt <= '20090515' and eml.fileno is not null
0
 

Author Comment

by:gladstonesheeba
ID: 24435855
Still iam getting the same error message Invalid syntax near '=' at line 2
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24435869
did u try my second query
0
 

Author Comment

by:gladstonesheeba
ID: 24435891
Yes, i tried both the query, but still the same error.
0
 

Author Comment

by:gladstonesheeba
ID: 24435926
sorry,  i tried the second query and it worked. one thing i want to know, how to remove that loc column from the select query, but still i need the case expression for the cmp column.

For example

SELECT
 Eml.Fileno,
 cmp= CASE WHEN eml.loc='PS'THEN 'VNE' ELSE 'WTE' end
FROM Emp
LEFT JOIN Eml ON Eml.Emp = Emp.Emp
LEFT JOIN Audtrl ON Audtrl.srcid = emp.empid
WHERE Audtrl.AudtrlDt <= '20090515' and eml.fileno is not null

how to write a query for that.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:gladstonesheeba
ID: 24436007
oh , iam sorry for the silly question. i figured it out my self. iam trying to change the column name for cmp , but iam getting the error mesage , invalid  syntax near the keyword 'as'

here it is

cmp= CASE WHEN eml.loc='PS'THEN 'VNE' ELSE 'WTE' end as 'company'

I would appreciate your help on this.

Thanks
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24436009
> how to remove that loc column from the select query
you mean within the 'Case' statement ? if you remove that, how will you compare the value and generate the 'CMP' column
0
 

Author Comment

by:gladstonesheeba
ID: 24436076
not within the CASE Statement , from the Select Statement,  just want to remove it from the select query not within the case statement.

instead of select eml.fileno, eml.loc, cmp=case  when eml.loc......

I want to just select these fields

eml.fileno, cmp=case when eml.loc .........

Hope you can find the difference between the two statements that i mentioned above.
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 500 total points
ID: 24436110
u jyst need to remove it from the SELECT list,

SELECT
 Eml.Fileno, emp= CASE WHEN eml.loc='PS'THEN 'VNE' ELSE 'WTE' end
FROM Emp
LEFT JOIN Eml ON Eml.Emp = Emp.Emp
LEFT JOIN Audtrl ON Audtrl.srcid = emp.empid
WHERE Audtrl.AudtrlDt <= '20090515' and eml.fileno is not null
0
 

Author Comment

by:gladstonesheeba
ID: 24436170
Hey Thanks for the reply. it worked.

how to change that cmp field name to "Company code" at the end. iam getting the error message if i do this

emp= CASE WHEN eml.loc='PS'THEN 'VNE' ELSE 'WTE' end as 'Company code'
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 24436200
remove the 'Emp =' that will do

CASE WHEN eml.loc='PS'THEN 'VNE' ELSE 'WTE' end as 'Company code'
0
 

Author Closing Comment

by:gladstonesheeba
ID: 31583698
Thank you so much for your help . Excellent.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now