IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Getting -301 in Dynamic SQL


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
mahi

New User


Joined: 04 Apr 2006
Posts: 86
Location: Hyderabad

PostPosted: Mon Oct 25, 2010 5:10 pm
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: 8796
Location: Welsh Wales

PostPosted: Mon Oct 25, 2010 5:27 pm
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: Hyderabad

PostPosted: Mon Oct 25, 2010 5:49 pm
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: Hyderabad

PostPosted: Tue Oct 26, 2010 10:52 am
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

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Tue Oct 26, 2010 11:44 am
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: Hyderabad

PostPosted: Tue Oct 26, 2010 12:14 pm
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: Hyderabad

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

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

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Tue Oct 26, 2010 12:32 pm
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: Hyderabad

PostPosted: Tue Oct 26, 2010 12:39 pm
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

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Tue Oct 26, 2010 2:07 pm
Reply with quote

here is a good place to start with
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: 1281
Location: Belgium

PostPosted: Tue Oct 26, 2010 3:06 pm
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: Hyderabad

PostPosted: Tue Oct 26, 2010 5:38 pm
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: Hyderabad

PostPosted: Wed Oct 27, 2010 11:45 am
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: Hyderabad

PostPosted: Wed Oct 27, 2010 11:53 am
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: Hyderabad

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

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

Moderator Emeritus


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

PostPosted: Wed Oct 27, 2010 7:55 pm
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: Hyderabad

PostPosted: Thu Oct 28, 2010 12:34 pm
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Using Dynamic file handler in the Fil... COBOL Programming 2
No new posts JCL Dynamic System Symbols JCL & VSAM 3
No new posts Synctool-dynamic split job for varyin... JCL & VSAM 7
No new posts Dynamic file allocation using JCL JCL & VSAM 8
No new posts Dynamic Sortin DD cards SYNCSORT 8
Search our Forums:

Back to Top