Equivalent function of "Decode" in ASE

Hello,
I am using Sybase adaptive server for the first time..
I am trying to convert a procedure writen in oracle to Sybase..
Can any one tell me what is the  Equivalent of Decode in ASE??

Thanks
Ashish


0
ashishshah
12/5/2001 2:42:06 PM
sybase.ase.general 8655 articles. 0 followers. Follow

8 Replies
1934 Views

Similar Articles

[PageSpeed] 20
Get it on Google Play
Get it on Apple App Store

See the "CASE" control statement.

-bret


ashishshah wrote:

> Hello,
> I am using Sybase adaptive server for the first time..
> I am trying to convert a procedure writen in oracle to Sybase..
> Can any one tell me what is the  Equivalent of Decode in ASE??
>
> Thanks
> Ashish

0
Bret
12/5/2001 3:33:23 PM
Bret,

Of course CASE stmt is better (at least syntactically)
than cumbersome expressions with characteristic functions,
but I think it would be better off if Sybase would provide
also just straight and nested IF ...THEN... ELSE... stmt
(or at least nested CASE stmt) directly in SQL stmt (not only in T-SQL
!),
cause sometimes it is not so easy (at least syntactically)
to convert nested if-then-else logic to  unnested CASE stmt and you have
to end-up
with cumbersome repeated expressions which again not so easy to modify.

Regards,
Eugene

Bret Halford wrote:

> See the "CASE" control statement.
>
> -bret
>
> ashishshah wrote:
>
> > Hello,
> > I am using Sybase adaptive server for the first time..
> > I am trying to convert a procedure writen in oracle to Sybase..
> > Can any one tell me what is the  Equivalent of Decode in ASE??
> >
> > Thanks
> > Ashish

0
Eugene
12/5/2001 4:37:46 PM
This is a multi-part message in MIME format.
--------------146480F9FBEFB5DF4C0C8BD9
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Eugene Korolkov wrote:
> 
> Bret,
> 
> Of course CASE stmt is better (at least syntactically)
> than cumbersome expressions with characteristic functions,
> but I think it would be better off if Sybase would provide
> also just straight and nested IF ...THEN... ELSE... stmt
> (or at least nested CASE stmt) directly in SQL stmt (not only in T-SQL
> !),
> cause sometimes it is not so easy (at least syntactically)
> to convert nested if-then-else logic to  unnested CASE stmt and you have
> to end-up
> with cumbersome repeated expressions which again not so easy to modify.

I'm not sure I follow.  Can you elaborate with an example perhaps?

Both Ora***'s DECODE and Sybase's CASE statements resolve into value
expressions.  I believe DECODE takes the form of:

DECODE(<target expr>, <val1>, <alt_val1>, <val2>, <alt_val2>, ...,
<default val>)

which directly translates to CASE:

CASE <target expr>
WHEN <val1> THEN <alt_val1>
WHEN <val2> THEN <alt_val2>
....
ELSE <default val>
END

Both can be used in SQL anywhere an value-expression is allowed.
--------------146480F9FBEFB5DF4C0C8BD9
Content-Type: text/x-vcard; charset=us-ascii;
 name="ksherlo.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Sherlock, Kevin
Content-Disposition: attachment;
 filename="ksherlo.vcf"

begin:vcard 
n:Sherlock;Kevin
x-mozilla-html:FALSE
url:http://qwestdex.com
org:Qwest DEX;Information Managment
adr:;;;Omaha;NE;68114;USA
version:2.1
email;internet:ksherlo_AT_qwest.com
title:Staff Information Systems Engineer
x-mozilla-cpt:;3
fn:Kevin Sherlock
end:vcard

--------------146480F9FBEFB5DF4C0C8BD9--

0
Sherlock
12/5/2001 7:05:57 PM
 In DECODE you can substitute n-th DECODE inside
  n-1 DECODE like decode (.....(decode...(decode ...)))
  simulating nested IF stmt, though it also looks awkward comparing
  to straight IF stmt, but in CASE you cannot, you have to combine
  conditions together, notwithstanding that CASE looks much more
  elegant, than DECODE

Let's say you want to write something like this:
select ccy_code,
         if  mult_div_ind = "M" then
           if  ccy_code = "USD"  then 1 else 2
         else if  mult_div_ind = "D" then -1
         else 0
       end
from fxrates

Nor Sybase, nor Oracle  support that syntax.
In Oracle you have to translate this to something like
select decode (mult_div_ind, "M", decode(ccy_code, "USD", 1, 2), "D", -1, 0)
from fxrates

