Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

cuetzpalin (Programmer)
25 Mar 10 19:26
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 (TechnicalUser)
25 Mar 10 20:52
Hi,
What is in the Header and Trailer?
 

profile

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

cuetzpalin (Programmer)
25 Mar 10 21:01
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 (MIS)
26 Mar 10 10:27
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 (TechnicalUser)
26 Mar 10 17:06
I think you're making it more difficult than it need be.

CODE

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

CODE

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:-

CODE


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 (TechnicalUser)
26 Mar 10 17:10
Forget that last post - with query sub factoring is I believe unavailable in Oracle 8 - doh!

Regards

T

Helpful Member!  taupirho (Programmer)
29 Mar 10 5:35
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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close