Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi Silviu,
You have answered your problem already, you need quotes around that field
Regards
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) ;
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
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.
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
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
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...?
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
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.