topic Re: Spool Data in Operating System - HP-UX

Spool Data

intp — Tue, 18 Apr 2006 11:00:30 GMT

Hi,

I have to spool 1.5 million records into a flat file.

First line (header record) in the spool file needs to be current date (YYYYMMDD)

starting second line till end of result set
is whatever returned by my actual query.

Last (Trailer) line should be the total count of records in the flat file. Excluding header and trailer records.

I tried using UNION...but the order getting changed and header record comes somewhere in between of the actual query result and same with trailer record too..i guess its sorting
implicitly..how to get it in same order?

Select from dual
union
actual query
union
Select count(*) from

Thanks

Re: Spool Data

Patti Johnson — Tue, 18 Apr 2006 11:39:11 GMT

Just use individual queries.

set feedback off
set heading off
set pages 0
select sysdate from dual;
select * from dual;
select count(*) from dual;

returns these three lines

18-APR-06
X
1

Patti

Re: Spool Data

intp — Tue, 18 Apr 2006 11:50:50 GMT

ok.

This is my problem... this is how the order changes..

select to_char(sysdate,'YYYYMMDD') from dual
union
select 'ABCD' from dual
union
Select to_char(12345) from dual

my actual query return character type data.

Re: Spool Data

intp — Tue, 18 Apr 2006 11:52:26 GMT

Sorry...i got your point...

Just ignore union and run as 3 queries..

OK thanks...let me try it.

Re: Spool Data

TwoProc — Tue, 18 Apr 2006 12:38:12 GMT

You'll need to aggregate the result of the union as a sub-query, and add a fake order by column (1,2,3) external to that subquery. Then that whole thing above is treated once again a subquery, and only the dates themselves are drawn out from the level 2 subquery.

Basically, you need a subquery that is ordered outside of the union, but from stubbed data from inside the union, and then in the outermost query, just don't select the stubbed data as part of the result set.

select a
from
(select a,aa
from
(select to_char(sysdate,'YYYYMMDD') a , 1 aa from dual
union
select 'ABCD' a ,2 aa from dual
union
select to_char(12345) a ,3 aa from dual)
order by 2)
/

A
--------
20060418
ABCD
12345

Re: Spool Data

intp — Tue, 18 Apr 2006 12:47:28 GMT

yes...i tried this way first ...but it takes around 1.5 hrs to complete...may be bcos of to_char and then order by..

if i run as 3 seperate queries..it only takes 15-20mins...i dont need to put to_char or order by .. still testing ...will
update time and performance.

Re: Spool Data

intp — Tue, 18 Apr 2006 14:17:32 GMT

it takes around 25 mins to spool the file (1.5 million records).. order is coming as i expect ..so fine.

Select from dual
union
actual query
union
Select count(*) from

actual query ==> returns 1.5 million records
and takes the most part of execution time.

Select count(*) from ==>
again has to run the same query(almost alike my actual query) again to find the count.

IS there a way i can avoid third query and get the count from / while running my second query ?

Hope i didnt confuse.

Re: Spool Data

Patti Johnson — Tue, 18 Apr 2006 14:41:54 GMT

If you don't set feeback off - the actual query will return the number of rows selected. Then you can reformat the
"1 row selected"
statement to be whatever you need - just a number. It will require a little formatting after the query completes, but it will eliminate reading all the data again to get the count.

Patti

Re: Spool Data

intp — Tue, 18 Apr 2006 14:45:33 GMT

how to capture that "1 Row Selected"
and add it as last record in the spool file ?

Re: Spool Data

Yogeeraj_1 — Tue, 18 Apr 2006 23:39:00 GMT

hi,

One easy way would be to use grep after you have finished spooling the output to your file.

e.g.
grep -v "1 row selected" filename.lis > newspoolfile.lis
echo "1 row selected" >> newspoolfile.lis

hope this helps too!

kind regards
yogeeraj