View previous topic :: View next topic
|
Author |
Message |
rajesh_m
New User
Joined: 15 Apr 2008 Posts: 39 Location: India
|
|
|
|
SELECT COUNT(*) (SELECT NAME FROM SYSIBM.SYSTABLES
WHERE CREATOR='ABCD') AS T1 FROM T1.NAME;
What is the problem in this query why it is not working. It is returning-104 error. Any help appriciatable. |
|
Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
Maybe you should just explain what you are trying to do. |
|
Back to top |
|
|
rajesh_m
New User
Joined: 15 Apr 2008 Posts: 39 Location: India
|
|
|
|
SELECT NAME FROM SYSIBM.SYSTABLES WHERE CREATOR='ABCD'
This query will return the list of tables with in the creator 'ABCD' i need the count of records for those tables. |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
SELECT COUNT(NAME) FROM SYSIBM.SYSTABLES WHERE CREATOR='ABCD' |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
i need the count of records for those tables
|
records is not a db2 thing.
do you mean that you want a count of the rows for each table?
or a count of the number of tables that satisfy the predicate: where creator = ...? |
|
Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005 Posts: 1208 Location: Bangalore,India
|
|
|
|
Hello Rajesh,
If you want to count the number of rows ine ach table where creator ='ABCD' then u can use following query
Code: |
SELECT CARDF
FROM SYSIBM.SYSTABLES
WHERE CREATOR LIKE 'ABCD%' WITH UR; |
Quote: |
CRADF definesTotal number of rows in the table or total number of LOBs in an auxiliary table. The value is -1 if statistics have not been gathered or the row describes a view, alias, or created temporary table. This is an updatable column. |
OR If u want total number of table for which CREATOR is 'ABCD' then u can use query given by Ashmir
Code: |
SELECT COUNT(NAME) FROM SYSIBM.SYSTABLES WHERE CREATOR='ABCD' with ur; |
|
|
Back to top |
|
|
rajesh_m
New User
Joined: 15 Apr 2008 Posts: 39 Location: India
|
|
|
|
Quote: |
do you mean that you want a count of the rows for each table?
or a count of the number of tables that satisfy the predicate: where creator = ..?
I want the number of records existing in the each table which is returning by the subquery |
|
|
Back to top |
|
|
Ajesh
New User
Joined: 03 Apr 2008 Posts: 6 Location: Chennai
|
|
|
|
Rajesh,
Try running this query.Hope this will solve the problem
SELECT COUNT(*) FROM (SELECT NAME FROM SYSIBM.SYSTABLES
WHERE CREATOR='ABCD') A; |
|
Back to top |
|
|
rajesh_m
New User
Joined: 15 Apr 2008 Posts: 39 Location: India
|
|
|
|
[quote="guptae"]Hello Rajesh,
If you want to count the number of rows ine ach table where creator ='ABCD' then u can use following query
Code: |
SELECT CARDF
FROM SYSIBM.SYSTABLES
WHERE CREATOR LIKE 'ABCD%' WITH UR; |
It is returning the results in floating point how come number of records in floating value. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
cast it or use a function to convert........... |
|
Back to top |
|
|
|