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.
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:
create a string with equal length portions containing space-padded results.
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.
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.