After decoding apples, pears, oranges, peaches, grapes - what can be done to
get them into a specific order (not alpha). {oranges, pears, apples, grape,
peaches?}
DanHW wrote in message <19981018215553.14847.00002932_at_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 Mon Oct 19 1998 - 05:44:32 CDT
