OraFAQ Forum: SQL & PL/SQL » Inbedding a SELECT inside of a DECODE

Home » SQL & PL/SQL » SQL & PL/SQL » Inbedding a SELECT inside of a DECODE

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Inbedding a SELECT inside of a DECODE [message #39128]

Tue, 18 June 2002 13:36

Niels Anderson
Messages: 2
Registered: June 2002

Junior Member

Having a bugger of a time trying to get a select statement to run inside of a decode. Here is the statement that I tried.

"select decode (eq.eqo_gkey, null, null, (select eqo.nbr from equipment eq, equipme
nt_order eqo where eq.eqo_gkey = eqo.eqo_gkey;)) as "Booking #", eq.nbr as
"Eligible_Equip #", eq.owner_id,
eq.loc_type as "L", eq.grade_id as "GRD" from
equipment eq, equipment_uses equse where eq.nbr = equse.eq_nbr and
eq.grade_id = 'PTR' and owner_id
in ('MSK' , 'SHM') and substr(eq.nbr,1,4) not in ('MVIU','MCAU', 'MCHU' );

and then tried reversing the imbedded select:

select decode (eq.eqo_gkey, not null, (select eqo.nbr from equipment eq, equipme
nt_order eqo where eq.eqo_gkey = eqo.eqo_gkey;), null) as "Booking #", eq.nbr as
"Eligible_Equip #", eq.owner_id,
eq.loc_type as "L", eq.grade_id as "GRD" from
equipment eq, equipment_uses equse where eq.nbr = equse.eq_nbr and
eq.grade_id = 'PTR' and owner_id
in ('MSK' , 'SHM') and substr(eq.nbr,1,4) not in ('MVIU','MCAU', 'MCHU' );

Any ideas on what can be done to get this to work. By the way I am running ORACLE 7.3.4.

Report message to a moderator

Re: Inbedding a SELECT inside of a DECODE [message #39134 is a reply to message #39128]

Tue, 18 June 2002 21:56

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Isn't this what you're looking for:

select decode (eq.eqo_gkey, not null, eqo.nbr , null) as "Booking #"
     , eq.nbr as "Eligible_Equip #"
     , eq.owner_id
     , eq.loc_type as "L"
     , eq.grade_id as "GRD" 
  from equipment       eq
     , equipment_uses  equse 
     , equipment_order eqo
 where eq.nbr = equse.eq_nbr 
   and eq.grade_id = 'PTR' 
   and owner_id in ('MSK' , 'SHM') 
   and substr(eq.nbr,1,4) not in ('MVIU','MCAU', 'MCHU' )
   and eq.eqo_gkey = eqo.eqo_gkey
/

And one remark: String terminators (semicolon, slash) aren't allowed within an SQL statement. sql*Plus will consider the statement as finished. Subselects in the select string aren't valid in Oracle 7.x, therefor I advise to use an extra join, selecting the fields necessary in the decode.

HTH,
MHE

Report message to a moderator

Previous Topic:	Re: Running multiple procedures from one script file
Next Topic:	DatabaseLinks

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 11:42:59 CDT 2024