In Sybase if you  try to fit  this in CASE stmt like below:
select ccy_code,
       case
         when mult_div_ind = "M" then
              (case when ccy_code = "USD" then 1 else 2 end case)
         when mult_div_ind = "D" then -1
         else 0
       end
from fxrates

It does not work (at least in my environment: Sybase 11.9/12.0).
Produce syntax error and nothing about syntax for
nesting case stmt in T-SQL guide.

So, eventually you will endup with:
select ccy_code,
       case
         when mult_div_ind = "M" and ccy_code = "USD" then 1
         when mult_div_ind = "M" and ccy_code != "USD" then 2
         when mult_div_ind = "D" then -1
         else 0
       end
from fxrates

It does work, but you have  to write every branch
now completely with repeated expressions and
different logical conditions. I think  in general
it is not the usual approach in programming languages where
obviously nested IF-THEN-ELSE  should be
presented and implemented first.

Regards,
Eugene




"Sherlock, Kevin" wrote:

> Eugene Korolkov wrote:
> >
> > Bret,
> >
> > Of course CASE stmt is better (at least syntactically)
> > than cumbersome expressions with characteristic functions,
> > but I think it would be better off if Sybase would provide
> > also just straight and nested IF ...THEN... ELSE... stmt
> > (or at least nested CASE stmt) directly in SQL stmt (not only in T-SQL
> > !),
> > cause sometimes it is not so easy (at least syntactically)
> > to convert nested if-then-else logic to  unnested CASE stmt and you have
> > to end-up
> > with cumbersome repeated expressions which again not so easy to modify.
>
> I'm not sure I follow.  Can you elaborate with an example perhaps?
>
> Both Ora***'s DECODE and Sybase's CASE statements resolve into value
> expressions.  I believe DECODE takes the form of:
>
> DECODE(<target expr>, <val1>, <alt_val1>, <val2>, <alt_val2>, ...,
> <default val>)
>
> which directly translates to CASE:
>
> CASE <target expr>
> WHEN <val1> THEN <alt_val1>
> WHEN <val2> THEN <alt_val2>
> ...
> ELSE <default val>
> END
>
> Both can be used in SQL anywhere an value-expression is allowed.

0
Eugene
12/5/2001 8:16:22 PM

Eugene Korolkov wrote:

>  In DECODE you can substitute n-th DECODE inside
>   n-1 DECODE like decode (.....(decode...(decode ...)))
>   simulating nested IF stmt, though it also looks awkward comparing
>   to straight IF stmt, but in CASE you cannot, you have to combine
>   conditions together, notwithstanding that CASE looks much more
>   elegant, than DECODE
>
> Let's say you want to write something like this:
> select ccy_code,
>          if  mult_div_ind = "M" then
>            if  ccy_code = "USD"  then 1 else 2
>          else if  mult_div_ind = "D" then -1
>          else 0
>        end
> from fxrates
>
> Nor Sybase, nor Oracle  support that syntax.
> In Oracle you have to translate this to something like
> select decode (mult_div_ind, "M", decode(ccy_code, "USD", 1, 2), "D", -1, 0)
> from fxrates
>
> In Sybase if you  try to fit  this in CASE stmt like below:
> select ccy_code,
>        case
>          when mult_div_ind = "M" then
>               (case when ccy_code = "USD" then 1 else 2 end case)
>          when mult_div_ind = "D" then -1
>          else 0
>        end
> from fxrates
>
> It does not work (at least in my environment: Sybase 11.9/12.0).
> Produce syntax error and nothing about syntax for
> nesting case stmt in T-SQL guide.


The syntax error lies at the very end of your embedded CASE.

Instead of

              (case when ccy_code = "USD" then 1 else 2 end case)

substitute

              (case when ccy_code = "USD" then 1 else 2 end )

-bret


0
Bret
12/5/2001 8:32:28 PM
Thanks Bret,

I am sorry. It does support nested case,
forget my theories, but anyway I still think
IF-THEN-ELSE would be better in that CASE :-)

Regards,
Eugene

Bret Halford wrote:

