RE: UPDATE...REPLACE...'...apostrophe...

Tue, 25 Feb 2003 13:52:32 -0800

Title: RE: SQL struggle

SYNTAX for REPLACE is:

 

REPLACE(<col1>,<string_exp>,<exp1>)

 

If you want to replace with nothing, just do this:

 

UPDATE tbl SET col = REPLACE (col, ‘&#39;’,’’);

 

And that should replace all instances of &#39 with nothing. I hope that’s what you were looking for.

 

Saira

 

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gorden-Ozgul, Patricia E
Sent: February 25, 2003 3:49 PM
To: Multiple recipients of list ORACLE-L
Subject: UPDATE...REPLACE...'...apostrophe...

 

I'm running Oracle on Solaris 2.6.

 

I successfully inserted data from a composite file by replacing apostrophes with &#39; by way of sed...s/'/\&#39;/g...beforehand.

 

Now I need to perform an UPDATE, REPLACE...

UPDATE tbl SET col = REPLACE(col, '&#39;', ...with what?)

 

Please advise.

 

Pat

-----Original Message-----
From: Saira Somani [mailto:[EMAIL PROTECTED]
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:[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 ;

Reply via email to