Ingres : Equivalent of the Oracle "decode()" function

Using Open Ingres 1.2, I’m looking for a function that give me the same feature as the “decode()” function with Oracle, or “Case Statement” with Sybase.
We are looking forward moving to Open Ingres 2.0 very soon.

I would like to define a objects call “Crew Category Description” and the definition should be :
Case CrewCatCode: “T” then “Technical”, “C” then “Cabin” Else “Unknown”

As I would like to avoid some joins and therefore I need to build a sort of virtual Reference table to give my users a description based on the code but not using the reference table, as it is only a small amount of values.

Thanks

Pascal

Pascal BRENNER - BusinessObjects Pre-Sales Consultant

“A superior sailor is one who uses his superior judgement to keep out of situations that require the use of his superior skills.”

Executive Computing - Sydney Office - 6 Ryde Road - Hunters Hill 2110 - Australia

Professional Mail : pbrenner@ecg.com.au Personal Mail : pbrenner@technologist.com
Phone : +612 9816 3433
Fax : +612 9816 3455


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

Using Open Ingres 1.2, I’m looking for a function that give me the same
feature as the “decode()” function with Oracle, or “Case Statement” with
Sybase.
We are looking forward moving to Open Ingres 2.0 very soon.

It would be nice wouldn’t it? I havent searched extensively in OI2 for this but there is a tricky little way around it. Here’s an example we used as part of our MANAGERI universe:

left(shift(’ Low Normal High
',ds_pending_job.priority*-10),10

The basis of this being:

  1. create a string with equal length portions containing space-padded results.
  2. transform the input value into a sequential number (i.e. if you want to convert 10,20,30 etc then divide it by 10 3) multiply the sequantial value out by the string section length, shift by this value then return the leftmost of this number.

Cheers,


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

I’d really like to understand why everyone goes to such lengths to avoid a lookup and hard code their SQL so it is inflexible.

Paul


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

Date: Wed, 9 Feb 2000 18:44:58 -0000

From: Paul Andrews ac297@DIAL.PIPEX.COM

I’d really like to understand why everyone goes to such lengths to avoid a lookup and hard code their SQL so it is inflexible.

Paul

This is a very good point Paul, and one that I should have questioned. I 'spose Business Objects can be forgiven for doing it about 10 times in the repository structures but if it’s your OWN database then you shouldn’t short-cut in this fashion. In the long run you’ll find a little lookup table will serve you far better.

Cheers,
Phil Morris

********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager.

This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com



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