6 Replies Latest reply: Apr 11, 2017 10:19 PM by Calvin Wee RSS

    Multiple table join

    Calvin Wee

      Hi,

       

      I am having 2 sets of data that I need to join them but not able to get the join right.

       

      Load Table A

      Left Join Table B

      Concatenate Table C

      Left Join Table D

       

      Joining Table A, B, C is successful but when I LEFT JOIN Table D, all the data in D is loaded instead of Left Join.

       

      Any Advise?

       

      Thanks in Advance.

        • Re: Multiple table join
          Muñoz Héctor

          Hi Calvin,

          Could you attach the script sentences?

          Regards,
          H

            • Re: Multiple table join
              Calvin Wee

              Hi,

               

              Thanks for your reply. Please see below simplified form:

               

              LOAD PRNo,

                   PONo,

                   Approver1;

              [PRPO Information]

               

              [SQL A]

               

              left join ([PRPO Information])

              LOAD Plant,
                   CRFNo,
                   PRNo,
                   PRId,
                   RequestorID,
                   Requestor,
                   RequestorMAIL,
                   CostCtr,
                   CostCtrName,
                   Stage,
                   BuyerCode,
                   Buyer,
                   PRDesc,
                   VendorCode,
                   VendorName,
                   PRSubmitDT,
                   InSystem;

               

              [SQL B]

               

              Concatenate

              LOAD Plant,
                   CRFNo,
                   PRNo,
                   PRId,
                   RequestorID,
                   Requestor,
                   RequestorMAIL,
                   CostCtr,
                   CostCtrName,
                   Stage,
                   BuyerCode,
                   Buyer,
                   PRDesc,
                   VendorCode,
                   VendorName,
                   PRSubmitDT,
                   InSystem,
                   ApprovalStageCode,
                   Approver1,
                   Approver2,
                   ApproverStartDT;

               

              [SQL C]

               

              LOAD PRNo,
                   CALRequired,
                   MCFRequired;
                  
              left join ([PRPO Information])

              [SQL D]

                • Re: Multiple table join
                  Muñoz Héctor

                  Hi Calvin,

                  Is hard for me to see where is the problem without the full code. Maybe the problem is in some part of [SQL D]...

                  Regards,

                  H

                  • Re: Multiple table join
                    kushal chawda

                    try like below

                     

                    [PRPO Information]:

                    LOAD PRNo,

                         PONo,

                         Approver1;

                    [PRPO Information]

                     

                    left join ([PRPO Information])

                    LOAD Plant,
                         CRFNo,
                         PRNo,
                         PRId,
                         RequestorID,
                         Requestor,
                         RequestorMAIL,
                         CostCtr,
                         CostCtrName,
                         Stage,
                         BuyerCode,
                         Buyer,
                         PRDesc,
                         VendorCode,
                         VendorName,
                         PRSubmitDT,
                         InSystem;

                     

                    Concatenate ([PRPO Information])

                    LOAD Plant,
                         CRFNo,
                         PRNo,
                         PRId,
                         RequestorID,
                         Requestor,
                         RequestorMAIL,
                         CostCtr,
                         CostCtrName,
                         Stage,
                         BuyerCode,
                         Buyer,
                         PRDesc,
                         VendorCode,
                         VendorName,
                         PRSubmitDT,
                         InSystem,
                         ApprovalStageCode,
                         Approver1,
                         Approver2,
                         ApproverStartDT;

                     

                       
                    left join ([PRPO Information])

                    LOAD *

                    FROM Source D;

                     

                     

                    Not sure about below part

                    [SQL C]

                     

                    LOAD PRNo,
                         CALRequired,
                         MCFRequired;

                    Multiple table join

                • Re: Multiple table join
                  Calvin Wee

                  Thank you all for your valuable inputs.

                  We managed to resolve as the wrong was putting LOAD before JOIN.

                  Once changed to JOIN then LOAD, it is ok.