Issue with where Clause in Script

[#1]

Hi All

I am having a issue in my script.

I have a number of codes which i wish to exclude from my load when importing my data.

I am using the a where clause to exclude these but for some reason when i use exclude more than two codes the where clause does not work.

below is my code..

2.3:

LOAD ClaimID as ClaimID2.3,

  ClaimNumber as ClaimNumber2.3,

     SourceSys as SourceSys2.3,

     ClaimType as ClaimType2.3,

     TypeOfLoss as TypeOfLoss2.3,

     CauseOfLoss as CauseOfLoss2.3,

     ClaimStatus as ClaimStatus2.3,

     PolicyNumber as PolicyNumber2.3,

     PolicyHolder as PolicyHolder2.3,

     AutoNumber (ClaimStatus & ClaimType & TypeOfLoss & SourceSys & GBMPLGroup & ClaimOwnerBusUnit & ClaimOwnerLoc & ClaimOwner & ReportMonth) as Key3,

     AgencyBranchCode as AgencyBranchCode2.3,

     IncidentDate as IncidentDate2.3,

     Notificationdate as Notificationdate2.3,

     ClosedDate as ClosedDate2.3,

     ClaimOwner as ClaimOwner2.3,

     ClaimOwnerLoc as ClaimOwnerLoc2.3,

     ClaimOwnerBusUnit as ClaimOwnerBusUnit2.3,

     GBMPLGroup as GBMPLGroup2.3,

     CoInsuranceInd as CoInsuranceInd2.3,

     ReInsuranceInd as ReInsuranceInd2.3,

     Bordereau_Indicator as Bordereau_Indicator2.3,

     legacy as legacy2.3,

     CM_Paid as CM_Paid2.3,

     CM_OS as CM_OS2.3,

     CM_Rec as CM_Rec2.3,

     CM_NIC as CM_NIC2.3,

     PM_Paid as PM_Paid2.3,

     PM_OS as PM_OS2.3,

     PM_Rec as PM_Rec2.3,

     PM_NIC as PM_NIC2.3,

     CM_PM_Movmnt as CM_PM_Movmnt2.3,

     CM_PM_Resv_Movmnt as CM_PM_Resv_Movmnt2.3,

     CCSRecoveryReserve as CCSRecoveryReserve2.3,

     ReportMonth as ReportMonth2.3,

     LastTransaction as LastTransaction2.3

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq)

Where (ClaimOwnerLoc <> 'NP11' or 'FR01' or 'NP01' or 'NL01' or 'BE01' or 'NP10' or 'HS01' or 'IT01' or 'NP08' or 'NP21' or 'GE01' or 'SP01' or 'NP16' or 'NP15' or 'US01' or 'NP26' or 'NP20' or 'NP18' or 'IR01' or 'NP07' or 'NP17' or 'NP19' or 'NP25' or 'NP06' or 'NP35' or 'NP33' or 'PI01' or 'CA01' or 'NP43' or 'MD01');

Can anyone tell me where i am going wrong??

Thanks

andrew

[#2]

I think you, unfortunately, may have to list out the "ClaimOwnerLoc <>" each time so like....
Where (ClaimOwnerLoc <> 'NP11' or ClaimOwnerLoc <> 'FR01' or ClaimOwnerLoc <> 'NP01' .........  If it were a SQL select you could use an IN clause with a value list, but I am not sure that will work with QV LOAD statement.

[sunny_talwar · Accepted solution]

You cannot used Where like the have you have used. You have to specify the name of the field each time for a condition or use Match Function.

Where ClaimOwnerLoc <> 'NP11' or ClaimOwnerLoc <> 'FR01' and so on

or use this

Where Not Match(ClaimOwnerLoc, 'NP11', 'FR01', .... and so on)

Personally, I like the second method more

Where not Match (ClaimOwnerLoc, 'NP11', 'FR01', 'NP01', 'NL01', 'BE01', 'NP10', 'HS01', 'IT01', 'NP08', 'NP21', 'GE01', 'SP01', 'NP16', 'NP15', 'US01', 'NP26', 'NP20', 'NP18', 'IR01', 'NP07', 'NP17', 'NP19', 'NP25', 'NP06', 'NP35', 'NP33', 'PI01', 'CA01', 'NP43', 'MD01');

HTH

Best,

S

[engishfaque]

Dear Andrew,

I recommended you, please use match.

Example:

Where Match(ClaimOwnerLoc, 'NP11', 'FR01', 'NP01);

or

Where not Match(ClaimOwnerLoc, 'NP11', 'FR01', 'NP01);

Kind regards,

Ishfaque Ahmed

[#5]

Thanks this seems to work now...