This is annoying me. Been working on some other things and this
complex query came back to bite me. Oracle 9i.

I have a parent record and one or more child records.

I want the query to join on the "Approved" record, (Status = 'A') where
it exists and if it doesn' exist then join on the draft record (Status
= 'D'). There is always one draft record, but there might be an
approved record.

I tried some attempts with subselects and NVLs, but they failed. I
didn't save this code.

Can anyone point me to Ask Tom or another example? I looked there
first, but I'm using the wrong keywords.

thanks,

Don

bbcrock@gmail.com wrote:
> This is annoying me. Been working on some other things and this
> complex query came back to bite me. Oracle 9i.
>
> I have a parent record and one or more child records.
>
> I want the query to join on the "Approved" record, (Status = 'A') where
> it exists and if it doesn' exist then join on the draft record (Status
> = 'D'). There is always one draft record, but there might be an
> approved record.
>
> I tried some attempts with subselects and NVLs, but they failed. I
> didn't save this code.
>
> Can anyone point me to Ask Tom or another example? I looked there
> first, but I'm using the wrong keywords.
>
> thanks,
>
> Don

One solution would be to use DECODE ... here's an example you can
modify that does what you want.

SELECT DECODE(asst_storemgr1, 0,
DECODE(asst_storemgr2, 0,
DECODE(asst_storemgr3, 0, 0, asst_storemgr3),
asst_storemgr2), asst_storemgr1) ASST_MANAGER,
DECODE(store_mgr1,0, DECODE(store_mgr2,0, 0, store_mgr2),
store_mgr1) STORE_MANAGER,
REGION_MGR,
REGION_DIR
FROM stores;

taken from my DECODE page in Morgan's Library.

Turn it into an in-line view and query it for the final result set.
--
Puget Sound Oracle Users Group

bbcrock@gmail.com wrote:
> This is annoying me. Been working on some other things and this
> complex query came back to bite me. Oracle 9i.
>
> I have a parent record and one or more child records.
>
> I want the query to join on the "Approved" record, (Status = 'A') where
> it exists and if it doesn' exist then join on the draft record (Status
> = 'D'). There is always one draft record, but there might be an
> approved record.
>
> I tried some attempts with subselects and NVLs, but they failed. I
> didn't save this code.
>
> Can anyone point me to Ask Tom or another example? I looked there
> first, but I'm using the wrong keywords.
>
> thanks,
>
> Don

Is this what you want:


SQL> create table x (parent number);

Table created.

SQL> create table y (child number, app char);

Table created.

SQL> insert into x values (1);

1 row created.

SQL> insert into x values (2);

1 row created.

SQL> insert into x values (3);

1 row created.

SQL> insert into y values (1,'A');

1 row created.

SQL> insert into y values (1,'D');

1 row created.

SQL> insert into y values (2,'D');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from x,y where x.parent=y.child and y.app='A'
2 union
3 select * from x,y where x.parent=y.child and y.app='D'
4 and y.child not in (select child from y where y.app='A');

PARENT CHILD A
---------- ---------- -
1 1 A
2 2 D

SQL>

Regards Andre Broers

I am going to try these examples. They aren't working quite the way I
was thinking of.

this is part of a complex join. I want to so something like:

SELECT child.PK_ID
FROM parent, jointable, child
WHERE parent.type_id = jointable.type_id
AND jointable.type_id = child.type_id
AND Parent.status = 'D'

and this is where it gets tricky:

AND child.status = 'A' but if that value is null, then give me the
child.pk_id where the child.status = 'D'

I need to see one record only. I am just going to write this in the
application language if i can't figure out a simple SQL solution. I
got two records when I looked at the below examples, but I will rewrite
and see what transpires.

thanks!

Don


bbcrock@gmail.com wrote:
> This is annoying me. Been working on some other things and this
> complex query came back to bite me. Oracle 9i.
>
> I have a parent record and one or more child records.
>
> I want the query to join on the "Approved" record, (Status = 'A') where
> it exists and if it doesn' exist then join on the draft record (Status
> = 'D'). There is always one draft record, but there might be an
> approved record.
>
> I tried some attempts with subselects and NVLs, but they failed. I
> didn't save this code.
>
> Can anyone point me to Ask Tom or another example? I looked there
> first, but I'm using the wrong keywords.
>
> thanks,
>
> Don

Andre Broers schreef:

>
> Is this what you want:
Jij hier?
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...