Decode Function Help

building semantic layer
1

Hi All,

I have the following if then else statement and I need to create an object for that using a decode function in my universe.I normally use decode function in my universe and get results but this one looks to be complex and I need your help.The syntax is as follows

Group ID = If =“SA” Then “BY-AS” Else If ( = “SS/RS” And InList(“IM”,“SC/IT”,“SV/VN”,“TM”)) Then “BY-AP” Else If =“SS/RS” And InList(“ANT”,“FSW”,“GG/NP”,“PC”) Then “PM-PC”

My environment is BO 4.1.3 and Oracle 7.2. Is it Possible to achieve using decode function?

Any input is greatly appreciated.

Ramakanth

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

2

In a message dated 00-03-01 10:47:34 EST, you write:

Group ID = If =“SA” Then “BY-AS” Else If ( = “SS/RS” And
InList(“IM”,“SC/IT”,“SV/VN”,“TM”)) Then “BY-AP” Else If =“SS/RS” And InList(“ANT”,“FSW”,“GG/NP”,“PC”) Then “PM-PC”

Try:

decode(system, ‘SA’, ‘BY-AS’, ‘SS/RS’, decode(component, ‘IM’,‘BY-AP’,‘SC/IT’,‘BY-AP’,‘SV/VN’,‘BY-AP’,‘TM’,‘BY-AP’,‘ANT’,‘PM-PC’,‘FSW’, ‘PM-PC’,‘GG/NP’,‘PM-PC’,‘PC’,‘PM-PC’),‘UNKNOWN’)

Replace SYSTEM and COMPONENT with appropriate database table.column values.

As I don’t have your database, I can’t syntax check this, so be careful that all of the quotes and parens match. The idea is that the first decode checks for a SYSTEM value of ‘SA’. If found, then it returns ‘BY-AS’. If the SYSTEM value is ‘SS/RS’ then the list of various values and return codes is provided by a second nested decode. The final “else” of ‘UNKNOWN’ was not specified, but it is my practice with a combination decode as complicated as this one to provide a “catch all” for anything that was not defined correctly, or not defined at all.

There is no equivalent to “In List” in decode. You simply must list each item out with a matching result value. Again, I have not syntax checked this, but it should give you a starting point!

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

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

3

Dave,

Thanks for the help,you pointed me in right direction and it worked.

Ramakanth

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