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

Use the CHR function. CHR(39) will display/insert an apostrophe.

Daniel W. Fink

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 ;
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel W. Fink
  INET: optimaldba_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).