Total Pageviews

2014/06/10

Parameter(s) [SPEC_FUND] are unresovlable in query string

Problem
Here has part of my SQL statement.
I faced a weird situation, this SQL statement can be executed successfully in SQL Developer, but fail to execute in Java program.

SELECT /*+USE_HASH (Y,LY,CALENDAR,LWD)*/ Y.PDATE ,
       NVL(Y.INCM_TAX,0) + NVL(LY.INCM_TAX,0) AS INCM_TAX ,
       NVL(Y.INCM_OTH,0) + NVL(LY.INCM_OTH,0) AS INCM_OTH ,
       Y.INCM_LOAN ,
       NVL(Y.INCM_NET_SALE,0) + NVL(LY.INCM_NET_SALE,0) AS INCM_NET_SALE ,
       NVL(Y.PAY_OTH,0) + NVL(LY.PAY_OTH,0) AS PAY_OTH ,
       Y.PAY_T_BILL ,
       NVL(Y.SPEC_FUND,0) - NVL(LWD.SPEC_FUND,0) AS SPEC_FUND,                        --:SPEC_FUND – ALLOT_TAX(自AVE107FB)
       NVL(Y.CHK_FUND,0) - NVL(LWD.CHK_FUND,0) AS CHK_FUND                     FROM .....

As I executed this SQL statement in Java program, it throws exception : Parameter(s) [SPEC_FUND] are unresovlable in query string

Root Cause
This problem result from ":SPEC_FUND" in remarks. 
SELECT /*+USE_HASH (Y,LY,CALENDAR,LWD)*/ Y.PDATE ,
       NVL(Y.INCM_TAX,0) + NVL(LY.INCM_TAX,0) AS INCM_TAX ,
       NVL(Y.INCM_OTH,0) + NVL(LY.INCM_OTH,0) AS INCM_OTH ,
       Y.INCM_LOAN ,
       NVL(Y.INCM_NET_SALE,0) + NVL(LY.INCM_NET_SALE,0) AS INCM_NET_SALE ,
       NVL(Y.PAY_OTH,0) + NVL(LY.PAY_OTH,0) AS PAY_OTH ,
       Y.PAY_T_BILL ,
       NVL(Y.SPEC_FUND,0) - NVL(LWD.SPEC_FUND,0) AS SPEC_FUND,                        --:SPEC_FUND – ALLOT_TAX(自AVE107FB)
                                          NVL(Y.CHK_FUND,0) - NVL(LWD.CHK_FUND,0) AS CHK_FUND                     FROM .....

Basically, program will ignore what I wrote in remarks. But I do not know why it will still interpret remarks in this case. Therefore, As I remove ":SPEC_FUND", this SQL statement will be working fine.

No comments: