alternate to Decode - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

alternate to Decode

Author

Topic

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-09-30 : 00:52:46

hi i have a view in oracle. in that i am using decode function.
same query i want to write it in sqlserver. what it is the alternate
to decode.

this is a cross tab query

SELECT code, SUM(DECODE(field1, 4, Present_Value, 0)) AS c1, SUM(DECODE(field1, 5, Present_Value, 0)) AS c2,
SUM(DECODE(field1, 6, Present_Value, 0)) AS c3,SUM(DECODE(field1, 9, Present_Value, 0)) AS c4
FROM (SELECT field1,Code, Present_Value FROM table1) DERIVEDTBL GROUP BY code

thanks

suji

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-30 : 01:51:10

I guess Case when is there in oracle too..


SELECT code, SUM(Case Wehn field1 = 4 then Present_Value Else 0 End) AS 
c1, 
SUM(Case When field1=5 then Present_Value Else 0 End) AS c2,
SUM(Case When field1 = 6 Then Present_Value Else 0 End) AS c3,
SUM(Case When field1= 9 Then Present_Value Else 0 End) AS c4
FROM (SELECT field1,Code, Present_Value FROM table1) DERIVEDTBL GROUP BY code

Chirag

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-09-30 : 01:57:17

thanks
i used the same query which u sent, one diff. is i have not used
derivedtbl.
i dont think 'case' is there in oracle. i will check it.

suji

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-09-30 : 02:09:21

what is DERIVEDTBL?
I HAVE searched forum , but i didnt get

suji

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-30 : 02:16:19

quote:
Originally posted by sujeethbala2110

what is DERIVEDTBL?
I HAVE searched forum , but i didnt get

suji

http://www.sql-server-performance.com/jg_derived_tables.asp

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-30 : 02:20:02

quote:
Originally posted by sujeethbala2110

thanks
i used the same query which u sent, one diff. is i have not used
derivedtbl.
i dont think 'case' is there in oracle. i will check it.

suji

In your case the derivedtbl is Derived Table, which filters some of the columns in your main table.. So you will not find it on the forum about it.

Derived Table are somthing like views, from where you can fetch the records filtering it..

I dont remember but i have read somewhere that there is Case statments in Oracle 10g which is equivalent to the Decode.

Chirag

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-30 : 04:07:40

Did you try chirag's query?

Madhivanan

Failing to plan is Planning to fail

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-09-30 : 04:52:29

ya i did. its working.

suji

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-30 : 08:16:24

Have a look at this article too

http://www.oracle-base.com/articles/9i/Case9i.php

Chirag