> Eugene Korolkov wrote:
>
> >  In DECODE you can substitute n-th DECODE inside
> >   n-1 DECODE like decode (.....(decode...(decode ...)))
> >   simulating nested IF stmt, though it also looks awkward comparing
> >   to straight IF stmt, but in CASE you cannot, you have to combine
> >   conditions together, notwithstanding that CASE looks much more
> >   elegant, than DECODE
> >
> > Let's say you want to write something like this:
> > select ccy_code,
> >          if  mult_div_ind = "M" then
> >            if  ccy_code = "USD"  then 1 else 2
> >          else if  mult_div_ind = "D" then -1
> >          else 0
> >        end
> > from fxrates
> >
> > Nor Sybase, nor Oracle  support that syntax.
> > In Oracle you have to translate this to something like
> > select decode (mult_div_ind, "M", decode(ccy_code, "USD", 1, 2), "D", -1, 0)
> > from fxrates
> >
> > In Sybase if you  try to fit  this in CASE stmt like below:
> > select ccy_code,
> >        case
> >          when mult_div_ind = "M" then
> >               (case when ccy_code = "USD" then 1 else 2 end case)
> >          when mult_div_ind = "D" then -1
> >          else 0
> >        end
> > from fxrates
> >
> > It does not work (at least in my environment: Sybase 11.9/12.0).
> > Produce syntax error and nothing about syntax for
> > nesting case stmt in T-SQL guide.
>
> The syntax error lies at the very end of your embedded CASE.
>
> Instead of
>
>               (case when ccy_code = "USD" then 1 else 2 end case)
>
> substitute
>
>               (case when ccy_code = "USD" then 1 else 2 end )
>
> -bret

0
Eugene
12/5/2001 8:57:59 PM
This is a multi-part message in MIME format.
--------------D2882CA12EB43D3F344C2110
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Eugene Korolkov wrote:
> 
>  In DECODE you can substitute n-th DECODE inside
>   n-1 DECODE like decode (.....(decode...(decode ...)))
>   simulating nested IF stmt, though it also looks awkward comparing
>   to straight IF stmt, but in CASE you cannot, you have to combine
>   conditions together, notwithstanding that CASE looks much more
>   elegant, than DECODE
> 
> Let's say you want to write something like this:
> select ccy_code,
>          if  mult_div_ind = "M" then
>            if  ccy_code = "USD"  then 1 else 2
>          else if  mult_div_ind = "D" then -1
>          else 0
>        end
> from fxrates
> 
> Nor Sybase, nor Oracle  support that syntax.
> In Oracle you have to translate this to something like
> select decode (mult_div_ind, "M", decode(ccy_code, "USD", 1, 2), "D", -1, 0)
> from fxrates
> 
> In Sybase if you  try to fit  this in CASE stmt like below:
> select ccy_code,
>        case
>          when mult_div_ind = "M" then
>               (case when ccy_code = "USD" then 1 else 2 end case)
>          when mult_div_ind = "D" then -1
>          else 0
>        end
> from fxrates
> 
> It does not work (at least in my environment: Sybase 11.9/12.0).
> Produce syntax error and nothing about syntax for
> nesting case stmt in T-SQL guide.

Slow down and listen to your ASE server :)
You simply have a syntax error in your CASE statement.  Try:

select ccy_code,
       case
          when mult_div_ind = "M" then
               (case when ccy_code = "USD" then 1 else 2 end )  /* fix
is here */
          when mult_div_ind = "D" then -1
          else 0
        end
 from fxrates
--------------D2882CA12EB43D3F344C2110
Content-Type: text/x-vcard; charset=us-ascii;
 name="ksherlo.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Sherlock, Kevin
Content-Disposition: attachment;
 filename="ksherlo.vcf"

begin:vcard 
n:Sherlock;Kevin
x-mozilla-html:FALSE
url:http://qwestdex.com
org:Qwest DEX;Information Managment
adr:;;;Omaha;NE;68114;USA
version:2.1
email;internet:ksherlo_AT_qwest.com
title:Staff Information Systems Engineer
x-mozilla-cpt:;3
fn:Kevin Sherlock
end:vcard

--------------D2882CA12EB43D3F344C2110--

0
Sherlock
12/5/2001 10:28:33 PM
This is a multi-part message in MIME format.
--------------411CA0263FC0FB2C378D6F6F
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Eugene Korolkov wrote:
> 
> Thanks Bret,
> 
> I am sorry. It does support nested case,
> forget my theories, but anyway I still think
> IF-THEN-ELSE would be better in that CASE :-)
> 

So you want this:

         if  mult_div_ind = "M" then
           if  ccy_code = "USD"  then 1 else 2
         else if  mult_div_ind = "D" then -1
         else 0

instead of this?:
         
         case
         when  mult_div_ind = "M" then
             case when ccy_code = "USD"  then 1 else 2 end
         when  mult_div_ind = "D" then -1
         else 0
         end

Well, I made a copy the "if" syntax you desire, and changed "if" to
"case when", and "else if" to "when" and tacked on 2 "end" statements.

