Oracle: Count on only one table within select

30 pts.
Tags:
Oracle
SQL
I have the following SQL and want to add to the query a count of records for each of the tables selected out of the user_tables, basically count(*). I've tried several things, but am getting only a count of 1. Any help would be appreciated.
select substr(a.table_name,1,15) ,  substr(b.t$desc,1,40)
  2  from user_tables a, tttadv130000 b
  3  where (substr(a.table_name,1,6) = 'TTTADV' or substr(a.table_name,1,6) = 'T
TTAAD')
  4  and substr(a.table_name,2,2) = upper(b.t$cpac)
  5  and substr(a.table_name,4,6) = upper(substr(b.t$rkey,1,6))
  6  and b.t$kdes = 3
  7  and b.t$clan = 2
  8* order by  a.table_name


Software/Hardware used:
oracle 11
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    I'm not sure to understand.

    Do you want a count from user_tables that is unrelated to the conditions you are using to join it to the other table?

    If so, a scalar subquery could help.

    Please explain what you need with more detail, and post the other query you tried (the one that includes the COUNT).

    Also, it would help seeing some sample data and the results you would get from that sample data.
    84,720 pointsBadges:
    report
  • nemsmith
    HI,
    I want to take the output of table name selected from the user table which will be multiple tables and take each of those tables and found the records count. For example, I would get the tttadv360100 table and then select the record count of that table. The same for the next table selected from the user_table. I go it to work with the rownum count, but I really would like to do a record count of each table I have selected from the user_tables.


    thanks,

    Linda
    30 pointsBadges:
    report
  • HrvojeZunko
    If I understand you well.
    Try something like this:

    create or replace function countrows(p_tab varchar2) return number
    as
    l_retval number;
    begin
    execute immediate 'select count(1) from '||p_tab into l_retval;
    return l_retval;
    end;

    select a.table_name,countrows(a.table_name) numrows
    from user_tables a where (substr(a.table_name,1,6) = 'TTTADV' or substr(a.table_name,1,6) = 'TTTAAD')
    220 pointsBadges:
    report
  • carlosdl
    I think HrvojeZunko nailed it, if we understood your requirement correctly.

    If that doesn't work, then explain why the results you get are incorrect.
    84,720 pointsBadges:
    report
  • carlosdl
    Comments by nemsmith (removed from the answer Wiki):

    "Works perfect!!
    Thank you for all your help.   Truly appreciated!!"
    84,720 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: