Another Oracle Question

[Ardesco]

Is there a way to get the number of result rows returned and use that number as a footer?

e.g.

SELECT 'HEADER - HEADER ROW HERE' FROM DUAL
UNION ALL
SELECT A,B,C
FROM MY_TABLE
UNION ALL
SELECT 'FOOTER - TOTAL NUMBER OF ROWS RETURNED =' <INSERT ROW COUNT HERE> FROM DUAL

I've tried using rownum, but it doesn't give me total number of rows output, only total number of rows in the current select statment (i.e. 1).

 

[SallyAnne]

SELECT 'HEADER - HEADER ROW HERE' FROM DUAL
UNION ALL
SELECT A,B,C
FROM MY_TABLE
UNION ALL
SELECT 'FOOTER - TOTAL NUMBER OF ROWS RETURNED = '||count(*) from MY_TABLE


This should work, but I'm not at work today, so I haven't been able to try it.

 

[Ardesco]

Cheers SA, works a treat.


Easy when you know how hey

 

[mashetti]

Cheers SA, works a treat.


Easy when you know how hey

that will work but you are hitting the same records twice which is not very efficient. Depends on how big the result set is but a more efficient way is to use pl/sql and have it in a cursor and loop through...