Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6970
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: 6970
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
This topic is locked: you cannot edit posts or make replies. ISREDIT Macro is not returning back t... nico_neoz CLIST & REXX 21 Sat Nov 18, 2017 7:40 pm
No new posts -502 Error - Cursor already open anand jeyapaul DB2 7 Sun Oct 29, 2017 4:22 am
No new posts IEW2456E error when link-editing a C ... Senthilraj JCL & VSAM 0 Fri Oct 13, 2017 3:12 pm
No new posts FTP Skip or ignore error and process ... AJAYREDDY All Other Mainframe Topics 3 Wed Sep 27, 2017 8:12 pm
No new posts Assembler Macro Copybook converted to... Ziquilix PL/I & Assembler 4 Tue Sep 26, 2017 3:07 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us