[prev in list] [next in list] [prev in thread] [next in thread]
List: odtug-sqlplus-l
Subject: RE: Query question repost
From: Messick Charles J Contr ESC/HRXT <Charles.Messick () RANDOLPH ! AF ! MIL>
Date: 2004-10-21 18:46:35
Message-ID: F001.005EE065.20041021114023 () fatcity ! com
[Download RAW message or body]
Rather than using a spool file, why not create a temp table which could
serve the same purpose and is more flexible to your needs. For
instance.....
Declare
cursor name_csr is
select distinct name from <wherever>;
cursor details_csr(name VARCHAR2) is
select blah, blah, blah, rownum
from <wherever>
where table_value = name;
Begin
for name_csr_rec in name_csr loop
for details_csr_rec in details_csr(name_csr_rec.name) loop
insert details_csr_rec.blah....details_csr_rec.rownum into
temp_table;
end loop;
end loop;
End;
Granted, it's not a single query...but it'll work. If the use of ROWNUM
doesn't work in 7.3 then use a counter instead.
CJ
-----Original Message-----
MONINA PARAZO-ROSE
Sent: Thursday, October 21, 2004 11:50 AM
To: Multiple recipients of list ODTUG-SQLPLUS-L
I am reposting my request since I forgot to mention we are on Oracle 7.3
version.
>Hi all,
>
>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.
>
>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
>.
>.
>.
>
>Thank you all in advance for your time and for any help.
Desktop 2005 - You're Virtually There! February 15-17, 2005 Visit
http://www.odtug.com for details. Computer + internet required. Keynote
Speakers: Bill Inmon, Sohaib Abbasi and Thomas Kurian.
--
Author: MONINA PARAZO-ROSE
INET: MONINA@hbgelec.com
Fat City Hosting, San Diego, California -- http://www.fatcity.com
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ODTUG-SQLPLUS-L (or the name
of mailing list you want to be removed from). You may also send the HELP
command for other information (like subscribing).
[Attachment #3 (text/html)]
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<META NAME="Generator" CONTENT="MS Exchange Server version 5.5.2657.73">
<TITLE>RE: Query question repost</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=2>Rather than using a spool file, why not create a temp table which \
could serve the same purpose and is more flexible to your needs. For \
instance.....</FONT></P>
<P><FONT SIZE=2>Declare</FONT>
<BR><FONT SIZE=2> cursor name_csr is</FONT>
<BR><FONT SIZE=2> select distinct name from \
<wherever>;</FONT> <BR><FONT SIZE=2> cursor details_csr(name \
VARCHAR2) is </FONT> <BR><FONT SIZE=2> select \
blah, blah, blah, rownum</FONT> <BR><FONT SIZE=2> from \
<wherever></FONT> <BR><FONT SIZE=2> where \
table_value = name;</FONT> <BR><FONT SIZE=2>Begin</FONT>
<BR><FONT SIZE=2> for name_csr_rec in name_csr loop</FONT>
<BR><FONT SIZE=2> for details_csr_rec in \
details_csr(name_csr_rec.name) loop</FONT> <BR><FONT \
SIZE=2> insert \
details_csr_rec.blah....details_csr_rec.rownum into temp_table;</FONT> <BR><FONT \
SIZE=2> end loop;</FONT> <BR><FONT SIZE=2> \
end loop;</FONT> <BR><FONT SIZE=2>End;</FONT>
</P>
<P><FONT SIZE=2>Granted, it's not a single query...but it'll work. If the use \
of ROWNUM doesn't work in 7.3 then use a counter instead.</FONT></P> <BR>
<BR>
<BR>
<P><FONT SIZE=2>CJ</FONT>
</P>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: ml-errors@fatcity.com [<A \
HREF="mailto:ml-errors@fatcity.com">mailto:ml-errors@fatcity.com</A>] On Behalf Of \
MONINA PARAZO-ROSE</FONT> <BR><FONT SIZE=2>Sent: Thursday, October 21, 2004 11:50 \
AM</FONT> <BR><FONT SIZE=2>To: Multiple recipients of list ODTUG-SQLPLUS-L</FONT>
<BR><FONT SIZE=2>Subject: Query question repost</FONT>
</P>
<BR>
<P><FONT SIZE=2>I am reposting my request since I forgot to mention we are on Oracle \
7.3 version.</FONT> </P>
<BR>
<P><FONT SIZE=2>>Hi all,</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>I would like to solicit your help in setting up an SQL query to \
help us</FONT> <BR><FONT SIZE=2>>move our customer database from one billing \
system to another.</FONT> <BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>In the process of converting our billing system, we came \
across a</FONT> <BR><FONT SIZE=2>>dillemma regarding customer account \
numbers. In our old system, we </FONT> <BR><FONT SIZE=2>>assigned a customer \
number - location number to identify an account. </FONT> <BR><FONT SIZE=2>>The \
customer number and location number are stored in different </FONT> <BR><FONT \
SIZE=2>>tables. In the new system, we will use a sequence number instead of \
</FONT> <BR><FONT SIZE=2>>the location number. The sequence number will also be \
stored separate </FONT> <BR><FONT SIZE=2>>from the customer number.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>For example</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Old System:</FONT>
<BR><FONT SIZE=2>>Customer Bing Chandler who has accounts at 3 different \
locations, will </FONT> <BR><FONT SIZE=2>>have account numbers 7889-2378, \
7889-5467, 7889-3334. The first 4 </FONT> <BR><FONT SIZE=2>>numbers are the \
same and identifies Bing Chandler, the next 4 numbers </FONT> <BR><FONT \
SIZE=2>>identifies the different locations where Bing Chandler has service.</FONT> \
<BR><FONT SIZE=2>></FONT> <BR><FONT SIZE=2>>New System:</FONT>
<BR><FONT SIZE=2>>Bing Chandler will have account numbers 7889-1, 7889-2, \
7889-3.</FONT> <BR><FONT SIZE=2>>Where the first 4 numbers still identifies Bing \
Chandler, and -1, 2 and 3 </FONT> <BR><FONT SIZE=2>>is chrnological and \
refers to the services he has established. Those </FONT> <BR><FONT \
SIZE=2>>numbers are no longer tied to the location. </FONT> <BR><FONT \
SIZE=2>></FONT> <BR><FONT SIZE=2>>MY question is how can I set up my query so \
that when I retrieve all</FONT> <BR><FONT SIZE=2>>accounts in our old system, I \
will be able to assign a sequence number </FONT> <BR><FONT SIZE=2>>that is \
customer specific. We will use the spool file created by this query </FONT> \
<BR><FONT SIZE=2>>to load our customer data into the new system.</FONT> <BR><FONT \
SIZE=2>></FONT> <BR><FONT SIZE=2>>For example</FONT>
<BR><FONT SIZE=2>>account# \
Name Seq #</FONT> <BR><FONT \
SIZE=2>>.</FONT> <BR><FONT SIZE=2>>.</FONT>
<BR><FONT SIZE=2>>7889 2378 Bing Chandler 1</FONT>
<BR><FONT SIZE=2>>7889 5467 Bing Chandler 2</FONT>
<BR><FONT SIZE=2>>7889 3334 Bing Chandler 3</FONT>
<BR><FONT SIZE=2>>7890 3333 Jerry Seinfeld 1</FONT>
<BR><FONT SIZE=2>>7890 2224 Jerry Seinfeld 2</FONT>
<BR><FONT SIZE=2>>7891 5467 Mike Wallace 1</FONT>
<BR><FONT SIZE=2>>7891 3333 Mike Wallace 2</FONT>
<BR><FONT SIZE=2>>7891 9999 Mike Wallace 3</FONT>
<BR><FONT SIZE=2>>7891 10000 Mike Wallace 4</FONT>
<BR><FONT SIZE=2>>.</FONT>
<BR><FONT SIZE=2>>.</FONT>
<BR><FONT SIZE=2>>.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>>Thank you all in advance for your time and for any help.</FONT>
</P>
<P><FONT SIZE=2>Desktop 2005 - You're Virtually There! February 15-17, 2005 Visit <A \
HREF="http://www.odtug.com" TARGET="_blank">http://www.odtug.com</A> for details. \
Computer + internet required. Keynote Speakers: Bill Inmon, Sohaib Abbasi and Thomas \
Kurian.</FONT></P>
<P><FONT SIZE=2>-- </FONT>
<BR><FONT SIZE=2>Author: MONINA PARAZO-ROSE</FONT>
<BR><FONT SIZE=2> INET: MONINA@hbgelec.com</FONT>
</P>
<P><FONT SIZE=2>Fat City Hosting, San Diego, California -- <A \
HREF="http://www.fatcity.com" TARGET="_blank">http://www.fatcity.com</A></FONT> \
<BR><FONT SIZE=2>---------------------------------------------------------------------</FONT>
<BR><FONT SIZE=2>To REMOVE yourself from this mailing list, send an E-Mail \
message</FONT> <BR><FONT SIZE=2>to: ListGuru@fatcity.com (note EXACT spelling of \
'ListGuru') and in the message BODY, include a line containing: UNSUB ODTUG-SQLPLUS-L \
(or the name of mailing list you want to be removed from). You may also send \
the HELP command for other information (like subscribing).</FONT></P>
</BODY>
</HTML>
Desktop 2005 - You're Virtually There! February 15-17, 2005
Visit http://www.odtug.com for details. Computer + internet required.
Keynote Speakers: Bill Inmon, Sohaib Abbasi and Thomas Kurian.
--
Author: Messick Charles J Contr ESC/HRXT
INET: Charles.Messick@RANDOLPH.AF.MIL
Fat City Hosting, San Diego, California -- http://www.fatcity.com
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ODTUG-SQLPLUS-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic