OraFAQ Forum: SQL & PL/SQL » multiple queries and 1 output window/result

Home » SQL & PL/SQL » SQL & PL/SQL » multiple queries and 1 output window/result (Oracle SQL Developer 19.1.0.094 / Oracle Database 12.2.0.1.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

multiple queries and 1 output window/result [message #684293]

Mon, 03 May 2021 09:50

abenitez77
Messages: 2
Registered: May 2021

Junior Member

I need to get the counts for multiple tables and I want to get my results into 1 sql worksheet so I can output to 1 excel output and not multiple sql worksheets. How can I get this query to do that?

Select count(*) from Table1;
Select count(*) from Table2;
Select count(*) from Table3;
Select count(*) from Table4;
Select count(*) from Table5;
Select count(*) from Table6;
Select count(*) from Table7;
Select count(*) from Table8;
Select count(*) from Table9;
Select count(*) from Table10;

Report message to a moderator

Re: multiple queries and 1 output window/result [message #684294 is a reply to message #684293]

Mon, 03 May 2021 10:24

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.

There are many solutions, here's (complex) one:

SQL> select column_value table_name,
  2          xmlcast(
  3            xmlquery(
  4              ('count(ora:view("SCOTT","' || column_value || '"))')
  5              returning content)
  6            as int) rows_in_table
  7  from table(sys.odcivarchar2list('EMP','DEPT','BONUS','SALGRADE'))
  8  /
TABLE_NAME      ROWS_IN_TABLE
--------------- -------------
EMP                        15
DEPT                        4
BONUS                       0
SALGRADE                    5

[Updated on: Mon, 03 May 2021 10:27]

Report message to a moderator

Re: multiple queries and 1 output window/result [message #684295 is a reply to message #684293]

Mon, 03 May 2021 10:24

John Watson
Messages: 8804
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

You could use UNION ALL to combine them in to one query,

Select count(*) from Table1
union all
Select count(*) from Table2
union all
Select count(*) from Table3;

Report message to a moderator

Re: multiple queries and 1 output window/result [message #684296 is a reply to message #684294]

Mon, 03 May 2021 10:29

abenitez77
Messages: 2
Registered: May 2021

Junior Member

Thank you both. Michael, could you explain what "SCOTT" value is for in this query you provided?

('count(ora:view("SCOTT","' || table_name || '"))')

Thanks!
Alex

Report message to a moderator

Re: multiple queries and 1 output window/result [message #684297 is a reply to message #684294]

Mon, 03 May 2021 11:16

Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA

Senior Member

Michel,

Oracle XQuery function ora:view is desupported. Use XQuery functions fn:collection instead:

select  column_value table_name,
        xmlcast(
                xmlquery(
                         ('count(fn:collection("oradb:/SCOTT/' || column_value || '"))')
                         returning content
                        )
                as int
               ) rows_in_table
  from table(sys.odcivarchar2list('EMP','DEPT','BONUS','SALGRADE'))
/

TABLE_NAME                     ROWS_IN_TABLE
------------------------------ -------------
EMP                                       14
DEPT                                       4
BONUS                                      0
SALGRADE                                   5

SQL>

SY.

Report message to a moderator

Re: multiple queries and 1 output window/result [message #684298 is a reply to message #684297]

Mon, 03 May 2021 12:07

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks for the information, Solomon.

Report message to a moderator

Re: multiple queries and 1 output window/result [message #684299 is a reply to message #684296]

Mon, 03 May 2021 12:08

Michel Cadot
Messages: 68417
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

abenitez77 wrote on Mon, 03 May 2021 17:29

Thank you both. Michael, could you explain what "SCOTT" value is for in this query you provided?

('count(ora:view("SCOTT","' || table_name || '"))')

Thanks!
Alex

SCOTT is the owner of the tables here.

Use Solomon's query instead.

Or John's one if the table list is fixed.

[Updated on: Mon, 03 May 2021 12:09]

Report message to a moderator

Previous Topic:	how to get the rows of different values of single column
Next Topic:	Optimization of SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 23 23:03:56 CDT 2023