Converting Orcale DECODE stmt to T-SQL

12 Sep 2006 4:25 PM

Rob

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

12 Sep 2006 4:42 PM

ML

COALESCE, maybe?

http://msdn2.microsoft.com/en-us/library/ms190349.aspx

And a plus (+) sign for concatenation.

ML

---
http://milambda.blogspot.com/

12 Sep 2006 5:06 PM

Rob

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

12 Sep 2006 5:04 PM

Jim Underwood

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

Show 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

12 Sep 2006 5:05 PM

David Browne

<DIV>"Rob" <***@discussions.microsoft.com> wrote in message

Show quote

news:D7FFF57F-0476-47B7-843F-747CE6E7E354@microsoft.com...</DIV>> 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.
>

Use CASE.

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

13 Sep 2006 5:06 AM

Roger Wolter[MSFT]

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

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

Show quote

"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