Loop through record set by customer ID 1

[cuetzpalin]

Hi,

I need help in putting together a script that will query a client table and spool the results after each unique client ID. The spool data will need a trailer and a header. I understand how to write a simple sql query and how to spool data but I don't know how to code for this situation. I imagine I need to use pl/sql and a loop.

Here's an example of what I need:

Table A
--------

Client ID Client Name
--------- -----------
1 John
2 Ray
3 Mary


I need to select * records from Table A and spool the data with a header and trailer for each client ID.

So the output should look ike this:

Header
1 John
Trailer
Header
2 Ray
Trailer
Header
3 Mary
Trailer



Thanks for your help!

- Johnny

 

[Turkbear]

Hi,
What is in the Header and Trailer?




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cuetzpalin]

The header should have the an "H" followed by the current date YYYYMMDD then a number that will be sequential. The trailer should have a "T" followed by the number of records for each client.

Example:
H20100325000001
1 John
T00000000000001
H20100325000002
2 Ray
2 Ray
2 Ray
T00000000000003
H20100325000003
3 Mary
T00000000000001

Thanks!

 

[Dagon]

For the output, you could use spool and dbms_output.put_line. Alternatively, you could look at the UTL_FILE package. The sequence numbers can be generated through a counter.

A simple way of doing the loop might be to have two cursors. One will get you distinct names e.g.

select distinct name from table.

The second will get you all the data associated with that name e.g.

select * from table
where name = <name from first cursor>

That will enable you to build the two loops that you would need quite easily.

You might also be able to do it through SQL*Plus and the "break on" command, although I suspect this would be pushing the limits of that technology.



For Oracle-related work, contact me through Linked-In.

 

[Thargy]

I think you're making it more difficult than it need be.

DROP TABLE CUETZ_TEST CASCADE CONSTRAINTS PURGE;

CREATE TABLE CUETZ_TEST
(
CLIENT_ID NUMBER (2,0),
CLIENT_NAME VARCHAR2(20)
);

INSERT INTO CUETZ_TEST (CLIENT_ID, CLIENT_NAME) VALUES (1,'John');
INSERT INTO CUETZ_TEST (CLIENT_ID, CLIENT_NAME) VALUES (2,'Ray');
INSERT INTO CUETZ_TEST (CLIENT_ID, CLIENT_NAME) VALUES (2,'Ray');
INSERT INTO CUETZ_TEST (CLIENT_ID, CLIENT_NAME) VALUES (3,'Mary');
COMMIT;

Then run the following from sql plus

SET SERVEROUTPUT ON
SET TRIMSPOOL ON
SET LINESIZE 120
SET HEADING OFF
SET FEEDBACK OFF

SPOOL CUETZ_FILE.TXT
WITH DATA AS
(
SELECT COUNT(*) NUM_RECORDS, CLIENT_NAME
  FROM CUETZ_TEST
 GROUP BY CLIENT_NAME
)
SELECT 'H'||TO_CHAR(SYSDATE, 'YYYYMMDD') ||LPAD(ROWNUM,6,0)||CHR(10)||CLIENT_ID||' '||CLIENT_NAME||CHR(10)||'T'||LPAD(DATA.NUM_RECORDS,14,0)
  FROM CUETZ_TEST INNER JOIN DATA USING (CLIENT_NAME);

SPOOL OFF;
QUIT;

which will produce the following output in a text file:-

H20100326000001
2 Ray
T00000000000002

H20100326000002
2 Ray
T00000000000002

H20100326000003
1 John
T00000000000001

H20100326000004

3 Mary
T00000000000001

Is that roughly what you had in mind?



Regards

T

 

[Thargy]

Forget that last post - with query sub factoring is I believe unavailable in Oracle 8 - doh!

Regards

T

 

[taupirho]

This was as close as I could get

1 select * from tom
2* order by id
SQL> /

ID NAME
---------- ----------
1 Tom
1 Dick
1 George
1 Harry
2 Mary
2 Moira
2 June
2 April
2 July
2 Jody
2 Betty

ID NAME
---------- ----------
2 Jane
3 ABC
3 DEF

14 rows selected.

SQL> column x format a20
SQL> set pages 100
SQL> select decode(H,null,null, H || chr(10) ) || id || ' ' || name ||
2 decode(T,null,null,chr(10) || T || chr(10) ) x
3 from
4 (
5 select id, name,
6 case when rn = 1 then 'H' || sysdate || rnk end H,
7 case when cnt = rn then 'T' || sysdate || cnt end T
8 from
9 (
10 select id,name,count(id) over(partition by id) cnt,
11 dense_rank() over(order by id) rnk,
12 row_number() over (partition by id order by id) rn
13 from tom
14 )
15 )
16 /

X
--------------------
H29-MAR-101
1 Tom

1 Dick
1 George
1 Harry
T29-MAR-104

H29-MAR-102
2 Mary

2 Moira
2 June
2 April
2 July
2 Jody
2 Betty
2 Jane
T29-MAR-108

H29-MAR-103
3 ABC

3 DEF
T29-MAR-102


14 rows selected.

SQL>


In order to understand recursion, you must first understand recursion.