|
View previous topic :: View next topic
|
| Author |
Message |
vd123
New User
Joined: 30 Jul 2009
Posts: 7
Location: Pune
|
|
|
|
Hi
I have to get total records for a dynamic sql. I tried all combination with count(*) but the program fails while preparing the dynamic sql statement with -104.(or while fetching the data it goes with this error)
e.g
Select count(*) into ?
from ...
where...
(? is a parameter marker which I tried passing as :W-count) but it failed.
I tried giving below combination also but failed.
Select count(*) into :?
from ...
where...
Can anyone please suggest me the syntax to get the count in dynamic sql
Alias is not allowed to use here so I need to get the data in variable to send ahead.
Thanks |
|
| Back to top |
|
 |
Ronald Burr
Active User
Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.
|
|
|
|
From the DB2 Application Programming and SQL Guide:
"Dynamic SELECT statements cannot use INTO. Therefore, you must use a cursor to put the results into host variables." |
|
| Back to top |
|
|
vd123
New User
Joined: 30 Jul 2009
Posts: 7
Location: Pune
|
|
|
|
could you please give an example how to do it?
As when I am fetching the cursor I am getting -313 error which says that the there is mismatch in host variables and the parameter markers(?) in the prepared sql statement |
|
| Back to top |
|
|
vd123
New User
Joined: 30 Jul 2009
Posts: 7
Location: Pune
|
|
|
|
Also I am trying to get the count from SQLERRD(3) will this be correct
as my prepared sql statement looks like this
SELECT COUNT(*)
From Tab1, tabl2
Where tab1.field 1= tab1.field 1 and
tab1.field 2= tab1.field 1 and
tab1.field 3 = ? and
tab2.field 2 = ?
Fetch::
Exec SQL
fetch cursor1
end exec |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
vd123,
since we are approaching a few days of holiday
(which means you have some extra time to spend learning things
that you should have learned before)
here are a few links
to provide you some insight into the normally acquired skills of a software engineer:
DB2 on-campus videos
IBM Developerworks suggest that you become a member, it is free, and there is a lot of information available at this website.
IBM Redbooks search for dynamic sql, there are several Redbooks that discuss and provide examples of dynamic sql. |
|
| Back to top |
|
|
ajeshrn
New User
Joined: 25 Mar 2009
Posts: 78
Location: India
|
|
|
|
Hi vd123,
Dynamic SQL when used in COBOL program, to get the count I would suggest the below code.
| Code: |
01 VAR-1 X(20)
"SELECT COUNT(*) INTO"
01 COUNT-1 S9(4)
01 VAR-1 X(20)
"from ... "
|
In run time , COUNT-1 will be populated with the value. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Did you actually test this before posting. . .?
Is the manual wrong?
| Quote: |
From the DB2 Application Programming and SQL Guide:
"Dynamic SELECT statements cannot use INTO. Therefore, you must use a cursor to put the results into host variables." |
|
|
| Back to top |
|
|
|
|
