Re: Dumb Oracle SQL question

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 03 Sep 2006 19:11:53 -0700
Message-ID: <1157335913.755119@bubbleator.drizzle.com>

bbcrock_at_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

Received on Sun Sep 03 2006 - 21:11:53 CDT