By my count we traded 18 key-strokes between us, and basically we are
speaking the same semantics now.  In fact, I can go better by typing:

case mult_div_ind
when "M" then
    case ccy_code when "USD"  then 1 else 2 end
when "D" then -1
else 0
end

Now we are saying the same exact thing, with equal number of keystrokes.

I guess my point here is that most languages have translations for each
other, it's just a matter of which language you're used to speaking :)
--------------411CA0263FC0FB2C378D6F6F
Content-Type: text/x-vcard; charset=us-ascii;
 name="ksherlo.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Sherlock, Kevin
Content-Disposition: attachment;
 filename="ksherlo.vcf"

begin:vcard 
n:Sherlock;Kevin
x-mozilla-html:FALSE
url:http://qwestdex.com
org:Qwest DEX;Information Managment
adr:;;;Omaha;NE;68114;USA
version:2.1
email;internet:ksherlo_AT_qwest.com
title:Staff Information Systems Engineer
x-mozilla-cpt:;3
fn:Kevin Sherlock
end:vcard

--------------411CA0263FC0FB2C378D6F6F--

0
Sherlock
12/5/2001 10:50:00 PM
Reply:

Similar Artilces:

Precedence of "where" ("of", "is", "will")?
Nobody on #perl6 today could answer this one. Is: Str | Int where { $_ } the same as: (Str | Int) where { $_ } or: Str | (Int where { $_ }) ? Followup questions, Mr. President: What kind of operators are "where", "of", "is", and "will"? Is there a reason that S03 doesn't list them? What are their precedence(s)? -- Chip Salzenberg - a.k.a. - <chip@pobox.com> Open Source is not an excuse to write fun code then leave the actual work to others. Chip Salzenberg writes: &...

.ALLCOL("%COLUMN%", " ", ", ", ", ")
Do you know anyway for me to exclude a subset of columns returned by this function. We have two columns (rec_user and rec_datetime) which are in all of our tables, but when generating triggers I want automatically generate a script which does not include those two columns but does include all other columns in that table. Bruce I should add that I am using PD 9.0.0.580. Bruce "Bruce Lamb" <lamb.bruce@mayo.edu> wrote in message news:6HgI315nCHA.155@forums.sybase.com... > Do you know anyway for me to exclude a subset of columns returned by this > function. ...

why does ASE always prompt " Warning: There is no valid license for ASE server product"
Dear all: I installed Sybase 12.0 on HP-UX 11.0 ,When Sybase is starting ,It always promt the fllow messages: ASE_SERVER_ERROR: 00:00000:00000:2000/11/27 06:31:17.10 kernel Warning: There is no valid license for ASE server product. Server is booting with all the option features disabled. 00:00000:00000:2000/11/27 06:31:17.28 kernel Using config area from primary master device. 00:00000:00000:2000/11/27 06:31:17.30 kernel Configuration Error: Configuration file, '/home/sybase/othp.cfg', does not exist. 00:00000:00000:2000/11/27 06:31:17.33 kernel Warning: A con...

