Conditional Load

[hvfalcao]

Hello!

How to load data based on other table field value?

I have this tables load with sql select.

tab CITY:

idcity,

namecity;

tab ORDER;

idorder,

idcustomer,

idcustomerprop,
qtyorder;

tab CUSTOMER:

idcustomer,

idcity;

tab CUSTOMER_PROP:
idcustomerprop,

idcustomer,

idcity

the condition I need on load:

IF ORDER.idcustomerprop is null THEN CUSTOMER.idcity = CUSTOMER_PROP.idcity else CUSTOMER.idcity = CUSTOMER.idcity

Each CUSTOMER, have one MAIN CITY and might have others PROPRIETIES, with different CITY.

On table ORDER, if the field idcustomerprop have any value, this means that the ORDER is related to the  CUSTOMER_PROP city, and if the same field is NULL then, the ORDER is related to the main CUSTOMER city.

When doing this on a SQL QUERY, I use case when, but I don't know how to solve this on load script...

Sorry if I couldn't explain better.

Thanks

[Peter_Cammaert · Accepted solution]

How about:

MapCustPropCity:

MAPPING LOAD idcustomer & '|' & idcustomerprop, idcity;

SQL SELECT * FROM CUSTOMER_PROP;

MapCustCity:

MAPPING LOAD idcustomer, idcity;

SQL SELECT * FROM CUSTOMER;

LOAD idorder, idcustomer, idcustomerprop, qtyorder,

          if (IsNull(idcustomerprop), applymap('MapCustCity', idcustomer),

             applymap('MapCustPropCity', idcustomer & '|' & idcustomerprop)) AS idcity;

SELECT * FROM ORDER;

Adjust the different SQL statements so that they fit your situation.

You can also accomplish the same with a few LEFT JOINS and a RESIDENT LOAD, but I think the Mapping Table-approach is faster.

Peter

[hvfalcao]

Peter,

Just perfect... Thank you very much!

Henrique