|
View previous topic :: View next topic
|
| Author |
Message |
diwa_thilak
Active User
.jpg)
Joined: 13 Jul 2006
Posts: 205
Location: At my desk
|
|
|
|
Hi Friends,
I have DB2 query like this.
| Code: |
SELECT DISTINCT.LOC_I
FROM LOC.STGY_LOC
WHERE LOC_IN (:WS-TOT-STRING)
FOR FETCH ONLY |
The total string is a working storage variable which will accept the locations during run time.
I am getting an error message like WS-TOT-STRING is not declared as a part of specified table(s).
When i execute the same query using a prepared statement in COBOL it is working fine ?
Could you please let me know the reason ? |
|
| Back to top |
|
 |
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hey Diwakar,
Would you please paste ur spool message? |
|
| Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006
Posts: 205
Location: At my desk
|
|
|
|
Guptae,
| Quote: |
465 IGYDS0209-W DSNH206I DSNHANAL STATEMENT REFERENCES COLUMN "LOC.STGY_LOC""WS-TOT-STRING", WHICH IS NOT DECLARED IN THE SPECIFIED TABLE(S)
757 IGYOP3091-W Code from "procedure name 9980-PROGRAM-ABEND" to "EXIT (l(line 775.01)" can never be executed and was therefore
discarded.
|
When i bind the table
| Quote: |
DSNT201I -DTTA BIND FOR PLAN A027180A NOT SUCCESSFUL
DSN
END
READY
END |
i get the above error message.
Do let me know how to proceed !!! |
|
| Back to top |
|
|
acevedo
Active User
Joined: 11 May 2005
Posts: 344
Location: Spain
|
|
|
|
| Code: |
| WHERE LOC_IN (:WS-TOT-STRING) |
what is the search condition that correlates LOC_IN and WS-TOT-STRING? I mean, should them be equal (=), not equal (¬=), IN, >, <....  |
|
| Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006
Posts: 205
Location: At my desk
|
|
|
|
Acevedo,
It is
| Quote: |
| WHERE LOC_I IN (:WS-TOT-STRING) |
I missed the In in the Code. |
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
| Good Catch Acevedo |
|
| Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006
Posts: 205
Location: At my desk
|
|
|
|
But still it is not working Boss. I mase a mistake while quoting the code in Forum.
Why the above code works perfectly while using it in Prepared statement ? |
|
| Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006
Posts: 205
Location: At my desk
|
|
|
|
Just one more information
1. WS-TOT-STRING is declared as X(500) , But the location value is defined to be Numeric in the table
2. When i tried executing the above code by statically setting a value like
WHERE LOC_I IN (1) It works perfectly. If i declare the same using a variable as TOT-STRING = value 1 then it throws out an error stating use = rather than IN.
Is the mapping of variables is the problem ?
If so , how can we approach this problem.
But the same query when prepared using the Prepare statement (dynamic) it works fine. |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| diwa_thilak wrote: |
Hi Friends,
I have DB2 query like this.
| Code: |
SELECT DISTINCT.LOC_I
FROM LOC.STGY_LOC
WHERE LOC_IN (:WS-TOT-STRING)
FOR FETCH ONLY |
The total string is a working storage variable which will accept the locations during run time.
I am getting an error message like WS-TOT-STRING is not declared as a part of specified table(s).
When i execute the same query using a prepared statement in COBOL it is working fine ?
Could you please let me know the reason ? |
Where is LOC_I suppose to go after you select it? You need an into clause. I am not sure about that period between DISTINCT and LOC_I. |
|
| Back to top |
|
|
diwa_thilak
Active User
Joined: 13 Jul 2006
Posts: 205
Location: At my desk
|
|
|
|
Craq,
You are correct. The peroid between the DISTINCT and LOC_I does not exists, Also the table name is LOC_STGY_LOC and not LOC.STGY_LOC.
If INTO CLAUSE is the problem, how come this query is getting executed using Prepared statement in COBOL ? |
|
| Back to top |
|
|
dr_te_z
New User
Joined: 08 Jun 2007
Posts: 71
Location: Zoetermeer, the Netherlands
|
|
|
|
| The preprocessor is more primitive than we hope, I'm afraid. My guess is that the SQL is coded before the definition of the host variable. Try to move that field to the top of the working-storage. |
|
| Back to top |
|
|
martin9
Active User
Joined: 01 Mar 2006
Posts: 290
Location: Basel, Switzerland
|
|
|
|
Hi diwa_thilak,
can you write the statement that is working?
if prepared or not, if it is not coded correctly, it will not work,
this has nothing to do with the preprocessor.
means is not a preprocessor problem by itself.
anyway the variables you use, must match the same picture (format)
as the one the table definition for that column.
Martin9 |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
your IN object (the working storage field) needs to be redefined or just data elemented so that db2 knows what kind of item length it is working with.
01 reference-name-in-where-clause.
05 element-1 pic x(4).
05 element-2 pic x(4).
05 element-3 pic x(4).
05 element-4 pic x(4).
05 element-5 pic x(4). |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| diwa_thilak wrote: |
| If INTO CLAUSE is the problem, how come this query is getting executed using Prepared statement in COBOL ? |
Because the prepare for a select creates a cursor which doesn't have an INTO clause. |
|
| Back to top |
|
|
|
|
