Switch function equivalent in Oracle?? 1

[mike777]

Hello all. New to Oracle. Other than that, I hope that fact is not too painfully obvious.
In MS Access, there is a "switch" function where you can say,
switch(bal > 50000,&quot;Big Account&quot;,bal <=50000,&quot;Small Acct&quot.
Using this, the return value will reduce the return values of the variable bal to two possible values, &quot;Big Account&quot; or &quot;Small Account&quot;. I guess it is a SQL equivalent of a Select Case statement maybe.
My question is, is there an equivalent way to do this in Oracle. We are using Toad (a GUI interface to Oracle), if that makes a difference (don't think it would).
Thanking you in advance.
-Mike

 

[jimbopalmer]

It is almost a sure bet you meant to ask this in an oracle database forum rather than a oracle Financials forum

the decode function may well be what you are looking for, it sounds similar to switch

I am not near my oracle manuals but

decode(sign(bal-50000), 1, 'big account', -1, 'small account')

may be about what you want where the sign function returns 1 or -1 I tried to remain child-like, all I acheived was childish.

 

[mike777]

You are correct about the intended forum. Thanks for the heads up.
Your memory serves you well. The only minor detail that may have been overlooked is the default value:

decode(sign(bal-50000),1,'big account',-1,'small account','unknown')

Of course, I believe this caveat is optional anyway, and in your example a default value certainly isn't needed, since every number is either positive or negative.

I do have a follow-up question:
I was trying to do something like this:
decode(id<450000,1,'New File',0,'Old File','Something is Wrong')

I also tried:
decode(id<450000,TRUE,'New File',False,'Old File','Something is Wrong')

It didn't like the operator &quot;<&quot; in the first argument position. Per your suggestion, I was successful with:

decode(sign(id-450000),1,'New File',-1,'Old File','Something is Wrong')

but I am wondering why it can't evaluate to either TRUE or FALSE. This would be very convenient if it could.

Thanks again.

-Mike