| Converting ORACLE statement to SQL [message #77898] |
Mon, 10 December 2001 23:30  |
Noma
Messages: 1
Registered: December 2001
|
Junior Member |
|
|
We have just converted from Oracle to SQL Server Database. I have a query I created in Oracle and it has a DECODE statement as an expression. SQL Server refuses to run this query responding that DECODE function is not recognised.
Oracle Statement:
decode(A.CM_TYPE,'1SHARE','SHARED COMPETENCIES','2LEAD','SHARED COMPETENCIES','2MAN','SHARED COMPETENCIES')
The English version is :
If A.CM_TYPE = 1SHARE then print SHARED COMPETENCIES............
I need an equivalent in SQL.
----------------------------------------------------------------------
|
|
|
|
| Re: Converting ORACLE statement to SQL [message #77910 is a reply to message #77898] |
Wed, 12 December 2001 16:57  |
sokeh
Messages: 77
Registered: August 2000
|
Member |
|
|
I am not sure what you are doing here but from what I can read of your decode, you are basically saying if A.CM_TYPE = '1SHARE' THEN A.CM_TYPE := 'SHARED COMPETENCIES'
ELSIF A.CM_TYPE = '2LEAD' THEN A.CM_TYPE := 'SHARED COMPETENCIES'
ELSIF A.CM_TYPE = '2MAN' THEN A.CM_TYPE := 'SHARED COMPENTENCIES'
END IF;
If this is correct, then here is the sql server version of it:
CASE A.CM_TYPE
WHEN '1SHARE' THEN 'SHARED COMPETENCIES'
WHEN '2LEAD' THEN 'SHARED COMPETENCIES'
WHEN '2MAN' THEN 'SHARED COMPETENCIES'
ELSE NULL
END
I did not test it so there might be some tweaking to be done but this is the conversion of your oracle decode statement.
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
