|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Converting Orcale DECODE stmt to T-SQLI've been asked to convert some Oracle code to SQL. As I begin my journey, the first stumbling block I've come across is with this Oracle stmt: Select..... decode(ft.from_resv_id,null,''(Group)'',rn.guest_last_name||decode(rn.guest_first_name,null,'''','', ''||rn.guest_first_name)) "Guest" ..... Googling on decode, I've deciphered the above code as... If ft.from_resv_id IS NULL, return Group, else return Lastname, Firstname (|| used for concatenation) OR just Lastname if Firstname IS NULL Is my analysis correct? I was also wondering how this could be written in T-SQL using CASE (or anything else). I've tried converting it but now have a mental block. Your suggestions are highly appreciated. TIA. Regards COALESCE, maybe?
http://msdn2.microsoft.com/en-us/library/ms190349.aspx And a plus (+) sign for concatenation. ML --- http://milambda.blogspot.com/ Thanks.
I have no experience using COALESCE. I have created a CASE stmt instead. Does this look correct? CASE When ft.from_resv_id IS NULL Then 'Group' CASE When rn.guest_first_name IS NULL Then rn_guest_last_name ELSE rn.guest_last_name+', '+rn.guest_first_name END AS GUEST Thanks again. Show quoteHide quote "ML" wrote: > COALESCE, maybe? > > http://msdn2.microsoft.com/en-us/library/ms190349.aspx > > And a plus (+) sign for concatenation. > > > ML > > --- > http://milambda.blogspot.com/ IF will work for MS Access, but not for SQL Server.
Look up COALESCE and CASE in Books On Line. Case is the more functional version, which allows comparisions (inlcuding like, <>, >, <, is null, etc), COALESCE sinply returns the first non null value in the list. "Rob" <R**@discussions.microsoft.com> wrote in message decode(ft.from_resv_id,null,''(Group)'',rn.guest_last_name||decode(rn.guest_news:D7FFF57F-0476-47B7-843F-747CE6E7E354@microsoft.com... > Hi, > > I've been asked to convert some Oracle code to SQL. As I begin my journey, > the first stumbling block I've come across is with this Oracle stmt: > > Select..... > first_name,null,'''','', ''||rn.guest_first_name)) "Guest" Show quoteHide quote > .... > > Googling on decode, I've deciphered the above code as... If ft.from_resv_id > IS NULL, return Group, else return Lastname, Firstname (|| used for > concatenation) OR just Lastname if Firstname IS NULL > > Is my analysis correct? I was also wondering how this could be written in > T-SQL using CASE (or anything else). I've tried converting it but now have a > mental block. > > Your suggestions are highly appreciated. TIA. > > Regards <DIV>"Rob" <***@discussions.microsoft.com> wrote in message
Show quoteHide quote news:D7FFF57F-0476-47B7-843F-747CE6E7E354@microsoft.com...</DIV>> Hi, Use CASE.> > I've been asked to convert some Oracle code to SQL. As I begin my journey, > the first stumbling block I've come across is with this Oracle stmt: > > Select..... > decode(ft.from_resv_id,null,''(Group)'',rn.guest_last_name||decode(rn.guest_first_name,null,'''','', > ''||rn.guest_first_name)) "Guest" > .... > > Googling on decode, I've deciphered the above code as... If > ft.from_resv_id > IS NULL, return Group, else return Lastname, Firstname (|| used for > concatenation) OR just Lastname if Firstname IS NULL > > Is my analysis correct? I was also wondering how this could be written in > T-SQL using CASE (or anything else). I've tried converting it but now have > a > mental block. > > Your suggestions are highly appreciated. TIA. > decode(ft.from_resv_id,null,''(Group)'',rn.guest_last_name||decode(rn.guest_first_name,null,'''','', ''||rn.guest_first_name)) "Guest" CASE WHEN ft.from_resv_id IS NULL THEN '(Group)' ELSE rn.guest_last_name + CASE WHEN rn.guest_first_name IS null THEN '' WHEN rn.guest_first_name = '' THEN '' ELSE '' + rn.guest_first_name END END David when'',',' '''','', ''||rn.guest_first_name)) If you're doing a lot of conversions, you might want to look at the Oracle
Migration Assistant: http://www.microsoft.com/sql/solutions/migration/oracle/default.mspx -- Show quoteHide quoteThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm "Rob" <R**@discussions.microsoft.com> wrote in message news:D7FFF57F-0476-47B7-843F-747CE6E7E354@microsoft.com... > Hi, > > I've been asked to convert some Oracle code to SQL. As I begin my journey, > the first stumbling block I've come across is with this Oracle stmt: > > Select..... > decode(ft.from_resv_id,null,''(Group)'',rn.guest_last_name||decode(rn.guest_first_name,null,'''','', > ''||rn.guest_first_name)) "Guest" > .... > > Googling on decode, I've deciphered the above code as... If > ft.from_resv_id > IS NULL, return Group, else return Lastname, Firstname (|| used for > concatenation) OR just Lastname if Firstname IS NULL > > Is my analysis correct? I was also wondering how this could be written in > T-SQL using CASE (or anything else). I've tried converting it but now have > a > mental block. > > Your suggestions are highly appreciated. TIA. > > Regards
Other interesting topics
Return a City in a column on max count from another column in a group by
SQL statement increase question Help me performance optimize Strange result Need help with select statement Dynamic SQL load into dynamic table SQL Select using parameter Two instances of sql on one server causes problems timeout expired The opposite to DECLARE.. |
|||||||||||||||||||||||