Re: Newbie -Can this done using SQL

From: Jennifer R. Amon <jamon_at_apk.net>
Date: Fri, 12 Oct 2001 15:36:54 -0400
Message-ID: <Pine.GSO.4.30.0110121535490.6218-100000@junior.apk.net>

How about this: (Note: I didn't try it - no time.)

insert into mytab a
select
b.accountid,
substr(b.zoneid,1,5) || 'Z' || substr(b.zoneid,7),

  b.firstnm,
  b.col4,
  b.col5,
  ...

from
  mytab b
where a.accountid = b.accountid
and substr(a.zoneid,6,1) = 'Y'
and not exists
  (select 'x'
   from mytab c
   where a.accountid = c.accountid
   and c.zoneid = substr(a.zoneid,1,5) || 'Z' || substr(a.zoneid,7));

Jennifer R. Amon

Personal: jamon_at_apk.net            Work Phone: 440-962-2471
Business: jamon_at_moen.com                  FAX: 440-962-2739

Web: http://junior.apk.net/~jamon/

I love music, nature, animals, books, antiques, children, family, travel, and Thin Mint Girl Scout cookies! :-)

On 11 Oct 2001, Sanjay Raj wrote:

> Friends,
> I need the query. If someone can help.
>
>
> Thx
>
> "Steve Long" <steve-long_at_mediaone.net> wrote in message news:<llNw7.1114$Ry.6530_at_typhoon.jacksonville.mediaone.net>...
> > yes, it can be done using just SQL.
> >
> > "Sanjay Raj" <rsanjaynj_at_yahoo.com> wrote in message
> > news:9f767483.0110091104.735452b0_at_posting.google.com...
> > > Hey Oracle Gurus I need a help.
> > > Might be simple. Here is the description of the problem
> > > -----------
> > > I have a table USERLIST. The table has 110 columns (not my design- I
> > > inherited it) . The layout is
> > >
> > > ACCOUNTID VARCHAR2(16)
> > > ZONEID VARCHAR2(12)
> > > FIRSTNM VARCHAR2(20)
> > > LASTNM VARCHAR2(20)
> > > CITY VARCHAR2(30)
> > > STATE VARCHAR2(15)
> > > ZIP VARCHAR2(10)
> > > next column;
> > > next column;
> > > next column;
> > >
> > > The data looks like
> > >
> > > -12345IL, 0415404V, AUSTEN,CHEN,NEW YORK,NY,10011
> > > 125438,0419904A, NATHAN,BROWN,SAN FRANCISCO,CA,94143
> > > 247090167OIL,0151050M,CURT,WEBER,Akron,OH,44304
> > > 7222057300IL,0141051M,RON,BAUM,,,
> > > 7222058190IL,0141051M,PETER,JOHNSON,NEW ORLEANS,LA,70146
> > > 2068842210,0415209V,PAUL,BEFFA,SPRINGFIELD,MO,65807
> > > 7222060580IL,0141051M,Bill,MERRILL,NEW ORLEANS,LA,70146
> > > 379322577, 09840Y0M, GLADSTONE,JONES,CLEVELAND,OH,441061736
> > > 380370953,0960350M,MARY,GALLAGHER,CINCINNATI,OH,452192364
> > > 380370953,0150850M,MARY,GALLAGHER,Cincinnati,OH,45237
> > > -1087634317,01703Y0C,MICHAEL,RUPP,Wilkes-Barre,PA,18701
> > > -1833696950,01806Z5A,Raymond,Lackore,Va. Beach,VA,23454
> > > -1087634432,01703Y0C,Art,Heal,Scranton,PA,18510
> > > -1087634432,01703Z0C,Art,Heal,Scranton,PA,18510
> > >
> > >
> > > Looking at the ZONEID column (position 2) in the last four (4) records
> > > above
> > >
> > > For the 4th Last record above
> > >
> > > -1087634317,01703Y0C,MICHAEL,RUPP,Wilkes-Barre,PA,18701
> > >
> > > Here there is Y in the 6th position of the ZONEID (01703Y0C) column
> > > and for the same ACCTID (-1087634317) there is no ZONEID where there
> > > is a Z in the 6th position (i.e 01703Z0C does not exist). We need to
> > > insert a record with the exact same information except that the 6th
> > > position will be replaced with a Z instead of a Y. That is we will
> > > insert the following record
> > >
> > > -1087634317,01703Z0C,MICHAEL,RUPP,Wilkes-Barre,PA,18701
> > >
> > > For the 3rd last record above
> > >
> > > -1833696950,01806Z5A,Raymond,Lackore,Va. Beach,VA,23454
> > >
> > > There is a Z in the 6th position of the ZONEID field hence we leave
> > > the record as is
> > >
> > > For the last two records
> > >
> > > -1087634432,01703Y0C,Art,Heal,Scranton,PA,18510
> > > -1087634432,01703Z0C,Art,Heal,Scranton,PA,18510
> > >
> > > Both the records are identical except that we have a Y in the 6th
> > > position in one of the records and a Z in the other. Here also we
> > > leave the record as is. No action required.
> > >
> > > So my requirement is to copy the whole record ( just change the 6th
> > > position in the ZONEID field) if ONLY one record for that
> > > ACCTID,ZONEID combination exists and the 6th position of the ZONEID
> > > field is a Y.
> > >
> > > I want to check all the records in the table ( 2 Million records) and
> > > add a record if – for a particular ACCTID, ZONEID combination
> > > the 6th position of the ZONEID is Y only i.e no record exists for the
> > > same ACCTID where the 6th position of the ZONEID is Z.
> > >
> > > Can this be done just by using SQL.
> > >
> > > Thanks in advance.
>
Received on Fri Oct 12 2001 - 14:36:54 CDT