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-----
From: Jacques Kilchoer
[mailto:[EMAIL PROTECTED]
Sent: February 25, 2003 1:57 PM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
Subject: RE: SQL struggle
(see answer below)
> -----Original Message-----
> From: Saira Somani [mailto:[EMAIL PROTECTED]]
>
> 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 ;
