Re: UPDATE...REPLACE...'...apostrophe...

chr(44)

"Gorden-Ozgul, Patricia E" wrote:

> I'm running Oracle on Solaris 2.6.I successfully inserted data from a
> composite file by replacing apostrophes with ' by way of
> sed...s/'/\'/g...beforehand.Now I need to perform an UPDATE,
> REPLACE...UPDATE tbl SET col = REPLACE(col, ''', ...with
> what?)Please advise.Pat
>
> -----Original Message-----
> From: Saira Somani [mailto:saira_somani_at_yahoo.com]
> Sent: Tuesday, February 25, 2003 3:24 PM
> To: Multiple recipients of list ORACLE-L
> Subject: 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-----
> From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_quest.com]
> Sent: February 25, 2003 1:57 PM
> To: 'ORACLE-L_at_fatcity.com'
> Cc: 'saira_somani_at_yahoo.com'
> Subject: RE: SQL struggle
>
> (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 ;
>

--
Scott Canaan (srcdco_at_rit.edu)
(585) 475-7886
"Life is like a sewer, what you get out of it depends on what you put
into it." - Tom Lehrer.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Scott Canaan
  INET: srcdco_at_ritvax.rit.edu

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).