Sybase Equivalent to IIF

building semantic layer
1

I have created a short demo for a client using an Access database. They now want to expand on this using Sybase. I was using the IIF function in the universe with the Access connection now must do the same in Sybase.

Basically I have a numeric field and must group these by a range of values. ie
if ‘number’ < = 36 then return “<36”
else if ‘number’ <=72 then return “36-72” else return “>72”

I need to create this in an object in designer as the user needs to use this in a heirarchy for drilling. I think I will need to use a combination of functions but can’t seem to come up with right combo.

Any ideas, Thanks

Kerry

Listserv Archives (BOB member since 2002-06-25)

2

Your best bet is to create a table with upper and lower limits for each range. Then link that to the ‘number’ field in your main table via the BETWEEN join and return the range name.

More effecient and flexible with less code. If you need to change your ranges, you just adjust the DB, not the objects.

Good luck!
David Jelinek

Listserv Archives (BOB member since 2002-06-25)

3

With Oracle we could use nested ‘decode’ statements. I would assume Sybase has a ‘decode’ function or something like it.

Chris O’Daniel
Programmer/Analyst Sr.
Roadway Express Inc.
(330) 384-9000 ext. 3857
codaniel@roadway.com

Listserv Archives (BOB member since 2002-06-25)

4

Basically I have a numeric field and must group these by a range of values. ie
if ‘number’ < = 36 then return “<36”
else if ‘number’ <=72 then return “36-72” else return “>72”

I don’t know what functions Sybase has, but I use a case statement against the database for a small set values. Sybase should have something that is similar. I would recommend a decode table if you have a large set of values. Here is a case example to define an object:

SELECT
CASE
WHEN objectcount < 36 THEN ‘<36’
WHEN objectcount BETWEEN 36 AND 72 THEN ‘36-72’ ELSE ‘>72’
END (NAMED count_range)
FROM objectcount_table

I hope this helps.

Kurt Kerchner
Southwestern Bell
(314)340-9853

Listserv Archives (BOB member since 2002-06-25)

5

All of the previous answers are good. Another alternative would be to use a stored procedure to return the “buckets” you want.

Sybase 11.5 does have a case statement that should do the trick. -----Original Message-----

Basically I have a numeric field and must group these by a range of values. ie
if ‘number’ < = 36 then return “<36”
else if ‘number’ <=72 then return “36-72” else return “>72”

I don’t know what functions Sybase has, but I use a case statement against the database for a small set values. Sybase should have something that is similar. I would recommend a decode table if you have a large set of values. Here is a case example to define an object:

SELECT
CASE
WHEN objectcount < 36 THEN ‘<36’
WHEN objectcount BETWEEN 36 AND 72 THEN ‘36-72’ ELSE ‘>72’
END (NAMED count_range)
FROM objectcount_table

I hope this helps.

Kurt Kerchner
Southwestern Bell
(314)340-9853

Listserv Archives (BOB member since 2002-06-25)

6

In a message dated 9/21/1998, 12:23:31 PM, BUSOB-L@LISTSERV.AOL.COM writes: << I would assume Sybase has a ‘decode’ function or something like it.>>

Not yet, but it is coming!

To do ranges in Sybase you have to use several different functions, including replicate(), sign(), and charindex(). If the original poster is still interested, I will work up an answer. The problem is that the object ends up being very long, which brings out another limitation of Sybase… the Group BY clause cannot exceed 256 characters.

A soon to be released version of Sybase is supposed to implement the CASE command, but until then the best suggestion is to do as others have already mentioned and build a range table.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

7

Hi listers,

An equivalent technique to Oracle DECODE or IIF() is to use substrings. These work on almost any database, but there is a slight restriction with number ranges in that they must be of equal amounts, and cannot have a ‘catch all’ bucket at the end. We use these quite a lot, and although slightly inefficient they do work very well.

Example:

substr("0 - 14 Days 15 - 28 Days 29 - 41 Days 42 - 56 Days 57 - 70 Days 71- 84 Days > 84 Days ",(int((()-1) /
14)+1)*14,14)

Where:
substr(" ",(int((()-1) / )+1)*,)

If you were clever with the second argument of substr() then you could set upper and lower limits…

Hope this is some help to somebody, it certainly solved a few tricky problems for us!

Phil Morris

Listserv Archives (BOB member since 2002-06-25)

8

In a message dated 98-09-21 11:20:12 EDT, you write:

I have created a short demo for a client using an Access database. They
now want to expand on this using Sybase. I was using the IIF function in the universe with the Access connection now must do the same in Sybase.

Basically I have a numeric field and must group these by a range of values. ie
if ‘number’ < = 36 then return “<36”
else if ‘number’ <=72 then return “36-72” else return “>72”

Kerry:

Try something like the following (assumes whole numbers):

replicate(“<36”, sign(36-x)) +
replicate(“36-72”, sign(73-x)*sign(x-35)) + replicate(“>72”, sign(x-72))

The sign() function works as follows:

x < 0, sign(x) = -1
x = 0, sign(x) = 0
x > 0, sign(x) = 1
x is null, sign(x) is null

replicate() repeats a string from 0 to n times

Since the sign() function returns a value of -1, 0, or 1, the replicate function will repeat your desired label 0 or 1 times. I believe that replicate will ignore the -1 possibility.

So, if x = 2…

replicate(“<36”, sign(36-2)) +
replicate(“36-72”, sign(73-2)*sign(2-35)) + replicate(“>72”, sign(2-72))

becomes

replicate(“<36”, 1) + replicate (“36-72”, (1*-1)) + replicate (“>72”, -1)

which evaluates to

replicate(“<36”, 1) + replicate (“36-72”, (-1)) + replicate (“>72”, -1)

so only the “<36” string will be replicated. I will leave verification of the others to the reader. :slight_smile:

Caveat: I don’t currently have access to a Sybase system to test this, so there may be syntax errors in my logic. But the idea should help you get started.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)