OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Decode Query

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

Decode Query [message #7494]

Tue, 17 June 2003 14:18

Michele
Messages: 77
Registered: December 2000

Member

Hello

I have a table

Cntrctr_ext

pers_id cntrctr_no,p_street1,clone_of
1,1229,150 East 55th,
2,,,1229

Basically, I need to write a query that will find all records that have a clone_of value and populate it into the cntrctr_no(which I have already done) and then find the address for (person 1) who has that cntrctr_no and populate that person's address in record 2.

Ex:

pers_id cntrctr_no,p_street1,clone_of
1,1229,150 East 55th,
2,1229,150 Easy 55th,1229

I have the below query but it is not populating the address:

create or replace view clone as
SELECT p.pers_id,
DECODE(p.cntrctr_no,NULL,to_char(p.CLONE_OF),p.cntrctr_no)cntrctr_no,
DECODE(p.cntrctr_no,t_inner.cntrctr_no,t_inner.p_street1,p.p_street1)p_street1,p.clone_of
from cntrctr_ext p,
(SELECT t.pers_id,t.cntrctr_no,t.P_STREET1
from cntrctr_ext t where t.clone_of is null)t_inner
where p.cntrctr_no = t_inner.cntrctr_no(+);

What am I doing wrong? Thanks

Michele

Report message to a moderator

Re: Decode Query [message #7499 is a reply to message #7494]

Wed, 18 June 2003 00:13

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You've misplaced your join. Try the following instead:

SELECT p.pers_id
     , DECODE(p.cntrctr_no,NULL,to_char(p.CLONE_OF),to_char(p.cntrctr_no)) cntrctr_no
     , DECODE(p.cntrctr_no,NULL,t_inner.p_street1,p.p_street1)             p_street1
     , p.clone_of
  FROM cntrctr_ext p
     , ( SELECT t.pers_id
              , t.cntrctr_no
              , t.p_street1
           FROM cntrctr_ext t
          WHERE t.clone_of is null
       ) t_inner
 WHERE <B>p.clone_of = t_inner.cntrctr_no(+)</B>
/

Or even simpler (the first decode isn't really necessary. You can use NVL:

SELECT p.pers_id
     , NVL(p.cntrctr_no,p.CLONE_OF)                                        cntrctr_no
     , DECODE(p.cntrctr_no,NULL,t_inner.p_street1,p.p_street1)             p_street1
     , p.clone_of
  FROM cntrctr_ext p
     , ( SELECT t.pers_id
              , t.cntrctr_no
              , t.p_street1
           FROM cntrctr_ext t 
          WHERE t.clone_of is null
       ) t_inner
 WHERE p.clone_of = t_inner.cntrctr_no(+)
/

It is possible you could replace the second DECODE with a NVL too, but since the criteria are slightly different (you should evaluate p_street1 against NULL instead of cntrctr_no) I let you be the judge.

HTH,
MHE

Report message to a moderator

Re: Decode Query [message #7510 is a reply to message #7499]

Wed, 18 June 2003 08:12

Michele
Messages: 77
Registered: December 2000

Member

Thank you. Working fine..

Michele

Report message to a moderator

Previous Topic:	Adding up Time Oracle 8/8i
Next Topic:	Multiple Rows into a Single Resulatant Column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 14:26:21 CDT 2024