|
View previous topic :: View next topic
|
| Author |
Message |
Sowmya Ramachandra
New User
Joined: 11 May 2007
Posts: 20
Location: Canada
|
|
|
|
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 |
|
 |
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
just to give You some thinking material...
how many rows will a "SELECT COUNT(*) FROM table_name" return ? |
|
| Back to top |
|
|
Sowmya Ramachandra
New User
Joined: 11 May 2007
Posts: 20
Location: Canada
|
|
|
|
| It will return total number of rows present in the Table ??? |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
| Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
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 
WTH. |
|
| Back to top |
|
|
Sowmya Ramachandra
New User
Joined: 11 May 2007
Posts: 20
Location: Canada
|
|
|
|
u mean how rows will be in out put ??
It is 1 row with the count |
|
| Back to top |
|
|
Sowmya Ramachandra
New User
Joined: 11 May 2007
Posts: 20
Location: Canada
|
|
|
|
| Wat is difference b/w Count (*) and Count(1) ?? Out put seems to be same... |
|
| Back to top |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
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 |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
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 |
|
|
Succor
New User
Joined: 20 Feb 2009
Posts: 96
Location: Bangalore :)
|
|
|
|
| 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 |
|
|
|
|
