View previous topic :: View next topic
|
Author |
Message |
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. |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8796 Location: Welsh Wales
|
|
|
|
What did the manual say when you looked up -301.
Where are you stuck after reading the explanation. |
|
Back to top |
|
|
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? |
|
Back to top |
|
|
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? |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
post the exact query You used! |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
mahi
New User
Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad
|
|
|
|
I am getting this error in OPEN Cursor Statement. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
|
|
please explain what You mean by dynamic SQL ? |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007 Posts: 10886 Location: italy
|
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
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 |
|
|
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 . |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
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)? |
|
Back to top |
|
|
mahi
New User
Joined: 04 Apr 2006 Posts: 86 Location: Hyderabad
|
|
|
|
Thanks all, The issue got solved. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19243 Location: Inside the Matrix
|
|
|
|
Hello,
Quote: |
The issue got solved. |
What caused/fixed the problem? |
|
Back to top |
|
|
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. |
|
Back to top |
|
|
|