'Decode' function in Sybase

Adev Ahluwalia

unread,

May 30, 1995, 3:00:00 AM5/30/95

to

I come from an ORACLE devolopment environment, where there
is a function called decode(expr, search, result, default) where if
the expr = a search value, then the result is returned other wise the
default is returned... I there such a thing in Sybase?

Adev Ahluwalia
QByte/Coopers and Lybrand
(403) 296 3334
ad...@cuug.ab.ca

Mischa Sandberg

unread,

May 31, 1995, 3:00:00 AM5/31/95

to

In article <D9Equ...@cuug.ab.ca>, ad...@cuug.ab.ca (Adev Ahluwalia)
writes:

>
> I come from an ORACLE devolopment environment, where there
> is a function called decode(expr, search, result, default) where if
> the expr = a search value, then the result is returned other wise the
> default is returned... I there such a thing in Sybase?

People use a lot of workarounds for this one, with nested converts, etc.
For conditionals, the principal tricks are that:
substring(x,1,255) returns x
substring(x,0,255) returns NULL
and ISNULL returns an argument conditionally. Thus, for character args, you
can
translate "if X = 'small' then 'petit' else 'grand'" into:
isnull(substring('petit',charindex(X,'small'),255), 'grand')
For non-char types, you can wrap the (X) in a "convert(varchar...)", and
wrap the whole expression in a "convert(Xtype,...)". Not pretty, but it can
get there.

For more complicated cases, it's possible to build an entire decision table
into a string function. But you'd better document it darn well!

--
Mischa_...@mindlink.bc.ca
Engineers think equations approximate reality.
Physicists think reality approximates the equations.
Mathematicians never make the connection.

Teresa A Larson

unread,

Jun 1, 1995, 3:00:00 AM6/1/95

to

In article <D9Equ...@cuug.ab.ca>, ad...@cuug.ab.ca (Adev Ahluwalia) writes:
|> I come from an ORACLE devolopment environment, where there
|> is a function called decode(expr, search, result, default) where if
|> the expr = a search value, then the result is returned other wise the
|> default is returned... I there such a thing in Sybase?

Sorry, but no. :-( Following is a posting from Bret Halford
that provides something similar (warning, these will look awful to
someone used to DECODE -- actually they look awful even if you're not):

> Problem: I have an employee table with a column called rating, with
> values of 1, 2, or 3. If the rating is 1, in want to return salary
> increased by 20%, if 2, salary increased by 10%, and if 3,
> salary increased by 5%.
>
> The following code will do that:
>
> select name, rating, new_salary =
> (1-abs(sign(rating-1)) * salary + 1.2 +
> (1-abs(sign(rating-2)) * salary + 1.1 +
> (1-abs(sign(rating-3)) * salary + 1.05
> from employee
>
> For those interested in experimenting further, here is a list of
> Point Characteristic Functions:
>
> a=b 1-abs(sign(a-b))
> a!=b abs(sign(a-b))
> a<b 1-sign(1+sign(a-b))
> a<=b sign(1-sign(a-b))
> a>b 1-sign(1-sign(a-b))
> x between a and b sign(1+sign(b-x))-sign(1+sign(a-x))

And another example from da...@sulu.orl.mmc.com (Dash Wendrzyk):

Use decoding:

SELECT Lastname,
Firstname,
isnull(substring('boy ', charindex('M', Sex), 4), 'girl')
FROM Kids

Hope this helps
Teresa Larson

+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
| Teresa A. Larson ------ Hughes Information Technology Corporation |
| NASA/GSFC Code 933.0 voice: (301) 286-7867 |
| Greenbelt, Maryland 20771 fax: (301) 286-1777 |
| Teresa...@gsfc.nasa.gov ISUG Electronic Media Chairperson |
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
Standard disclaimer ...

James Kondek

unread,

Jun 2, 1995, 3:00:00 AM6/2/95

to

> > Problem: I have an employee table with a column called rating, with
> > values of 1, 2, or 3. If the rating is 1, in want to return salary
> > increased by 20%, if 2, salary increased by 10%, and if 3,
> > salary increased by 5%.
> >
> > The following code will do that:
> >
> > select name, rating, new_salary =
> > (1-abs(sign(rating-1)) * salary + 1.2 +
> > (1-abs(sign(rating-2)) * salary + 1.1 +
> > (1-abs(sign(rating-3)) * salary + 1.05
> > from employee
> >

I believe the above example is incorrect. Below is a slightly
revised version along with some sample results:

1> select name,rating, salary, new_salary =
2> ((1-abs(sign(rating-1))) * salary * 1.2) +
3> ((1-abs(sign(rating-2))) * salary * 1.1) +
4> ((1-abs(sign(rating-3))) * salary * 1.05)
5> from ddd
6> go
name rating salary new_salary
---------- ------ ----------- ------------------
Sunny 1 10000 12000.00
Jim 2 10000 11000.00
Bill 3 10000 10500.00
Anne 1 20000 24000.00
Nancy 2 20000 22000.00
Greg 3 20000 21000.00
Sid 1 50000 60000.00
Rita 2 50000 55000.00
Albert 3 50000 52500.00

(9 rows affected)

Jim Kondek
Electronic Data Systems -- Seattle Development Center
sessec01...@eds.com

Izrail Blank RD

unread,

Jun 6, 1995, 3:00:00 AM6/6/95

to

I admit all this is great as a SQL exercize or a job interview puzzle (provided
the interviewee does not read the net :-) ), but I would prefere to view
decoding as a join and have something like that:

select employees.name, employees.rating, new_salary =
employees.salary * rating_to_rate.rate
from employees, rating_to_rate
where employees.rating = rating_to_rate.rating

and if you don't like the idea of having this table in the database
permanently, make it a temporary table.

Isaac Blank

James Kondek (sessec01...@eds.com) wrote:

: > > Problem: I have an employee table with a column called rating, with