Join 2 tables with 0 where there is no Value

[Anonymous]

Hi all, this seems like such a simple issue but i just cannot get it to work.

I have two tables as per the attached images, One has a "complete" list of Sales Orders and Sales Order Lines. The other has only sales order / Sales Order lines that have stock reservations against them.

Ultimately I need to sum up all the reservations by product, hence why i can't have any nulls in my table.

At the moment, where there are no data Matches, I am getting a NULL. I want to convert this into a 0.

I am using IF(isNull("qty_reserve"),'0',"qty_reserve") as PBSSOLineQTYReserved; but have tried using Len() and various other combinations and functions but it just won't work...

Is anyone able to shed some light please?

here is my script:

PBSSOrdersActive:

Load

"s_order" as PBSSOrder,

"ent_code_ware" as PBSSOrderWHEnt;

SQL SELECT

s_order,

ent_code_ware

FROM pub.sorder where sorder.so_status = 2 AND sorder.ent_code_ware IN ('hc','HC');

Left JOIN (PBSSOrdersActive)

PBSSOrdLine:

Load

"s_order" as PBSSOrder,

SKU as PBSSKU,

lineno as PBSSOLineLineNo,

"qty_onorder" as PBSSOLineQTYonOrder,

"qty_picking" as PBSSOLineQTYinPicking;

SQL SELECT

s_order,

SKU,

lineno,

qty_onorder,

qty_picking

FROM pub.sordline;

Left Join (PBSSOrdersActive)

PBSReserve:

Load

"doc_num" as PBSSOrder,

"lineno_doc" as PBSSOLineLineNo,

IF(isNull("qty_reserve"),'0',"qty_reserve") as PBSSOLineQTYReserved;

SQL SELECT

doc_num,

lineno_doc,

qty_reserve

FROM pub.reserve;

[shraddha_g]

Instead of Left join, Try Apply Map.

MapTable:

Mapping Load

SOrder# &'-'& Line# as Key,

"Reservation Qty"

From Table B;

[Table A+B]:

Load *,

ApplyMap('MapTable',SOrder# &'-'& Line#,0) as "Reservation Qty"

From Table A;

[tamilarasu · Accepted solution]

Hi Fabrizo,

Try like this,

PBSSOrdersActive_Temp:

Load

"s_order" as PBSSOrder,

"ent_code_ware" as PBSSOrderWHEnt;

SQL SELECT

s_order,

ent_code_ware

FROM pub.sorder where sorder.so_status = 2 AND sorder.ent_code_ware IN ('hc','HC');

Left JOIN (PBSSOrdersActive)

PBSSOrdLine:

Load

"s_order" as PBSSOrder,

SKU as PBSSKU,

lineno as PBSSOLineLineNo,

"qty_onorder" as PBSSOLineQTYonOrder,

"qty_picking" as PBSSOLineQTYinPicking;

SQL SELECT

s_order,

SKU,

lineno,

qty_onorder,

qty_picking

FROM pub.sordline;

Left Join (PBSSOrdersActive)

PBSReserve:

Load

"doc_num" as PBSSOrder,

"lineno_doc" as PBSSOLineLineNo,

"qty_reserve";

SQL SELECT

doc_num,

lineno_doc,

qty_reserve

FROM pub.reserve;

PBSSOrdersActive:

Load *,

If(Isnull("qty_reserve"),0, "qty_reserve") as PBSSOLineQTYReserved

Resident PBSSOrdersActive_Temp;

DROP Table PBSSOrdersActive_Temp;

[Anonymous]

thanks Tamil,

wow, that worked straight away...

it is a bit of a round about way, but gives me the result I need...

so does this mean that the isNull should be done separately to a join?

Thanks again...

Fab

[Anonymous]

Thanks Shradda,

I will look into the apply map function ... I have never used it before, sounds like it's definitely worth knowing...

Thanks for your contribution on this...

Fab

[tamilarasu]

Fab,

No problem. If the data (PBSSOrder) which is available in the first table (PBSSOrdersActive_Temp) is not present in the second table (PBSReserve) which is to be joined then null values will be present. So we are using resident table to replace the null values with 0 (using if condition).

Below method is not elegant but thought of sharing with you.

PBSSOrdersActive_Temp:

Load

"s_order" as PBSSOrder,

"ent_code_ware" as PBSSOrderWHEnt;

SQL SELECT

s_order,

ent_code_ware

FROM pub.sorder where sorder.so_status = 2 AND sorder.ent_code_ware IN ('hc','HC');

Left JOIN (PBSSOrdersActive)

PBSSOrdLine:

Load

"s_order" as PBSSOrder,

SKU as PBSSKU,

lineno as PBSSOLineLineNo,

"qty_onorder" as PBSSOLineQTYonOrder,

"qty_picking" as PBSSOLineQTYinPicking;

SQL SELECT

s_order,

SKU,

lineno,

qty_onorder,

qty_picking

FROM pub.sordline;

Left Join (PBSSOrdersActive)

PBSReserve:

Load

"doc_num" as PBSSOrder,

"lineno_doc" as PBSSOLineLineNo,

"qty_reserve";

SQL SELECT

doc_num,

lineno_doc,

qty_reserve

FROM pub.reserve;

NullAsValue "qty_reserve";   // You can add more fields but only mentioned fields will be replaced with 0

Set NullValue = 0;

PBSSOrdersActive:

Load *

Resident PBSSOrdersActive_Temp;

DROP Table PBSSOrdersActive_Temp;