| Author |
Message |
rajesh_m
New User
Joined: 15 Apr 2008
Posts: 16
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 |
|
 |
References
|
 Posted: Thu May 08, 2008 6:20 pm Post subject: Re: Subquery aliasing returning-104 error |
|
|
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 709
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: 16
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
Warnings : 1
Active User
Joined: 13 Feb 2004
Posts: 113
|
|
|
|
| SELECT COUNT(NAME) FROM SYSIBM.SYSTABLES WHERE CREATOR='ABCD' |
|
| Back to top |
|
|
dbzTHEdinosauer
Senior Member
Joined: 20 Oct 2006
Posts: 903
Location: germany
|
|
|
|
| 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: 990
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: 16
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: 16
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
Senior Member
Joined: 20 Oct 2006
Posts: 903
Location: germany
|
|
|
|
| cast it or use a function to convert........... |
|
| Back to top |
|
|
|
|
