Left keep function

[#1]

Hello Experts

I used a "Left keep" function to link the two tables in a way that when datum_auftrag <> null, values are shown. if datum_auftrag = null,  all other values are excluded. Not only in table "job" but also the fields that are linked from table "jleistg".

SQL SELECT "job_nummer",

    jobnr,

    "datum_auftrag",

    Year ("datum_auftrag") as Jahr,

    Month ("datum_auftrag") as Monat,

    jobkategorie

FROM EASY.job where "datum_auftrag" is not null;

LEFT KEEP (job)

load *, [auf_kurs] * [auf_wert] / 100 as [Auftragswert];

SQL SELECT "auf_datum",

    "auf_fw",

    "auf_kurs",

    "auf_wert",

    bez as Leistungstext,

    jobnr,

    "auf_anzahl",

    ltyp,

    storniert,

    optional,

    lartnr 

FROM EASY.jleistg where storniert = 0 and optional = 0 and ltyp <> 0;

Now I would like to use another table with a selection criteria to exclude all linked vaues in the two tables above:

SQL SELECT bez as Jobkriterium,

    beznr as Jobkritnr

FROM EASY.divbez where Jobkriterium <> 'Internes Projekt';

Where do I have to set the "Left keep" keep in this case? I do not seem to get it right...

Thank you so much for your help...

[#2]

Hi Jan

LEFT KEEP will keep table but operate similar as a join.

If you don't precise any table it will be based on the previous table.

If you want you can precise a table wich was loaded many instruction before with

LEFT KEEP (TableName to consider)

LOAD ....

SQL SELECT ....

best regards

Chris

[#3]

Hi christianj

Thank you for your reply. So if I want to join my last table "divbez" with the other two, can I write two Left keep orders?

LEFT KEEP (Job)

LEFT KEEP (jleistg)

LOAD ....

SQL SELECT ....

[#4]

In other words how do I script multiple LEPT KEEP orders to one table?

[#5]

You would do them done at a time,

LEFT KEEP (Job)

LOAD ....

SQL SELECT ....

LEFT KEEP (jleistg)

LOAD ....

SQL SELECT ....

[#6]

Jan

as far as i know the LEFT KEEP will operate only on 1 table, so you'll have to do

T3:

LEFT KEEP (job)

LOAD ....

T4:

LEFT KEEP (jleistg)

LOAD ... resident T3

best regards

Chris