Conversion from Oracle to SQL Server

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

SQL Server 2000 Forums

Transact-SQL (2000)

Conversion from Oracle to SQL Server

Author

Topic

msastry
Starting Member

8 Posts

Posted - 2004-08-19 : 19:56:00

Hi All,
Any one help to convert the followiing (SQL) from Oracle to SQL Server:

OBJ_M_REPOSITORY_doc.M_REPO_C_NAME as "Domain",
decode(OBJ_M_DOCUMENTS.M_DOC_N_TYPE,1,'Full Client',128,'Webi',OBJ_M_DOCUMENTS.M_DOC_N_TYPE) as "Type",
count(( decode(OBJ_M_DOCUMENTS.M_DOC_N_TYPE,1,'Full Client',128,'Webi',OBJ_M_DOCUMENTS.M_DOC_N_TYPE) )) as "Count"
FROM
OBJ_M_REPOSITORY OBJ_M_REPOSITORY_doc,
OBJ_M_DOCUMENTS
WHERE
( OBJ_M_REPOSITORY_doc.M_REPO_N_ID=OBJ_M_DOCUMENTS.M_DOC_N_REPOID )
AND OBJ_M_DOCUMENTS.M_DOC_N_TYPE IN ('1','128')
AND (
( OBJ_M_DOCUMENTS.M_DOC_N_LAT != 1 )
)
GROUP BY
OBJ_M_REPOSITORY_doc.M_REPO_C_NAME ,
decode(OBJ_M_DOCUMENTS.M_DOC_N_TYPE,1,'Full Client',128,'Webi',OBJ_M_DOCUMENTS.M_DOC_N_TYPE)

regards,
msastry

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-25 : 01:26:41

The equivalent of the Oracle decode keyword is CASE. So your expression would be something like:


CASE OBJ_M_DOCUMENTS.M_DOC_N_TYPE
  WHEN 1 THEN 'Full Client'
  WHEN 128 THEN 'Webi'
  ELSE 'Other'
END

Also in SQL Server, use the ANSI join syntax, i.e. Table1 INNER JOIN Table2 ON Table1.col1 = Table2.col3, it is easier to maintain and understand.

OS

msastry
Starting Member

8 Posts

Posted - 2004-08-25 : 21:10:27

Hi mohdowais!
Thanks for reply -- please check the syntax:

Server: Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'inner'.

select OBJ_M_REPOSITORY.M_REPO_C_NAME as "Domain",
CASE OBJ_M_DOCUMENTS.M_DOC_N_TYPE
WHEN 1 THEN 'Full Client'
WHEN 128 THEN 'Webi'
ELSE 'Other'
END,
count(CASE OBJ_M_DOCUMENTS.M_DOC_N_TYPE
WHEN 1 THEN 'Full Client'
WHEN 128 THEN 'Webi'
ELSE 'Other' END)
where
OBJ_M_REPOSITORY inner JOIN OBJ_M_DOCUMENTS
on OBJ_M_REPOSITORY.M_REPO_N_ID=M_DOC_N_REPOID.OBJ_M_DOCUMENTS
AND OBJ_M_DOCUMENTS.M_DOC_N_TYPE IN ('1','128')
and OBJ_M_DOCUMENTS.M_DOC_N_LAT != 1

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-25 : 23:32:34

[code]
SELECT
OBJ_M_REPOSITORY.M_REPO_C_NAME AS Domain,
CASE OBJ_M_DOCUMENTS.M_DOC_N_TYPE
WHEN 1 THEN 'Full Client'
WHEN 128 THEN 'Webi'
ELSE 'Other'
END,
COUNT(CASE OBJ_M_DOCUMENTS.M_DOC_N_TYPE
WHEN 1 THEN 'Full Client'
WHEN 128 THEN 'Webi'
ELSE 'Other'
END)
FROM
OBJ_M_REPOSITORY
INNER JOIN OBJ_M_DOCUMENTS ON OBJ_M_REPOSITORY.M_REPO_N_ID = M_DOC_N_REPOID.OBJ_M_DOCUMENTS
AND OBJ_M_DOCUMENTS.M_DOC_N_TYPE IN ('1','128')
AND OBJ_M_DOCUMENTS.M_DOC_N_LAT != 1
[/code]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

msastry
Starting Member

8 Posts

Posted - 2004-08-26 : 00:15:24

Thanks Guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources