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