Problem with conditions when loading table

[#1]

Hello,

I have a small problem. I have imported a table, and the second table must be imported with some conditions regarding to the first one:

------------------------------------------------------------------------------------

Pol1:

Load "POLICY_ID",
     "ANNEX_ID",
     "COVER_TYPE",
     "INSR_TYPE";
SQL select POLICY_ID, ANNEX_ID, COVER_TYPE, INSR_TYPE
from "..."."GEN_RISK_COVERED"
where RISK_STATE between 0 and 12
and ANNEX_ID=0;

for P=0 to NoOfRows('Pol1')-1;

LET vPOLICY_ID = Peek('POLICY_ID',$(P),Pol1);

LET vCOVER_TYPE =Peek('COVER_TYPE',$(P),Pol1);

LET vINSR_TYPE = Peek('INSR_TYPE',$(P),Pol1);

Pol2:

Load "POLICY_ID" as POLICY_ID2,
     "ANNEX_ID" AS ANNEX_ID2,
     "COVER_TYPE" AS COVER_TYPE2,
     "INSR_TYPE" AS INSR_TYPE2;
SQL select POLICY_ID, ANNEX_ID, COVER_TYPE, INSR_TYPE
FROM "INSIS_OMN"."GEN_RISK_COVERED"
where POLICY_ID=$(vPOLICY_ID)
and ANNEX_ID <>0
and COVER_TYPE<>$(vCOVER_TYPE)
and INSR_TYPE<>$(vINSR_TYPE)
and RISK_STATE between 0 and 12;

NEXT P;

------------------------------------------------------------------------------------

The error that appear when reloading is:

SQL##f - SqlState: S0022, ErrorCode: 904, ErrorMsg: [Oracle][ODBC][Ora]ORA-00904: "PERSACCID": invalid identifier

SQL select POLICY_ID, ANNEX_ID, COVER_TYPE, INSR_TYPE
FROM "INSIS_OMN"."GEN_RISK_COVERED"
where POLICY_ID=100000018936
and ANNEX_ID <>0
and COVER_TYPE<>PERSACCID
and INSR_TYPE<>6016
and RISK_STATE between 0 and 12

Can someone detects what can be the problem?

It seems that I can't get this condition:  COVER_TYPE<>$(vCOVER_TYPE)

where the first $(vCOVER_TYPE) is "PERSACCID" and it cannot be putted as a condition.

Thank you in advance,

Silviu.

[jagan]

Hi,

By applying breakpoints check whether this variables are filled with values.  The error occurred is due to empty values in the variable.

Regards,

Jagan.

[CELAMBARASAN]

Hi,

     Then there won't be a semicolon at the end of for P=0 to NoOfRows('Pol1')-1;

     Remove the semi colon and try.

Celambarasan

[#4]

I have tested my code into debug mode, and I observe that the first loop is ok (my variables get the values from the first row), but after my P increases and my variables must get the values from the seccond row, they are empty.

so is a problem with reading the first table regarding to the P row position.

when P is initial (=0), my variables are populated fine, but after P is increased, my variables are getting null.

Can you observe where is the mistake?

Thanx,

Silviu.