Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Newbie -Can this done using SQL

Re: Newbie -Can this done using SQL

From: Steve Long <steve-long_at_mediaone.net>
Date: Wed, 10 Oct 2001 01:18:09 GMT
Message-ID: <llNw7.1114$Ry.6530@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 Tue Oct 09 2001 - 20:18:09 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US