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

[Scott Canaan]

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:[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 ; -- Scott Canaan ([EMAIL PROTECTED]) (585) 475-7886 "Life is like a sewer, what you get out of it depends on what you put into it." - Tom Lehrer.