BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Sybase Equivalent to IIF


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Mon Sep 21, 1998 10:07 am 
Post subject: Sybase Equivalent to IIF



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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Mon Sep 21, 1998 10:59 am 
Post subject: Sybase Equivalent to IIF



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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Mon Sep 21, 1998 11:03 am 
Post subject: Sybase Equivalent to IIF



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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Mon Sep 21, 1998 11:59 am 
Post subject: Sybase Equivalent to IIF



>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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Mon Sep 21, 1998 2:53 pm 
Post subject: Sybase Equivalent to IIF



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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Mon Sep 21, 1998 3:09 pm 
Post subject: Sybase Equivalent to IIF



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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Wed Sep 23, 1998 8:00 am 
Post subject: Sybase Equivalent to IIF



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(((<VARIABLE>)-1) /
14)+1)*14,14)

Where:
substr(" <String containing labels occupying same size substring>",(int(((<VARIABLE>)-1) / <INTERVAL>)+1)*<SUBSTRING SIZE>,<SUBSTRING SIZE>)

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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Wed Sep 30, 1998 8:44 pm 
Post subject: Sybase Equivalent to IIF



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. :-)

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


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0318 seconds using 17 queries. (SQL 0.0032 Parse 0.0009 Other 0.0277)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo