Load with 3 conditions from 2 tables

[Michiel_QV_Fan]

Hello,

I'm trying to develop a load script.

It has to do the following:

Load data from CATSDB. CATSDB has 2 field which are used for the key measurement Hours.

Load data from AUFK. AUFK has 1 field which is used for the key measurement Hours.

I tried this script:

LOAD
WORKDATE,
AWART,
applymap('AUFK_MAP', RAUFNR) as Ordersoort,
CATSHOURS as Uren2030,
PERNR,
RAUFNR as Ontvangen_order,
STATUS;
SQL SELECT *
FROM CATSDB where AWART in ('Y800', 'Y801', 'Y802', 'Y803', 'Y820', 'Y821', 'Y900') and STATUS IN ('20','30');;

which works OK for the AWART and STATUS but not for the mapped value from line 3.

Unfortunately, this value is essential for the calculation.

Adding the mapping statement to the where clause doesn't work.

Anyone an idea to solve this?

Thanks! [:)]

[#2]

can't you filter by RAUFNR instead of the mapped result?

[Anonymous]

1 It is not clear if you have a map here, and if it is created correctly. Your LOAD statement assumes that there is script preceeding it:
AUFK_MAP:
MAPPING LOAD DISTINCT
Field1,
Field2;
SQL SELECT
...
FROM AUFK.

2. Applymap can work only in LOAD, but not in SELECT:
LOAD
...
WHERE ...applymap()...;
SQL SELECT...

[Michiel_QV_Fan]

No, the result of the mapping is new data.

Thanks.

[Michiel_QV_Fan]

Michael Solomovich wrote:
1 It is not clear if you have a map here, and if it is created correctly. Your LOAD statement assumes that there is script preceeding it:
AUFK_MAP:
MAPPING LOAD DISTINCT
 Field1,
 Field2;
SQL SELECT
...
FROM AUFK.
2. Applymap can work only in LOAD, but not in SELECT:
LOAD
...
WHERE ...applymap()...;
SQL SELECT... <div></div>

You are right. Unfortunately mapping doesn't work in Select..... I have to find another way.

Thanks