Re: if then else statement on 2 columns in SQL query?

From: DanHW <danhw_at_aol.com>
Date: 19 Oct 1998 01:55:53 GMT
Message-ID: <19981018215553.14847.00002932@ng115.aol.com>

>Hi, I need to do a very simple if then statement on 2 columns and I was
>wondering if it could be done in an SQL query without PL/SQL. ie some
>variation of decode?
>
>say I have 2 columns X and Y I want to do something like
>if X.column ='Apples' then
>decode(Y.column,'oranges','apples',Y.column)
>
>if not then can I embed a PL/SQL function straight into the query? how would
>the syntax work( with the declares and semi-colons )? Or do I have to create
>a
>function first and then call this function in the SQL query?
>
>Thanks in advance
>
>Joe Grgas
>joeyd_at_hunterlink.net.au

You can nest decode statements ( I believe 20 levels deep) so you can do this

decode (column1, 'O','Orange',

                                     decode(column2,'R', 'red apple',
                                                                        'G',
'green apple',
                                                                         '

yellow apple'))

If column1 is anything other than 'O', it will look at the decode involving column2.

You can embed a SQL function (ie ABS, DECODE, MIN, MAX, etc) right in the query. You can write a function in PL/SQL to do additional checks; if it is purely self-contained (no DB access), there does not seem to be any appreciable performance degradation due to the calling overhead.

The only thing is that if that column is involved in the where clause or group by, etc, it will have to be evaluated for every row before Oracle can determine if a row meets the criteria. This can slow down a query because you will do a full table scan, rather than use any indexes.

Dan Hekimian-Williams Received on Sun Oct 18 1998 - 20:55:53 CDT