Left join with multiple conditions

[ranibosch]

Hi, would like to left join to a table with multiple conditions, but I have no idea how to write the script for that.

I have the following 3 tables:

1. BOM Lines:

BOM Lines.No_	BOM Lines.Production BOM No_	BOM Lines.Quantity	BOM Lines.Unit of Measure Code	BOM Lines.Version Code
MARSH001	6130663604	0,0176	KG	5
MARSH001	6130663604	0,059	KG
MARSH001	6130663604	0,059	KG	1
OATS015	6130663604	0,1381	KG	3
OATS015	6130663604	0,1558	KG	4
OATS015	6130663604	0,1618	KG	5

2. BOM Header:

Production BOM Header.BOM No	Production BOM Header.BOM UOM	Production BOM Header.Status
6130663604	CASE	2

3. BOM Version Header:

Production BOM Version.Production BOM No_	Production BOM Version.Status	Production BOM Version.Unit of Measure Code	Production BOM Version.Version Code
6130663604	3	CASE	1
6130663604	3	CASE	2
6130663604	1	CASE	3
6130663604	1	CASE	4
6130663604	0	CASE	5

I would like to join the BOM STATUS (yellow column) to the BOM LINES table.

I have the following conditions of which tables' status it must take (BOM HEADER / BOM VERSION HEADER):

a. If the VERSION CODE = BLANK in the BOM LINES TABLE. Then join the Status column from the BOM HEADER table.

b. if the VERSION CODE is not BLANK, then look for the status in the BOM VERSION HEADER table.

    THE BOM NO & VERSION CODE MUST MATCH between the BOM LINES table & BOM VERSION HEADER table.

I hope that makes sense and that I can get a solution.

Thanks in advance!

[olivierrobin]

hello

you have to create key : status + version and join on this key

and create in table header a row with blank version

the question is ; which data do you have to put in this row

[ranibosch]

Thanks, can you have a look as to why my script does not want to left join the the BOM LINES table?

//********************************************************************************************************************************************************************

//bring in the BOM Lines of all versions of BOM's

[BOM Lines]:

LOAD

"Production BOM No_",

    "Version Code",

    "Production BOM No_"&'-'&"Version Code" as [BOM KEY],

    "No_" as [Item No],

    "Unit of Measure Code",

    Quantity,

    Consumable;

SQL SELECT

"Production BOM No_",

    "Version Code",

    "No_",

    "Unit of Measure Code",

    Quantity,

    Consumable

FROM $Production BOM Line";

//********************************************************************************************************************************************************************

//bring in the BOM VERSION HEADERS

[TEMP BOM HEADERS]:

LOAD

    "Production BOM No_" as [BOM No],

    "Version Code" as [BOM Version],

    "Unit of Measure Code" as [BOM UOM],

    Status;

SQL SELECT

"Production BOM No_",

    "Version Code",

    "Unit of Measure Code",

    Status

FROM $Production BOM Version";

//bring in the BOM HEADERS and CONCATENATE with the BOM VERSIONS table

Concatenate ([TEMP BOM HEADERS])

LOAD

    "No_" as [BOM No],

    "Version Nos_" as [BOM Version],

    "Unit of Measure Code" as [BOM UOM],

    Status;

SQL SELECT

"No_",

    "Unit of Measure Code",

    Status,

    "Version Nos_"

FROM $Production BOM Header";

//create key in order to link the bom header table to the BOM lines table

left Join [BOM Lines]:

LOAD

    [BOM No]&'-'&[BOM Version] as [BOM KEY],

    [BOM UOM],

    Status

Resident [TEMP BOM HEADERS];

DROP Table [TEMP BOM HEADERS];

//********************************************************************************************************************************************************************

[olivierrobin]

at 1st sight, that's seems ok

do you have correct results when No and Version have values ?

[ranibosch]

I get the correct results when I do not left join the BOM HEADERS to the BOM LINES.(when i keep it as a seperate table)

Then I have the 2 tables, with primary key called BOM KEY.

as soon as I try to LEFT JOIN to the BOM LINES, the BOM UOM & STATUS fields disappear.

[olivierrobin]

that means you left join isn't successful

try to see if the fields contains the right values (ie no spaces somewhere, same length, ...)

[Anonymous]

Could you try the below script  which as same as what you have implemented with little adjustments.

first creation of key in BOM lines table

when the version code is blank replacing all the code with NA .Also as  you want status from BOM header only when the version code is blank so iassigned NA to all the BOMNO in BOM header Table

//********************************************************************************************************************************************************************

