-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|