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
 

 

Getting -301 in Dynamic SQL

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

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Mon Oct 25, 2010 5:10 pm    Post subject: Getting -301 in Dynamic SQL
Reply with quote

Hi,
I am getting -301 while trying to execute my Dynamic SQl.

My requirement:

SELECT COL1, COL2
FROM
TABLE1 AS A
LEFT OUTER JOIN TABLE2 AS B
ON A.COL1 = B.COL2
AND B.COL2 = "dynamic Value" (using CAST here)

WHERE
A.COL3 = "dynamic Value" (using CAST here)
B.COL3 = "dynamic Value" (using CAST here).

Could any one suggest , how to implement ths requirement using Dynamic SQl.
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Mon Oct 25, 2010 5:27 pm    Post subject:
Reply with quote

What did the manual say when you looked up -301.
Where are you stuck after reading the explanation.
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Mon Oct 25, 2010 5:49 pm    Post subject:
Reply with quote

The explannation for -301 is:
"-301 THE VALUE OF INPUT HOST VARIABLE OR PARAMETER NUMBER position-number CANNOT BE USED AS SPECIFIED BECAUSE OF ITS DATA TYPE".

One Doubt: Can we use the parameter Markers in the FROM clause as well as in WHERE clause?
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Tue Oct 26, 2010 10:52 am    Post subject:
Reply with quote

I am not able to find out the root cause, Could any suggest any thing?
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Oct 26, 2010 11:44 am    Post subject: Reply to: Getting -301 in Dynamic SQL
Reply with quote

post the exact query You used!
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Tue Oct 26, 2010 12:14 pm    Post subject:
Reply with quote

Hi,
Please find the Generated Query:

SELECT DISTINCT A.DID,A.RID_NUM,A.AIDN
M,A.RTYPE,A.RSTART_TS,A.REND_TS
B.A_FFLMT
FROM
D_RARR_RN AS A LEFT OUTER JOIN D_ARA AS B
ON B.AID_NUM = A.AID_NUM
AND B.DID =A.DID LEFT OUTER JOIN D_RARR_PRR AS C
ON C.RD_NUM = A.RID_NUM AND C.AID_NUM =A.AID_NUM
AND C.DID = A.DID
AND C.PRR_CDE = CAST(? AS CHAR (0010))
AND C.PRR_TYPE = CAST(? AS CHAR (0010))
WHERE A.D_ID= CAST(? AS INTEGER)
AND PRR_CDE IS NOT NULL
ORDER BY C.PRR_CDE DESC .

For this error, from where can I debug to find the cause.
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Tue Oct 26, 2010 12:29 pm    Post subject:
Reply with quote

I am getting this error in OPEN Cursor Statement.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Oct 26, 2010 12:32 pm    Post subject: Reply to: Getting -301 in Dynamic SQL
Reply with quote

please explain what You mean by dynamic SQL ?
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Tue Oct 26, 2010 12:39 pm    Post subject:
Reply with quote

Hi,
Here our requirement is: the Columns in the where clause is not Fixed, Depending on some conditions the column names may vary. In some cases we build WHERE cluase with only one predicate, in some other cases the predicate count is more than one.
So we are using Dynamic SQl to build the SQl at run time rather than the Fixed Static SQL.

I have pasted the SQL that I got from the XPEDITOR debugging.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Tue Oct 26, 2010 2:07 pm    Post subject: Reply to: Getting -301 in Dynamic SQL
Reply with quote

here is a good place to start with
http://www.redbooks.ibm.com/abstracts/sg246418.html
remember also to download the additional material
( link on the left of the page )
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Oct 26, 2010 3:06 pm    Post subject:
Reply with quote

I'm not sure you can use cast() with parameter markers.

else one of these is wrong :
prr_cde is not char(10), prr_type is not char(10), d_id is not integer
or
the parameters used are not x(10) or s9(9) comp.
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Tue Oct 26, 2010 5:38 pm    Post subject:
Reply with quote

Hi,
If I use the C.PRR_CDE and C.PRR_TYPE in WHERE predicate instead of FROM Clause, it is working fine. But I am not sure why I am getting -301 if I used the above two fields in FROM clause.

Is this because the values are not assigned properly?

I fired the SQL in SPUFI with the above two cases(1. prfr_cde and prfr_type in FROM & 2. prfr_cde and prfr_type in WHERE) and the results are same.


SQL:
SELECT DISTINCT A.DID,A.RID_NUM,A.AIDN
M,A.RTYPE,A.RSTART_TS,A.REND_TS
B.A_FFLMT
FROM
D_RARR_RN AS A LEFT OUTER JOIN D_ARA AS B
ON B.AID_NUM = A.AID_NUM
AND B.DID =A.DID LEFT OUTER JOIN D_RARR_PRR AS C
ON C.RD_NUM = A.RID_NUM AND C.AID_NUM =A.AID_NUM
AND C.DID = A.DID
AND C.PRR_CDE = CAST(? AS CHAR (0010))
AND C.PRR_TYPE = CAST(? AS CHAR (0010))

WHERE A.D_ID= CAST(? AS INTEGER)
AND PRR_CDE IS NOT NULL
ORDER BY C.PRR_CDE DESC .
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Wed Oct 27, 2010 11:45 am    Post subject:
Reply with quote

Hello all...

I am still facing the issue -301. Could any one tell me how the values will be assigned to Coulmns? I think in my case the values are not getting assigned properly. Could any one suggest me on this.

If you need any more info pls post it to me.
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Wed Oct 27, 2010 11:53 am    Post subject:
Reply with quote

Is there any way that we can see the SQl that got generated(with values assigned to each cast statement)?
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Wed Oct 27, 2010 5:54 pm    Post subject:
Reply with quote

Thanks all, The issue got solved.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Oct 27, 2010 7:55 pm    Post subject:
Reply with quote

Hello,

Quote:
The issue got solved.
What caused/fixed the problem?
Back to top
View user's profile Send private message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Pune

PostPosted: Thu Oct 28, 2010 12:34 pm    Post subject:
Reply with quote

Hi,
If use the CASTing in JOIN clause I was facing the error -301, I tried in different ways and i was unable to solve.

So i have hard coded vaues into FROM clause instead of Casting. Thats how I fixed it tempararly.

Still I am not sure whether to use the CAST in FROM or not. If any one one knows please post it to me.
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 Creating a dynamic sort card in JCL u... ChitraChhabra DFSORT/ICETOOL 4 Wed Aug 03, 2016 6:15 pm
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts Need to create file with Dynamic Name... kapil27 JCL & VSAM 4 Wed May 25, 2016 9:45 am
No new posts DB2 Stored Procedure - Dynamic SQL - ... GuyC DB2 2 Wed Feb 17, 2016 5:10 pm
No new posts Need help with Oracle Dynamic SQL: Me... Anoop Chandran1984 COBOL Programming 1 Sat Feb 13, 2016 7:27 am


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