Sybase Equivalent of Oracle decode() or Access Crosstab? 
Author Message
 Sybase Equivalent of Oracle decode() or Access Crosstab?

Looking for a Sybase equivalent to the decode() function provided by Oracle.
This function allows a single select to produce a pivot table (or Crosstab for
those with MS Access knowledge).

Given a table with three columns:

account #,      month,  amount.  
1               1       10
1               2       20
1               3       40
1               4       5
1               5       77
...
2               1       2
...

Many accounts, tweleve months per account.   We'd like to get a table with
thir{*filter*} columns and one row for each account.  

account #,      mth 1   mth 2   mth3    mth4    mth5    ...     mth12
1               10      20      40      5       77      ...     ...
2               ...

The columns would be for account # and Jan thru Dec.   We realise that this can
be achieved with a temporary table and multiple inserts, but we'd like a
general solution if there is one.

Any help would be appreciated.

--
+=====================================================+
Chris Williams
Canberra, Australia
Welcome To The Information Goat Track



Sun, 05 Jul 1998 03:00:00 GMT
 Sybase Equivalent of Oracle decode() or Access Crosstab?


Quote:
Williams) writes:

>Looking for a Sybase equivalent to the decode() function provided by

Oracle.

Sorry bud;  there is no equivalent.  This is an extremely powerful
feature of Oracle that is far too often overlooked.

- Vivek



Mon, 06 Jul 1998 03:00:00 GMT
 Sybase Equivalent of Oracle decode() or Access Crosstab?

Quote:

> [ snipped table inversion question ]

Hi Chris,

Say you had something like this table:
======================================

create table #account
(acct  int,
 month int,
 amt   int)
go

and it was populated as follows:
================================

insert into #account
select 1, 1, 10
insert into #account
select 1, 2, 10
insert into #account
select 1, 3, 10
insert into #account
select 1, 4, 10
insert into #account
select 1, 5, 10
insert into #account
select 1, 6, 10
insert into #account
select 1, 7, 10
insert into #account
select 1, 8, 10
insert into #account
select 1, 9, 10
insert into #account
select 1, 10, 10
insert into #account
select 1, 11, 10
insert into #account
select 1, 12, 10
go

insert into #account
select 2, 1, 20
insert into #account
select 2, 2, 20
insert into #account
select 2, 3, 20
insert into #account
select 2, 4, 20
insert into #account
select 2, 5, 20
insert into #account
select 2, 6, 20
insert into #account
select 2, 7, 20
insert into #account
select 2, 8, 20
insert into #account
select 2, 9, 20
insert into #account
select 2, 10, 20
insert into #account
select 2, 11, 20
go

So that it contained the following:
===================================

1> select * from #account
2> go
 acct        month       amt        
 ----------- ----------- -----------
           1           1          10
           1           2          10
           1           3          10
           1           4          10
           1           5          10
           1           6          10
           1           7          10
           1           8          10
           1           9          10
           1          10          10
           1          11          10
           1          12          10
           2           1          20
           2           2          20
           2           3          20
           2           4          20
           2           5          20
           2           6          20
           2           7          20
           2           8          20
           2           9          20
           2          10          20
           2          11          20

and you executed the following SQL:
===================================

/* find all the accounts */
select distinct acct into #acct_no
from #account
go

/* invert, I said Invert, rise, rise, INVERT!!!! */
select #acct_no.acct,
       mth1 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 1),
       mth2 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 2),
       mth3 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 3),
       mth4 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 4),
       mth5 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 5),
       mth6 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 6),
       mth7 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 7),
       mth8 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 8),
       mth9 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 9),
       mth10 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 10),
       mth11 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 11),
       mth12 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 12)
from #acct_no

and it provided the following output:
=====================================

 acct        mth1        mth2        mth3        mth4        mth5        mth6        mth7        mth8        mth9        mth10       mth11       mth12      
 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
           1          10          10          10          10          10          10          10          10          10          10          10          10
           2          20          20          20          20          20          20          20          20          20          20          20        NULL

(2 rows affected)

is that what you wanted?  :-)

Actually, I guess it's not generic... you can do it as above or move
the data out of the SQL server and use a Unix filter to invert it...

Pablo Sanchez              | Ph # (415) 933.3812        Fax # (415) 933.2821

