5 Replies Latest reply: Sep 12, 2017 1:20 AM by Tamil Nagaraj RSS

    Join 2 tables with 0 where there is no Value

    Fabrizio Giorgio

      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;

        • Re: Join 2 tables with 0 where there is no Value
          Shraddha Gajare

          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;

          • Re: Join 2 tables with 0 where there is no Value
            Tamil Nagaraj

            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;

              • Re: Join 2 tables with 0 where there is no Value
                Fabrizio Giorgio

                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

                  • Re: Join 2 tables with 0 where there is no Value
                    Tamil Nagaraj

                    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;