convertion of oracle query into Sqlserver

Posted by Rrana under Sql Server on 9/7/2011 | Points: 10 | Views : 700 | Status : [Member] | Replies : 1
Hi,

Below query is in oracle==================


select ACTIVITY_ID,START_DATE,ACTIVITY_NAME,decode(STATUS,0,'WORK IN PROGRESS',1,'COMPLETED',2,'PROBLEM GOING ON')
STATUS_DESCRIPTION,END_DATE
from TBLUSER_ACTIVITY
where USER_ID = TMP_USER_ID
and END_DATE is NULL;

====================================

i want this query into sqlserver


Thanks in advance

Sucharitha Goud
Bank Of America..



Responses

Posted by: Niladri.biswas on: 9/7/2011 [Member] Platinum | Points: 25

Up
0
Down
Try this (Decode is the Case statement here) .
Select

ACTIVITY_ID
,START_DATE
,ACTIVITY_NAME
,STATUS_DESCRIPTION = Case When STATUS = 0 Then 'WORK IN PROGRESS'
When STATUS = 1 Then 'COMPLETED'
When STATUS = 2 Then 'PROBLEM GOING ON'
End
,END_DATE
from TBLUSER_ACTIVITY
where USER_ID = TMP_USER_ID
and END_DATE is NULL


In Sql 11, you can even do this using IIF statement


SQL11 Denali
Select

ACTIVITY_ID
,START_DATE
,ACTIVITY_NAME
,STATUS_DESCRIPTION = IIF(STATUS = 0,'WORK IN PROGRESS', IIF(STATUS = 1,'COMPLETED','PROBLEM GOING ON'),'')
,END_DATE
from TBLUSER_ACTIVITY
where USER_ID = TMP_USER_ID
and END_DATE is NULL;


Hope this helps

Best Regards,
Niladri Biswas

Rrana, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response