Re: decode function in ingres

From: Michael Leo <mleo_at_cariboulake.com>
Date: 17 Feb 2003 09:25:00 -0600
Message-ID: <4.3.2.7.2.20030217085823.02e0de48_at_127.0.0.1>

At 06:00 AM 2/17/2003 -0800, krishna wrote:
>Guys,
>
>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?
>
>thanks,
>Krishna.

Krishna,

There is a case statement in Ingres II 2.6 there is an ANSI case expression similar to Oracle's decode function. It is decidely more "wordy" than Oracle's decode function, but here is an example.

• select * from junkt \g Executing . . .

   +-------------+
   |cool |
 +-------------+

   |            1|
   |            0|

   +-------------+
   (2 rows)
   continue

• select case when cool = 1 then 'Y' when cool = 0 then 'N' else '?' end from junkt \g Executing . . .

   +------+
   |col1 |
 +------+

   |Y     |
   |N     |

   +------+
   (2 rows)
   continue
   *

If you aren't running Ingres II 2.6, you can cheat (for your case) with something like this:

• select * from junkt \g Executing . . .

   +-------------+
   |cool |
 +-------------+

   |            1|
   |            0|

   +-------------+
   (2 rows)
   continue

• select charextract('YN', 2-cool) from junkt \g Executing . . .

   +------+
   |col1 |
 +------+

   |Y     |
   |N     |

   +------+
   (2 rows)
   continue
   *

Hope that helps,

Michael Leo mleo_at_cariboulake.com Java, J2EE, .NET Caribou Lake Software http://www.cariboulake.com Oracle, Ingres,

                                                    DB2, Websphere

"We should forget about small efficiencies, say about 97% of the   time: premature optimization is the root of all evil."

• Donald Knuth

Received on Mon Feb 17 2003 - 16:25:00 CET