HOW TO MATCH RECORDS FROM 2ND TABLE

[upaliwije]

I have following fields in two tables

//table 1

EN:

LOAD

POLICY_NO ,

   PRD_CODE,

     POL_PERIOD_FROM,

     POL_PERIOD_TO,

     POL_SUM_INSURED,

     ME_CODE,

     BCOD,

     TRN_DATE AS RISK_DATE,

     PREMIUM

FROM

[..\..\QVD\FACT_Table\Sales.qvd]

(qvd);

//table 2

Concatenate(EN)

LOAD

  TRA_BRANCH AS BCOD,

   TRA_POLICY_NO   AS POLICY_NO,

   TRN_DATE AS RISK_DATE,

   PREMIUM AS RI_PREMIUM,

   DATE(FLOOR(TRA_VALID_FR_DT))AS  POL_PERIOD_FROM,

   date(floor(TRA_VALID_TO_DT)) AS POL_PERIOD_TO

FROM

RI_PREMIUM.QVD

(qvd);

I want to load data from second table if following fields match from the 1st Table

Table 1                           Table 2

POLICY_NO       =          TRA_POLICY_NO

POL_PERIOD_FROM  =  date(floor(TRA_VALID_TO_DT)) AS POL_PERIOD_TO

Pls someone help me to modify the script

[#2]

HI,

you you should be able to do this by using the exists function.  The syntax should be like the following:

where exists(field from table 1,field from table two)

PRoving the the the dates are in the same format in each table the statement should be the same for this also.

thanks

stuart

[upaliwije]

Thanks

Can u pls modify my script and reply as I am unable to understand what U say

[#4 · Accepted solution]

Code should look like this:

EN:

LOAD

POLICY_NO ,

   PRD_CODE,

     POL_PERIOD_FROM,

     POL_PERIOD_TO,

     POL_SUM_INSURED,

     ME_CODE,

     BCOD,

     TRN_DATE AS RISK_DATE,

     PREMIUM

FROM

[..\..\QVD\FACT_Table\Sales.qvd]

(qvd);

//table 2

Concatenate(EN)

LOAD

  TRA_BRANCH AS BCOD,

   TRA_POLICY_NO   AS POLICY_NO,

   TRN_DATE AS RISK_DATE,

   PREMIUM AS RI_PREMIUM,

   DATE(FLOOR(TRA_VALID_FR_DT))AS  POL_PERIOD_FROM,

   date(floor(TRA_VALID_TO_DT)) AS POL_PERIOD_TO

FROM

RI_PREMIUM.QVD

where exists (field from table one,field from table two)

[robert99]

or try

where exists (POLICY_NUM,TRA_POLICY_NO )

AND EXISTS (POL_PERIOD_FROM,date(floor(TRA_VALID_TO_DT)) );

[#6]

Good point I didn't think of doing it that way

[robert99]

Yours should work though I think

where exists (POLICY_NUM,TRA_POLICY_NO )

AND EXISTS (POL_PERIOD_FROM,TRA_VALID_TO_DT);

I have done this in the past but I can't remember what way finally worked

[upaliwije]

THANKS ALL

[#9]

If this discussion is completed please can you mark it as answered.

thanks