ASE "Events"
Is there anyway to have ASE "notify" an application when data in a table is changed. If not are there any products besides TIBCO's TIB/Adapter that does this? Thanks Nick Yes, Sybase Enterprise Event Broker (SEEB) "Nick N." wrote: > Is there anyway to have ASE "notify" an application when data in a table is > changed. > > If not are there any products besides TIBCO's TIB/Adapter that does this? > > Thanks > Nick Sure. You can put a trigger on the table that sends an RPC to the application (which would in...

Is "if update()" function trust worthy in ASE 12.5 ?
Hi, We know about problems with the if update() function in ASE 11.9 , we were wondering if the function performs better in ASE 12.5 on Sun Solaris 32. Thanks for any information about the subject, Good day, Anna. ---== Posted via the PFCGuide Web Newsreader ==--- http://www.pfcguide.com/_newsgroups/group_list.asp On Sun, 02 Jan 2005 00:13:20 -0800, Anna wrote: > Hi, > > We know about problems with the if update() function in ASE 11.9 , we were > wondering if the function performs better in ASE 12.5 on Sun Solaris 32. What sort of problems are you having...

quotes, quotes, quotes...
I am getting this error and I know what is causing it, but I have no idea how to fix it, any help would be great. The script steps through the /var/log/messages file on a linux server and puts The entries into a mysql database. However when it gets to the 'hlt' line in the messages file it just barfs. The single quotes are freaking it out. I know about quotes but not how to use in this situation. Thanks, Paul Error: May 27 17:53:00 localhost kernel: Checking 'hlt' instruction... OK. <----- doesn't like this in the messages file DBD::mysql::st exec...

ASE 12.0 64-bit & "disk reinit"/"disk refit"
Hi, I have been having problems with "disk reinit" and "disk refit" under ASE 12.0 64-bit [Solaris 7]. Under 11.9.2 I was able to do the "disk reinit" and "disk refit" command when the server was in single user mode [-m boot flag]. But under ASE 12.0 64-bit I get this message when I run the command: 1> disk refit 2> go Msg 5159, Level 16, State 1 Server 'MERCURY', Line 1 Message empty. After talking with Sybase I found out you had to boot up with traceflag 3608 [not in the on-line documentation]: =====================...

Utilizing the "quote" function
Hello All, How do I "quote" line from a newsgroup post in a reply to same? I have been highlighting the sentence that I want to quote, click the "quote icon [in the upper RH corner] but I must be doing something wrong. Right clicking on the sentence and using "copy" does not have the desired effect either. Any help would be most gratefully appreciated! Alain A. Pismo Clam > wrote: > Hello All, > > How do I "quote" line from a newsgroup post in a reply to same? > > I have been highlighting the sentence that I want t...

Right click "search function" and "show html" function is great BUT
Right click "search function" and "show html" function is great BUT what you really need is a function that will launch the URL being highlighted. If I highlight a section of text that has www.mozilla.org in it, it would be nice to have a launch URL function. In Forte Agent I just need to place my cursor inside any URL then right click, then select launch URL and it will launch the URL in the browser. It's a great feature! I find I'm cutting and pasting all day from different sources to launch them. On Fri, 08 Aug 2003 20:58:58 +0100, Neil <n...

double quote
hello there...  i tried everything of think but not working the way i wanted to be... not sure what i'm missing...i'm generating a <span> in code behind and then using in javascript.... here is what i'm doing code behind: int i=0string _keywordID = "keyword";string _name = row["visit_info_nm"].ToString().Trim(); String _getElementByID = String.Format("<span id='{0}' OnClick = \"document.getElementById('{1}').value='{2}';\">{3}</span><br>", i, _keywordID, _name, _name); here is what it generate : <span id='1' OnClick = \"document.getElementById('keyword')...

Replacing "\\" with "\"
Hi all I'm getting this value from a CheckBoxList control - a location of file, i have to remove "\\" and replace it with "\" and pass it to Query, how to do it, i tried with Replace, but coud'nt suceed. "\\\\Blaze10xp\\BLZ_SFS_07\\Sample Excel Files\\Excel Files\\report2.xls" thank's in advance - Prakash.C you tried Replace like this? string newstring = oldstring.Replace(@"\\",@"\");Plese, do not forget to click "Mark as Answer" on the post that helped you. Thanx!My blog: Scenes From A Developer Memory yes i tr...

Are "=" and "LIKE" equivalents when used with constants ?
Hi, I am just wondering if the following two queries will have the same effect in term of performance : select * from myTable where myField = "popo" select * from myTable where myField like "popo" As you can see I use = or LIKE for the same purpose. I hope that in this example the query time and the I/O will be the same. I lokked for indications on that in the Reference Manual, the Transact-SQL documentation without any success on that. I tried in a real database and it seems to perform equally. However I would like to be certain of this. When ASE 11...

"classname" parameter of function "connecttonewobject"
Hi: I'm using function "ConnectToNewObject" to work with Word, the "classname" parameter is "word.application".If I want to work with other OLE classes, like excel, powerpoint,etc,How can I get their "classname"? I'm using Formula One 6, I don't know its classname,so some methods and properties can't be used in my app. Thanks in advance. hi, check in the object browser in pb and go to OLE tab and look in programmable ole objects. Cicy John Shen wrote: > Hi: > I'm using function "ConnectToNewO...

"Me" is better than "You"
Yes I know, strings are frozen. But let me talk about it, I really can't get through the idea of a PC talkin to me. I consider my PC as an extension of myself, not a dumb companion who addresses Me as You. Yes there are times when I get angry with Him while I work and get wrong calculations etc.., but it really is my fault, Me using wrong istructions and eventually wanting to find someone else to blame, but it's Me. And yes, I consider Thunderbird my mail program, reading my mail on my PC as Me. So I personally like to have Me in the header bar as a compact address ...

Web resources about - Equivalent function of "Decode" in ASE - sybase.ase.general

Resources last updated: 12/27/2015 2:59:59 AM