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
 

 

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: 10211
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: 6966
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: 2181
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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts What is the command to check MODE of ... rohanthengal CLIST & REXX 7 Fri Nov 18, 2016 1:48 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm


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