chevere
asked on
How do I best add a row count in a footer with a union
I have a file that is layed out as follows: HEADER, DETAILS, FOOTER
There is a union between all 3 as the header and footer are 2 and 5 columns respectively, and the details section has 35.
I'm trying to get a row count from the details section into the footer, but can't figure out a better way than copying the entire details into the footer, doing a row count on that, and modifying the output footer to include it. My details section is over 1000 lines long and uses 76 tables. I'd rather not have it take twice as long to run if there's a better way of going about doing this.
Any ideas?
There is a union between all 3 as the header and footer are 2 and 5 columns respectively, and the details section has 35.
I'm trying to get a row count from the details section into the footer, but can't figure out a better way than copying the entire details into the footer, doing a row count on that, and modifying the output footer to include it. My details section is over 1000 lines long and uses 76 tables. I'd rather not have it take twice as long to run if there's a better way of going about doing this.
Any ideas?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Also in sqlplus you can compute totals, set headers, etc.
ASKER
I'm using SQLPLUS
So you want a sqlplus report?
Can you post a sample of what you are after?
Sample tables, sample data and expected results would be great.
Can you post a sample of what you are after?
Sample tables, sample data and expected results would be great.
ASKER
Everything I have is in one column each (header, details, footer)
details is set up like: Select LPAD(this,9,' ') || LPAD(that,9,' ') as details
Example:
Header1 03112011
John Doe XXXXXXXXX High
Jane Doe XXXXXXXXX High
Sam Sample XXXXXXXXX Medium
Footer1 3
(The 3 being a count of the details)
Could you use this logic to show me how this example would work inside the code layout you sent me?
details is set up like: Select LPAD(this,9,' ') || LPAD(that,9,' ') as details
Example:
Header1 03112011
John Doe XXXXXXXXX High
Jane Doe XXXXXXXXX High
Sam Sample XXXXXXXXX Medium
Footer1 3
(The 3 being a count of the details)
Could you use this logic to show me how this example would work inside the code layout you sent me?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>could you use this logic to show me how this example would work inside the code layout you sent me?
I was typing...
Will the compute above work for you?
I was typing...
Will the compute above work for you?
ASKER
I think so. So if I understand your example right, you're advising me to drop the data into a table (I'm assuming using an insert statement) and then do a query on the table with a union then a count (*) on the same table in the footer?
Ex:
Header - stuff
Details - Query on data now in table (possibly a temp table)
Footer - Count from table with data
Or am I missing the mark?
Ex:
Header - stuff
Details - Query on data now in table (possibly a temp table)
Footer - Count from table with data
Or am I missing the mark?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>on the table with a union then a count (*) on the same table in the footer?
forgot to comment on this.
NO. That was the very first example not knowing this was a SQLPLUS report. forget that first post...
forgot to comment on this.
NO. That was the very first example not knowing this was a SQLPLUS report. forget that first post...
ASKER
I get the concept now! I'll play with it, but it looks like it will do what I need it to do.
I've got to run right now, but will know by morning how this works.
I've got to run right now, but will know by morning how this works.