===============================================================================
"I am accountable for my actions."  http://reality.sgi.com/employees/pablo_corp
   - pablo



Mon, 06 Jul 1998 03:00:00 GMT
 Sybase Equivalent of Oracle decode() or Access Crosstab?

Quote:
: Williams) writes:

: >
: >Looking for a Sybase equivalent to the decode() function provided by
: Oracle.

: Sorry bud;  there is no equivalent.  This is an extremely powerful
: feature of Oracle that is far too often overlooked.

So true.  However, I've seen some pretty clever work-arounds done by some
very creative people posted in this forum.  Perhaps Chris can tell the
group what he needs to accomplish so that some of those creative folks
in this group can give him some idea how it might be done.  Granted, it
won't be elegant, but, if it can be done, it will probably be pretty
interesting.  :-)

To give you an idea, I'm including some old postings that I have saved.

                                Good luck,
                                Teresa Larson

+----------------------------------------------------------------------+
| Teresa A. Larson              ISUG Electronic Media Chair            |
| L{*filter*}AeroSys                 Voice: (301) 805-0494                  |
| 7375 Executive Place          Fax:   (301) 805-0444                  |

+----------------------------------------------------------------------+
                #include <std_disclaimer>

Article: 13800 of comp.databases.sybase
Newsgroups: comp.databases.sybase
Path: post.gsfc.nasa.gov!newsfeed.gsfc.nasa.gov!ames!hookup!olivea!uunet!naples.gain.com!sybase!usenet

Subject: Re: Substituting strings for non-string column values
Content-Type: TEXT/PLAIN; charset=US-ASCII

Lines: 178

Organization: Sybase, Inc.
X-Newsreader: NEWTNews & Chameleon -- TCP/IP for MS Windows from NetManage

Mime-Version: 1.0
Date: Wed, 4 Jan 1995 01:30:14 GMT

