RE: SQL struggle

Thank you for your assistance - it works - and I have one more question:  

How can I also get the SELECT to show me the original item number - i.e with the '-OR'?  

Thanks,
Saira  

-----Original Message-----
Sent: February 25, 2003 1:57 PM
To: 'ORACLE-L_at_fatcity.com'
Cc: 'saira_somani_at_yahoo.com'  

(see answer below)
> -----Original Message-----
> From: Saira Somani [mailto:saira_somani_at_yahoo.com]
>
> Oracle 8.1.7 on AIX 4.3
>
> Here is what my data looks like in a table called item_w:
>
> WHSE_CODE ITEM_NUM LAST_COST
> ------------ ------------------------------ ----------
> HL1 111230 1.12
> CPD-TWH 111230-OR 0
> CPD-TGH 111230-OR 0
> HL1 50034 .91
> MSH-CDS 50034 0
> CPD-TGH 50034-OR 0
> HL1 650300 4.789
> TWH-STAT 650300 0
> CPD-TWH 650300-OR 0
> CPD-TGH 650300-OR 0
>
> If you'll notice, only the items with WHSE_CODE='HL1' have a cost
> associated with them.
>
> What I need to is:
>
> Parse ITEM_NUM for those items which have a suffix of -OR in order to
> compare with an ITEM_NUM without -OR so that I can take the last cost
> from there and display it beside the one that has -OR. Also
> note, there
> are some $0 cost items that don't have a suffix of -OR; I
> would need to
> match those up with a cost as well.
>
> So in the end, I suppose, this is the result I'm looking for:
>
> WHSE_CODE ITEM_NUM LAST_COST LAST_COST_REV
> ------------ ------------- ------------- -------------
> HL1 111230 1.12 1.12
> CPD-TWH 111230-OR 0 1.12
> CPD-TGH 111230-OR 0 1.12
> HL1 50034 0.91 0.91
> MSH-CDS 50034 0 0.91
> CPD-TGH 50034-OR 0 0.91
> HL1 650300 4.789 4.789
> TWH-STAT 650300 0 4.789
> CPD-TWH 650300-OR 0 4.789

> CPD-TGH 650300-OR 0
> 4.789
>
> And if any of you out there use Cognos Impromptu, perhaps you
> could tell
> me how I can achieve these results in a report.
 

Would this work?
 select

    a.whse_code, a.item_num, a.last_cost,     b.last_cost as last_cost_rev
 from

    item_w a, item_w b
 where

    a.last_cost = 0
    and replace (a.item_num, '-OR') = b.item_num     and b.last_cost > 0
union
 select

    c.whse_code, c.item_num, c.last_cost,     c.last_cost as last_cost_rev
 from

    item_w c
 where

    c.last_cost > 0 ;

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Saira Somani
  INET: saira_somani_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).