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
 
Check if the Table is empty

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

New User


Joined: 11 May 2007
Posts: 20
Location: Canada

PostPosted: Thu May 28, 2009 9:18 pm    Post subject: Check if the Table is empty
Reply with quote

Hi,

I need to check whether the Table contains the data or not. For that I have two options. Please let me know which is better.


EXEC SQL
SELECT COUNT(*)

INTO : WS-ROW-COUNT
FROM TABLE1
END-EXEC.

OR


EXEC SQL
SELECT COUNT(*)

INTO : WS-ROW-COUNT
FROM TABLE1
FETCH FIRST 1 ROW ONLY
END-EXEC.
Back to top
View user's profile Send private message

enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10327
Location: italy

PostPosted: Thu May 28, 2009 9:44 pm    Post subject: Reply to: Check if the Table is empty
Reply with quote

just to give You some thinking material...

how many rows will a "SELECT COUNT(*) FROM table_name" return ?
Back to top
View user's profile Send private message
Sowmya Ramachandra

New User


Joined: 11 May 2007
Posts: 20
Location: Canada

PostPosted: Thu May 28, 2009 9:50 pm    Post subject:
Reply with quote

It will return total number of rows present in the Table ???
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 28, 2009 10:02 pm    Post subject:
Reply with quote

Quote:
how many rows
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Thu May 28, 2009 10:43 pm    Post subject: Reply to: Check if the Table is empty
Reply with quote

Sowmya Ramachandra,If you are required to only find the table is empty or not ,you may try this:
Code:

EXEC-SQL
SELECT 1
INTO :X
FROM TAB1
END-EXEC

X is defined as s9(4) comp value zeroes
So, if there is a row in the table (i.e. the table is not empty), 1 gets moved to variable X.
Did not have resources to run the query and validate , i hope it should be ok icon_smile.gif
WTH.
Back to top
View user's profile Send private message
Sowmya Ramachandra

New User


Joined: 11 May 2007
Posts: 20
Location: Canada

PostPosted: Thu May 28, 2009 10:47 pm    Post subject:
Reply with quote

u mean how rows will be in out put ??

It is 1 row with the count
Back to top
View user's profile Send private message
Sowmya Ramachandra

New User


Joined: 11 May 2007
Posts: 20
Location: Canada

PostPosted: Thu May 28, 2009 10:50 pm    Post subject:
Reply with quote

Wat is difference b/w Count (*) and Count(1) ?? Out put seems to be same...
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Thu May 28, 2009 11:17 pm    Post subject: Reply to: Check if the Table is empty
Reply with quote

Sowmya Ramachandra,
Quote:
u mean how rows will be in out put ??
It is 1 row with the count

No, its not the count ,it doesn't count the number of rows , it just produces the result set as 1 if the criteria matches.
Quote:
Wat is difference b/w Count (*) and Count(1) ?? Out put seems to be same...
Again i have not used count, and secondly are you sure your Count (*) and Count(1) produced the same results, did you manually check how many rows are there in the Table.

WTH
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Fri May 29, 2009 8:56 am    Post subject:
Reply with quote

Succor,

I think there's a misunderstanding here. Sowmya Ramachandra was trying to answer the below question raised by enrico,
enrico sorichetti wrote:
how many rows will a "SELECT COUNT(*) FROM table_name"
return ?
Sowmya ,
Sowmya Ramachandra wrote:
u mean how rows will be in out put ??

It is 1 row with the count
Then why do you need a 'FETCH FIRST 1 ROW ONLY ' in your sql?
Back to top
View user's profile Send private message
Succor

New User


Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)

PostPosted: Fri May 29, 2009 5:33 pm    Post subject: Reply to: Check if the Table is empty
Reply with quote

Arun, I think you are correct , I should have bothered to relate the doubts to their explanations before replying to the post.
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm

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