- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Spool Data
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2006 04:00 AM
тАО04-18-2006 04:00 AM
Spool Data
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
union
actual query
union
Select count(*) from
Thanks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2006 04:39 AM
тАО04-18-2006 04:39 AM
Re: Spool Data
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2006 04:50 AM
тАО04-18-2006 04:50 AM
Re: Spool Data
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2006 04:52 AM
тАО04-18-2006 04:52 AM
Re: Spool Data
Just ignore union and run as 3 queries..
OK thanks...let me try it.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2006 05:38 AM
тАО04-18-2006 05:38 AM
Re: Spool Data
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2006 05:47 AM
тАО04-18-2006 05:47 AM
Re: Spool Data
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2006 07:17 AM
тАО04-18-2006 07:17 AM
Re: Spool Data
Select
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2006 07:41 AM
тАО04-18-2006 07:41 AM
Re: Spool Data
"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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2006 07:45 AM
тАО04-18-2006 07:45 AM
Re: Spool Data
and add it as last record in the spool file ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО04-18-2006 04:39 PM
тАО04-18-2006 04:39 PM
Re: Spool Data
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