Re: decode function in ingres

Hi,

Ingres does support Decoding like oracle.

The syntax which will be used is

1. case expr when expr1 then expr2 when expr3 then expr4 when .... [ else expr5 ] end
2. case when expr1 then expr2 when expr3 then expr4 when .... [ else expr5 ] end

I am giving an example below which can help for the above syntaxes :

Lets say a table table1 has two columns col1 integer, col2 varchar(20) and has the values as

TABLE1 :
col1 col2

   1        a
   2        b
   3        c
   4        d
   5        e

Now, I want to decode the integers at the select time. ( It can be used in any DDL or DML statements )

I will decode 2 as 22 , 3 as 33 and others as 00

Syntax :
1. select case col1 when 2 then 22 when 3 then 33 else 00 end from table1
2. select case when col1 = 2 then 22 when col1 = 3 then 33 else 00 end from table1

Both will give the output as

col1 col2

00        a
22        b
33        c
00        d
00        e

I think it will help you.

Best of Luck,
Guru

"Chris Simon" <c.simonuninstall_at_windowsbangor.ac.uk> wrote in message news:3E5219D3.7050201_at_windowsbangor.ac.uk...
> krishna wrote:
> > Is there any function in Ingres which in equivalent to Decode function
> > in oracle. all I want to do is print 'Y' for 1 and 'N' or 0. any
> > function for this purpose?
>
> You can use either charextract or shift - these both return character
> results and take a numeric argument and a target string. If you can
> evaluate your condition to a number then you can 'perform arithmetic' on
> the string to return what you want!
>
> e.g.
>
> charextract('NY',parameter+1)
> shift('NY',parameter)
>
> shift can return more than one character so it's useful for when you
> want to return, say "Yes" or "No" instead of Y or N,
>
> e.g.
>
> shift('YesNo',parameter*3)
>
> --
> Chris Simon,
> Analyst/Programmer,
> Prifysgol Cymru Bangor - University of Wales Bangor
>
> ** Uninstall Windows to reply **
>
Received on Wed Feb 19 2003 - 09:23:48 CET