Impressive math, but ouch!!!!  I prefer to use substrings as in

   select cols..., substring("Child",1,sign(floor(18/age))*255)+
             substring("{*filter*}",1,sign(floor(age/18)*255)
     from ...

Have used substring in place of characteristic functions on
numerous times .... beauty is concatenating NULL to any string is
the original string....just be sure third arg is 0 or positive...
255 in above is arbitrary max char length...for Child/{*filter*}, 5
would be sufficient....

Jeff Tallman

Quote:
> |>
> |>      select  name,
> |>              isnonzero ( 1 - sign ( 1 + sign ( age - 19 ) ), "Child"
) +
> |>              isnonzero ( 1 - sign ( 1 + sign ( 18 - age ) ), "{*filter*}"
)
> |>      from    person
> |>
> |> Since one of the two characteristic functions will always output
> |> an empty string, we will get the desired output.
> |>
> |> NOW ---
> |>
> |>      The problem is that Sybase has no such function!  There are

several

Quote:
> /* Causes divide by zero to return NULL (4.9 behavior) rather than
aborting */
> go

> select name, age from test
> go

> /* if the characteristic function returns 0, then we can use it to
divide 1
> to get a NULL and use isnull to return a value of our choice.
> The characteristic function will then return either our string, or
the
> single characer "1".  By allowing a leading space in our string, we
can use
> the stuff function to delete the leading character, be it a "1" or a
space.  */

> select name, convert(char(6),
> stuff(isnull(convert(char(6),1/(1-sign(1-sign(age-19)))),"
Child"),1,1,null)+
> stuff(isnull(convert(char(6),1/(1-sign(1+sign(age-19)))),"
{*filter*}"),1,1,null)
> )
> from test
> go

> drop table test
> go

> dbcc traceoff (3610)
> go
> --------------------
> 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))

> What made this problem difficult was the non-numerical output.  A
simpler
> example of the use of these functions might be:

> 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

> -bret

Article: 15187 of comp.databases.sybase
Path: news.gsfc.nasa.gov!newsfeed.gsfc.nasa.gov!cs.umd.edu!eff!news.duke.edu!godot.cc.duq.edu!hudson.lm.com!news.pop.psu.edu!news.cac.psu.edu!howland.reston.ans.net!math.ohio-state.edu!magnus.acs.ohio-state.edu!csn!boulder!news.coop.net!news.den.mmc.com!i
plmail.orl.mmc.com!sulu.orl.mmc.com!dash

Newsgroups: comp.databases.sybase
Subject: Re: IIf Function in Sybase System 10 SQL?
Date: 6 Feb 1995 23:44:00 GMT
Organization: IPL InterNetNews site
Lines: 20
Distribution: world


NNTP-Posting-Host: sulu.orl.mmc.com
Keywords: IIF, Function

|> I will be porting an application from Microsoft Access to Sybase System 10.
|> The application makes heavy use of the IIF function in its SQL statements.
|> The IIF function has the syntax:
|>    IIF(expression,truevalue,falsevalue)
|> For Example:
|> "SELECT  Lastname, Firstname, IIF(Sex = 'M','boy','girl') FROM Kids;"
|>
|> [munch]

Use decoding:

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

----
dash

Article: 17142 of comp.databases.sybase
Path: news.gsfc.nasa.gov!newsfeed.gsfc.nasa.gov!ames!olivea!spool.mu.edu!howland.reston.ans.net!ix.netcom.com!netnews

Newsgroups: comp.databases.sybase
Subject: Re: Translating ORACLE Decode Function to Sybase
Date: 25 Mar 1995 12:44:15 GMT
Organization: Netcom
Lines: 88
Distribution: world


NNTP-Posting-Host: ix-dc2-08.ix.netcom.com


- Show quoted text -

Quote:

>I apologize for the length of this posting but I do appreciate your
>help.  We are migrating an application from Oracle to Sybase and I
>am having trouble with one of the views which contains a DECODEd
>expression.  Most of these can be handled by various perversions of
>isnull and the abs and sign functions, but this one is more complex.

>Suppose table has three int columns a, b, and c.
>View is defined as
>    CREATE VIEW oracle_view AS
>      SELECT  DECODE(b,  a, c,  0, NULL,  a/b)
>        FROM   table ...

>The DECODE function in ORACLE is sort of a case statement on the fly
>so the intent above is as follows:
>    Take the value of b.
>    If b = a, then the value of the expression is the value of c.
>    Else if b = 0, the expression is null.
>    Else the expression is the quotient a divided by b.

>It's the NULL business and the need to avoid division by zero that
>make this out of the ordinary.  I have tried coding the expression
>as a union of subqueries but this turns the query from "you're
>sitting on a row, do something with it" into "Join, Join, Join"
>which is an entirely different and definitely more sluggish beast.

>All suggestions (other than leave it in Oracle) are appreciated.
>You can post here or email me at the address below.  Thanx.

>Larry Barkey


Three possible suggestions:

1. Change the application.  That's among the reasons most Sybase shops
use stored procedures to access data -- you could do the kind of thing
you want to do relatively simply within a stored procedure.  The problem
is that you want a VIEW to do it.

2. If you really want a view and you really want the application to
remain the same, add a column, desired_value, to the table.  Write
insert and update triggers on your table to compute the appropriate
desired_value for every row added or changed.  Have the view retrieve
the desired_value column instead of the DECODEd value.  This obviously
adds overhead to all modification operations on your table, but it does
handle the general case, because you can code all kinds of logic in a
trigger.  Your specific DECODE function, which involves only numeric
data, can be handled without this mechanism, though (see next
suggestion).

3. I don't have a Sybase system to test this on right now, but at least
this should give you the idea.  We could use an auxiliary table, helper,
with the following columns and values:

  b_equals_a  b_equals_0  use_c  use_null  use_division  avoid_zero
  ----------  ----------  -----  --------  ------------  ----------
       0           0        0        0           1            0
       0           1        0      NULL          0            1
       1           0        1        0           0            0
       1           1        1        0           0            1

Add helper to your FROM clause, and the following join to your WHERE
clause:

      b_equals_a = 1 - abs( sign( b - a ) )
  and b_equals_0 = 1 - abs( sign( b ) )

(We could change the sense of b_equals_a and b_equals_0 to get rid of
the "1 -", but I want to keep the exposition straightforward.)

Then, the Sybase equivalent of DECODE( b, a, c, 0, NULL, a/b ) becomes:

  use_c * c + use_null + use_division * ( a / ( b + avoid_zero ) )

The keys here are (1) the auxiliary table to get NULL into the
expression, and (2) the avoid_zero adjustment to avoid dividing by zero.
Of course, the auxiliary table is small and remains in cache if the
operations are performed often, so performance does not suffer
excessively from this contorted approach.

-- Fred


RACOM Computer Professionals

Article: ...

read more »



Tue, 07 Jul 1998 03:00:00 GMT
 Sybase Equivalent of Oracle decode() or Access Crosstab?

Quote:


> Hi Pablo,

Hey there Graham

Quote:
> Hope you don't mind me hijacking your code, but here's what we do:

Definitely do not mind!

Quote:
> with:

> select     acct,
>    sum(amt * (1 - abs(sign(month - 1)))),
>    sum(amt * (1 - abs(sign(month - 2)))),
>    sum(amt * (1 - abs(sign(month - 3)))),
>    sum(amt * (1 - abs(sign(month - 4)))),
>    sum(amt * (1 - abs(sign(month - 5)))),
>    sum(amt * (1 - abs(sign(month - 6)))),
>    sum(amt * (1 - abs(sign(month - 7)))),
>    sum(amt * (1 - abs(sign(month - 8)))),
>    sum(amt * (1 - abs(sign(month - 9)))),
>    sum(amt * (1 - abs(sign(month - 10)))),
>    sum(amt * (1 - abs(sign(month - 11)))),
>    sum(amt * (1 - abs(sign(month - 12))))
> from #account
> group by acct

Neat!

Quote:
> to achieve the same output:

:-)

Quote:

> What do you think?

I think it kicks butt!  I like what you have written better...

Pablo Sanchez              | Ph # (415) 933.3812        Fax # (415) 933.2821

===============================================================================
"I am accountable for my actions."  http://reality.sgi.com/employees/pablo_corp
   - pablo



Tue, 07 Jul 1998 03:00:00 GMT
 Sybase Equivalent of Oracle decode() or Access Crosstab?

Hi Pablo,

Hope you don't mind me hijacking your code, but here's what we do:

Quote:

>> [ snipped table inversion question ]

>Hi Chris,
>Say you had something like this table:
>======================================
>create table #account
>(acct  int,
> month int,
> amt   int)
>go
>and it was populated as follows:
>================================
>insert into #account
>select 1, 1, 10
>insert into #account
>select 1, 2, 10
>insert into #account
>select 1, 3, 10
>insert into #account
>select 1, 4, 10
>insert into #account
>select 1, 5, 10
>insert into #account
>select 1, 6, 10
>insert into #account
>select 1, 7, 10
>insert into #account
>select 1, 8, 10
>insert into #account
>select 1, 9, 10
>insert into #account
>select 1, 10, 10
>insert into #account
>select 1, 11, 10
>insert into #account
>select 1, 12, 10
>go
>insert into #account
>select 2, 1, 20
>insert into #account
>select 2, 2, 20
>insert into #account
>select 2, 3, 20
>insert into #account
>select 2, 4, 20
>insert into #account
>select 2, 5, 20
>insert into #account
>select 2, 6, 20
>insert into #account
>select 2, 7, 20
>insert into #account
>select 2, 8, 20
>insert into #account
>select 2, 9, 20
>insert into #account
>select 2, 10, 20
>insert into #account
>select 2, 11, 20
>go
>So that it contained the following:
>===================================
>1> select * from #account
>2> go
> acct        month       amt        
> ----------- ----------- -----------
>           1           1          10
>           1           2          10
>           1           3          10
>           1           4          10
>           1           5          10
>           1           6          10
>           1           7          10
>           1           8          10
>           1           9          10
>           1          10          10
>           1          11          10
>           1          12          10
>           2           1          20
>           2           2          20
>           2           3          20
>           2           4          20
>           2           5          20
>           2           6          20
>           2           7          20
>           2           8          20
>           2           9          20
>           2          10          20
>           2          11          20
>and you executed the following SQL:
>===================================

OK up to here, but replace the following:

- Show quoted text -

Quote:
>/* find all the accounts */
>select distinct acct into #acct_no
>from #account
>go
>/* invert, I said Invert, rise, rise, INVERT!!!! */
>select #acct_no.acct,
>       mth1 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 1),
>       mth2 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 2),
>       mth3 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 3),
>       mth4 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 4),
>       mth5 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 5),
>       mth6 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 6),
>       mth7 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 7),
>       mth8 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 8),
>       mth9 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 9),
>       mth10 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 10),
>       mth11 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 11),
>       mth12 = (select amt from #account where #account.acct =* #acct_no.acct and #account.month = 12)
>from #acct_no

with:

select  acct,
        sum(amt * (1 - abs(sign(month - 1)))),
        sum(amt * (1 - abs(sign(month - 2)))),
        sum(amt * (1 - abs(sign(month - 3)))),
        sum(amt * (1 - abs(sign(month - 4)))),
        sum(amt * (1 - abs(sign(month - 5)))),
        sum(amt * (1 - abs(sign(month - 6)))),
        sum(amt * (1 - abs(sign(month - 7)))),
        sum(amt * (1 - abs(sign(month - 8)))),
        sum(amt * (1 - abs(sign(month - 9)))),
        sum(amt * (1 - abs(sign(month - 10)))),
        sum(amt * (1 - abs(sign(month - 11)))),
        sum(amt * (1 - abs(sign(month - 12))))
from #account
group by acct

to achieve the same output:

Quote:
>and it provided the following output:
>=====================================
> acct        mth1        mth2        mth3        mth4        mth5        mth6        mth7        mth8        mth9        mth10       mth11       mth12      
> ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
>           1          10          10          10          10          10          10          10          10          10          10          10          10
>           2          20          20          20          20          20          20          20          20          20          20          20        NULL
>(2 rows affected)
>is that what you wanted?  :-)
>Actually, I guess it's not generic... you can do it as above or move
>the data out of the SQL server and use a Unix filter to invert it...
>Pablo Sanchez              | Ph # (415) 933.3812        Fax # (415) 933.2821

