Solved

How do I best add a row count in a footer with a union

Posted on 2011-03-10
11
397 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:chevere
  • 7
  • 4
11 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35101251
What tool?

I straight sql you can use a union.

Select col1, col2 from
(
Select 1
Sort_order, 'header' col1, null col2 from dual
Union all
Select 2, 'detail', 'some column' from table
Union all
Select 3, 'total', count(1) from table
) Order by sort_order
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35101261
Also in sqlplus you can compute totals, set headers, etc.
0
 

Author Comment

by:chevere
ID: 35101277
I'm using SQLPLUS
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35101292
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.
0
 

Author Comment

by:chevere
ID: 35101321
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?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 35101325
>>Can you post a sample of what you are after?

I should say, a simple sample.

For example you can COMPUTE the count.

See the sample below.

This is the output:
C
-
a
b
c
-
3

drop table tab1 purge;
create table tab1(col1 char(1));
insert into tab1 values('a');
insert into tab1 values('b');
insert into tab1 values('c');
commit;


clear breaks
clear computes
clear columns

set pages 100
compute count of col1 on report
break on report

select * from tab1;

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35101329
>>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?

0
 

Author Comment

by:chevere
ID: 35101378
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?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
ID: 35101438
>>you're advising me to drop the data into a table

NO.  Since I don't have your 1000 line select statement over 76 tables and 35 columns, to show the 'concept' I need to create my own test case.

I dummied up a table to show the power of the COMPUTE statements.  You need to apply that concept to your situation.

Also below is a possible alternative.  Same basic results, different method.
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));
insert into tab1 values('a','1');
insert into tab1 values('b','2');
insert into tab1 values('c','3');
commit;

set verify off
clear columns
column myTotal new_value some_var_name
column myTotal noprint

--turn off column headings
set pages 0

select 'Some Header' from dual;

Select col1, col2, count(*) over() myTotal from tab1;

Select 'total', &some_var_name from dual;

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35101456
>>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...
0
 

Author Comment

by:chevere
ID: 35101496
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question