Getting -301 in Dynamic SQL

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

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.

expat · Posted: Mon Oct 25, 2010 5:27 pm

What did the manual say when you looked up -301.
Where are you stuck after reading the explanation.

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

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?

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

I am not able to find out the root cause, Could any suggest any thing?

enrico-sorichetti · Posted: Tue Oct 26, 2010 11:44 am

post the exact query You used!

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

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.

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

I am getting this error in OPEN Cursor Statement.

enrico-sorichetti · Posted: Tue Oct 26, 2010 12:32 pm

please explain what You mean by dynamic SQL ?

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

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.

enrico-sorichetti · Posted: Tue Oct 26, 2010 2:07 pm

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 )

GuyC · Posted: Tue Oct 26, 2010 3:06 pm

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.

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

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 .

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

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.

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

Is there any way that we can see the SQl that got generated(with values assigned to each cast statement)?

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

Thanks all, The issue got solved.

dick scherrer · Posted: Wed Oct 27, 2010 7:55 pm

Hello,

mahi · New User Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad

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.