[prev in list] [next in list] [prev in thread] [next in thread] 

List:       odtug-sqlplus-l
Subject:    Re: query help
From:       "MONINA PARAZO-ROSE" <MONINA () hbgelec ! com>
Date:       2004-10-22 16:58:47
Message-ID: F001.005EE18E.20041022095023 () fatcity ! com
[Download RAW message or body]

Thank you all for your insights.  I think I may have misled some of you to think that \
the sequence number already exists in our old system.  We dont have sequence numbers \
now and this is what I need to generate in order to replace the location number with \
the sequence number.   I dont think I can use ROWNUM because the sequence number has \
to reset to 1 when the customer value changes (or is there a way to do this in oracle \
7.3?). 

Messick Charles J asked the question:
Is your intention to create a result set that looks like :

7889	BING, Chandler	1
7889	BING, Chandler	2
7889	BING, Chandler	3
7990	BUFFET, Phoebe	1
8321	GELLER, Ross	1
8321	GELLER, Ross	2
...
Etc....

OR

7889	BING, Chandler	1
7889	BING, Chandler	2
7889	BING, Chandler	3
7990	BUFFET, Phoebe	4
8321	GELLER, Ross	5
8321	GELLER, Ross	6

The first example is exactly what I need to for the new system (the sequence number \
goes back to 1 on a different customer).

For those who may not have read my original post,  I am reposting it below.  

> I would like to solicit your help in setting up an SQL query to help us move our \
> customer database from one billing system to  another.  We are currently on oracle \
> 7.3, SQLPLUS 3.3.3. 
> In the process of converting our billing system, we  came across a dillemma \
> regarding customer account numbers.  In our old  system, we assigned a customer \
> number - location number to identify an account. The customer number and location \
> number  are stored in different tables. In the new system, we will use a sequence \
> number instead of the location number. The sequence number will also be stored \
> separate from the customer number. 
> For example
> 
> Old System:  
> Customer Bing Chandler who has accounts at 3 different locations, will have account \
> numbers 7889-2378, 7889-5467, 7889- 3334.  The first 4 numbers are the same and \
> identifies Bing Chandler, the next 4 numbers identifies the different locations  \
> where Bing Chandler has service. 
> New System:
> Bing Chandler will have account numbers 7889-1, 7889-2, 7889-3.  Where the first 4 \
> numbers still identifies Bing Chandler,  and  -1, 2 and 3 is chrnological and \
> refers to the services he has established.  Those numbers are no longer tied to the \
>  location.  
> 
> MY question is how can I set up my query so that when I retrieve all accounts in \
> our old system, I will be able to assign a  sequence number that is customer \
> specific.  We will use the spool file created by this query to load our customer \
> data into the >new system. 
> For example
> account#    Name        Seq #
> .
> .
> 7889 2378  Bing Chandler  1
> 7889 5467  Bing Chandler  2
> 7889 3334  Bing Chandler  3
> 7890 3333  Jerry Seinfeld  1
> 7890 2224  Jerry Seinfeld  2
> 7891 5467  Mike Wallace  1
> 7891 3333  Mike Wallace  2
> 7891 9999  Mike Wallace  3
> 7891 10000  Mike Wallace  4


[prev in list] [next in list] [prev in thread] [next in thread]