Re: Can I do this strictly in SQL*Plus?

I wrote a reply earlier, but it did not show up in the thread. Now here is it again:

I have to put some conditions here:

table1 and table2 are all very big tables, therefore not easy to do UNION or select-in-select;

one select count may take a while, therefore only two select may be allowed or say finish running in allowable time frame;

please use my format only, that is to output like:

Total: the-sum-number
Count1: r1
Count2: r2

And if I have to tell, I may have more tables (table3, table4 in the sequence that need to get the total). Can someone help me out of the trouble?

Thanks.

Maxim Demenko wrote:
> RK schrieb:
> > Can someone help me, please? -- I want to do the following steps, and
> > output strictly in SQL*Plus only:
> >
> > -1- select count(*) from table1;
> > -2- select count(*) from table2;
> >
> > Assume the results are r1 and r2, two numbers, and I want the output to
> > be like:
> >
> > Total: r1 + r2
> > Count1: r1
> > Count2: r2
> >
> > Can I do this strictly in SQL*Plus?
> >
> > Thanks.
> >
>
> There are many ways,
> for example
> SQL> select sum(cnt),nvl(tname,'total')
> 2 from (select count(*) cnt,'user_tables' tname from user_tables
> 3 union all select count(*) cnt,'user_indexes' tname from user_indexes)
> 4 group by rollup(tname)
> 5 /
>
> SUM(CNT) NVL(TNAME,'TOTAL')
> ---------- ------------------------------------
> 348 user_indexes
> 344 user_tables
> 692 total
>
> SQL>
>
> Best regards
>
> Maxim
Received on Tue Jun 07 2005 - 10:43:44 CDT