Re: Newbie -Can this done using SQL

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 &#8211; 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 Thu Oct 11 2001 - 10:28:58 CDT