>===============================================================================
>"I am accountable for my actions."  http://reality.sgi.com/employees/pablo_corp
>   - pablo

What do you think?
Regards,
Graham


Tue, 07 Jul 1998 03:00:00 GMT
 Sybase Equivalent of Oracle decode() or Access Crosstab?

says...

Quote:


>Williams) writes:

>>Looking for a Sybase equivalent to the decode() function provided by
>Oracle.

>Sorry bud;  there is no equivalent.  This is an extremely powerful
>feature of Oracle that is far too often overlooked.

>- Vivek

True, but can't you just use a small "translation table" and a join to
achieve the same results?  This table would reside-in all likelihood-in
cache and the join would probably not cost much.

People are experimenting with "Delta function" approaches where one of
several possibilities is selected at run-time.  Here's an example:

/* No guarantees here... */
/* (x is defined and set elsewhere) */
/* Say I want column a from table A when x is 5 or column b otherwise */

SELECT (x=5)*a + (x != 5)*b FROM A WHERE....

I think you get the idea; there are papers around on this--Pinnacle
Publishing has a small paperback (about US $25) on the technique...

--
G. Patrick Sand


(301) 925-0791
"Travel Light But Right..."
Microsoft Network is prohibited from redistributing
this work in any form, in whole or in part.   License
to distribute this individual post is available to Microsoft
for $999. Posting without permission constitutes an
agreement to these terms...gps



Tue, 07 Jul 1998 03:00:00 GMT
 Sybase Equivalent of Oracle decode() or Access Crosstab?

Quote:

>Williams) writes:

>>Looking for a Sybase equivalent to the decode() function provided by
>Oracle.

SQL92 CASE statement is an altenative which has the added
benefit of being based on standards and allowing portability.
I am not sure if it exists in the Sybase DBMS at this point.


Sat, 11 Jul 1998 03:00:00 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Sybase Equivalent of Oracle DECODE Function

2. DECODE equivalent in Sybase.

3. Does Sybase have a Case or Decode equivalent?

4. how do i give an if condition in sql query, equivalent like decode in oracle

5. SQL Server equivalent of Oracle's decode function

6. Access Crosstab equivalent

7. Equivalent of DECODE() in Oracle???

8. oracle DECODE equivalent in SQLserver?

9. Oracle Decode equivalent

10. SQL Server equivalent to Oracle Decode

11. SQL Server equivalent of Oracle's decode function

12. Oracle Decode equivalent in SQL server


 
Powered by phpBB® Forum Software