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.

[#2]

Hi Silviu,

You have answered your problem already, you need quotes around that field

Regards

[lironbaram]

because PERSACCID is a text you need to change the sintax

you the line to look somethong like COVER_TYPE<>'PERSACCID'

so maybe you can do something in the varaible like

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

[CELAMBARASAN]

Hi,

     Check with by using variable expression like this

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

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

It may solve your problem

Celambarasan

[jagan]

Hi,

Need to include the Quotes before and after the Text value.   You can use the expression suggested by for Variable as suggested Liron.

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

OR

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

Regards,

Jagan.

[#6]

Thank you for you're answers.

INSR_TYPE - is type NUMBER

POLICY_ID - is type NUMBER

COVER_TYPE - is type VARCHAR2

I have modify the following, I didn't got any errors, but 0 rows were imported:

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

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

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

is there a conditioning error that I don't see?

Thx!

Silviu

[CELAMBARASAN]

Hi,

     number no need quotes.so use as below itself.Varchar only need quotes.

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

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

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

Celambarasan

Yes your are right.Sorry I wrongly edited. Message was edited by: Celambarasan Adhimulam

[#8]

Thank you for you're answer, but I thik that chr(39) must be on vCOVER_TYPE because it's a VARCHAR2 type.

I have tried

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

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

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

but the error appear:

SQL##f - SqlState: S1000, ErrorCode: 936, ErrorMsg: [Oracle][ODBC][Ora]ORA-00936: missing expression

SQL select POLICY_ID, ANNEX_ID, COVER_TYPE, INSR_TYPE
FROM "INSIS_OMN"."GEN_RISK_COVERED"

where POLICY_ID=
and ANNEX_ID <> 0
and COVER_TYPE<>''
and INSR_TYPE<>
and RISK_STATE between 0 and 12

Maybe vPOLICY_ID is not correctly populate...?

[CELAMBARASAN]

Hi,

     Use this as expression for the P instead of using For loop.

     LET P=NoOfRows('Pol1')-1;

because it looks like problem with P variable only.

Celambarasan

[#10]

Yes, but if I use it this way, it will take only the first record from table Pol1, the first POLICY_ID.

I have to make this verification for all POLICY_ID's that are in Pol1.