//bring in the BOM Lines of all versions of BOM's

[BOM Lines]:

LOAD

"Production BOM No_",

    "Version Code",

    "Production BOM No_"&'-'&if(len(trim("Version Code"))>0,"Version Code",'NA') as [BOM KEY],

    "No_" as [Item No],

   "Unit of Measure Code",

    Quantity,

    Consumable;

SQL SELECT

"Production BOM No_",

    "Version Code",

    "No_",

    "Unit of Measure Code",

    Quantity,

    Consumable

FROM $Production BOM Line";

//********************************************************************************************************************************************************************

//bring in the BOM VERSION HEADERS

[TEMP BOM HEADERS]:

LOAD

    "Production BOM No_" as [BOM No],

    "Version Code" as [BOM Version],

    "Unit of Measure Code" as [BOM UOM],

    Status;

SQL SELECT

"Production BOM No_",

    "Version Code",

    "Unit of Measure Code",

    Status

FROM $Production BOM Version";

//bring in the BOM HEADERS and CONCATENATE with the BOM VERSIONS table

Concatenate ([TEMP BOM HEADERS])

LOAD

    "No_" as [BOM No],

    'NA' as [BOM Version],

    "Unit of Measure Code" as [BOM UOM],

    Status;

SQL SELECT

"No_",

    "Unit of Measure Code",

    Status,

   "Version Nos_"

FROM $Production BOM Header";

//create key in order to link the bom header table to the BOM lines table

left Join [BOM Lines]:

LOAD

    [BOM No]&'-'&[BOM Version] as [BOM KEY],

    [BOM UOM],

    Status

Resident [TEMP BOM HEADERS];

DROP Table [TEMP BOM HEADERS];

Hope this helps.

[ranibosch · Accepted solution]

//********************************************************************************************************************************************************************

//********************************************************************************************************************************************************************

//bring in the BOM Lines of all versions of BOM's

[TEMP BOM Lines]:

LOAD

"Production BOM No_" as [BOM No],

    "Version Code" as [BOM Version],

    "No_" as [Item No],

    "Unit of Measure Code",

    Quantity,

    Consumable;

SQL SELECT

"Production BOM No_",

    "Version Code",

    "No_",

    "Unit of Measure Code",

    Quantity,

    Consumable

FROM $Production BOM Line";

//create the key field between the bom header and lines

[BOM Lines]:

LOAD

[BOM No],

    [BOM Version],

    [BOM No]&'-'&[BOM Version] as [BOM KEY],

    [Item No],

    "Unit of Measure Code",

    Quantity,

    Consumable

Resident [TEMP BOM Lines];

DROP Table [TEMP BOM Lines];

//********************************************************************************************************************************************************************

//*******************************************************************************************************************************************************************

//bring in the BOM VERSION HEADERS

[TEMP BOM HEADERS]:

LOAD

"Production BOM No_",

"Version Code",

    "Unit of Measure Code" as [BOM UOM],

    Status;

SQL SELECT

"Production BOM No_",

    "Version Code",

    "Unit of Measure Code",

    Status

FROM $Production BOM Version";

//bring in the BOM HEADERS and CONCATENATE with the BOM VERSIONS table

Concatenate ([TEMP BOM HEADERS])

LOAD

"No_" as "Production BOM No_" ,

"Version Nos_" as "Version Code",

    "Unit of Measure Code" as [BOM UOM],

    Status;

SQL SELECT

"No_",

    "Unit of Measure Code",

    Status,

    "Version Nos_"

FROM $Production BOM Header";

//create a bom key in order to join to the bom lines table

Left Join ([BOM Lines])

LOAD

    "Production BOM No_"&'-'&"Version Code" as [BOM KEY],

    [BOM UOM],

    Status

Resident [TEMP BOM HEADERS];

DROP Table [TEMP BOM HEADERS];

//********************************************************************************************************************************************************************

//********************************************************************************************************************************************************************

//filter through the newly created BOM Lines table to show only the BOM's with status = 1

[Certified BOM's]:

LOAD

[BOM No],

    [BOM Version],

    [Item No],

    "Unit of Measure Code",

    Quantity,

    Consumable,

    [BOM UOM],

    Status

Resident [BOM Lines]

Where Status = 1;

DROP Table [BOM Lines];

//filter to show the latest BOM version only

[Latest Version of Certified BOM's]:

Inner Keep LOAD

[BOM No],

    max([BOM Version]) as [BOM Version]

Resident [Certified BOM's]

Group By [BOM No];

DROP Table [Latest Version of Certified BOM's];