convertion of oracle query into Sqlserver

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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..

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

Latest Posts