Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Subquery aliasing returning-104 error

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Thu May 08, 2008 6:20 pm    Post subject: Subquery aliasing returning-104 error
Reply with quote

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
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu May 08, 2008 6:38 pm    Post subject:
Reply with quote

Maybe you should just explain what you are trying to do.
Back to top
View user's profile Send private message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Thu May 08, 2008 6:56 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu May 08, 2008 7:02 pm    Post subject:
Reply with quote

SELECT COUNT(NAME) FROM SYSIBM.SYSTABLES WHERE CREATOR='ABCD'
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Thu May 08, 2008 7:32 pm    Post subject:
Reply with quote

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
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Thu May 08, 2008 8:55 pm    Post subject:
Reply with quote

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
View user's profile Send private message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Thu May 08, 2008 10:27 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Ajesh

New User


Joined: 03 Apr 2008
Posts: 6
Location: Chennai

PostPosted: Wed May 14, 2008 5:02 pm    Post subject:
Reply with quote

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
View user's profile Send private message
rajesh_m

New User


Joined: 15 Apr 2008
Posts: 39
Location: India

PostPosted: Fri May 16, 2008 6:16 pm    Post subject:
Reply with quote

[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
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri May 16, 2008 8:23 pm    Post subject:
Reply with quote

cast it or use a function to convert...........
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 Program abending without giving a... rahulgarg14 DB2 7 Mon May 29, 2017 8:10 pm
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts IDC2902I Error tzeche ABENDS & Debugging 2 Tue Mar 28, 2017 11:39 am
No new posts Need help with below DB2 error ashek15 DB2 3 Sat Mar 18, 2017 5:56 am
No new posts FINDREP Syntax Error seahawk789 DFSORT/ICETOOL 4 Thu Mar 09, 2017 10:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us