display total number of rows with function and criteria
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: display total number of rows with function and criteria

  1. #1
    Join Date
    Sep 2006
    Location
    London
    Posts
    58

    display total number of rows with function and criteria

    This query displays column c1, c2 and c3. The data of column c3 converts number into date from using function.

    SELECT a.c1, a.c2, integerdate(a.c3)
    FROM t141 a

    The below query display all the tables starts with 'T' charcter.

    select count(*) from user_tables where table_name like 'T%'

    but I need to display total number of rows from all the above tables start start with 'T' character using the below criteria.

    WHERE integerdate( c3 ) < add_months( sysdate, -36 )

    Can you please write the SQL statemetn to display total number of rows from each tables start with 'T' character.

    Thanks in advance.

  2. #2
    Join Date
    Oct 2006
    Posts
    11

    Through a view, perhaps?

    Assuming all T% tables have the same structure you should create a view like:

    SELECT * FROM Twhatever1
    UNION ALL
    SELECT * FROM Twhatever2
    UNION ALL
    ...

    SELECT * FROM TwhateverLAST

    This view can be done by hand or constructed dynamically from SELECT TABLE_NAME FROM WHATEVER_TABLES WHERE TABLE_NAME LIKE 'T%', or can be an inline view.

    Then you just do

    SELECT WHATEVER
    FROM YOUR_VIEW
    WHERE integerdate( c3 ) < add_months( sysdate, -36 )

    HTH

    Cheers.

    Carlos.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width