9 Replies Latest reply: Jun 3, 2015 9:00 AM by Sunny Talwar RSS

    Getting Error while loading data

      Hi All,

       

      I am trying to reload below code up to last join in 2nd tab reloading data smoothly. But when it comes to last left join in Tab2 , system is hanging up to 30 min then getting error as general script error. Even if i change the order of last 4 tables i am facing same issue (Hanging on 4th table). Could you please help me here.

       

      Tab1:-

       

      Act:

      Load

      ActID,

      Begdate,

      category

      ICID,

      Amount

      from Act.Qvd(qvd);

       

      left join

      Load

      Campkey,

      CampID as ActID

      from camp.qvd(qvd);

       

       

      left join

      Load

      ActID,

      Amt1,

      Amt2,

      Amt3,

      Amt4

      from resp.qvd(qvd);

       

       

      left Join

      Load

      Lkey,

      CampKey

      from LCamp.qvd(qvd);

       

       

      left join

      Load

      LKey,

      LID

      from LData.qvd(qvd);

       

       

      Tab2:-

       

      Empty:

      Load

      ActID,

      Sum(Amt1)+Sum(Amt2)+Sum(Amt3)+Sum(Amt4)+Count(LID) as Resp

      resident Act group by ActID;

       

      Left Join

      Load

      Lkey,

      CampKey

      from LCamp.qvd(qvd) where exists(CampKey) and Flag='Y';

       

      Left Join

      Load

      Lkey,

      Flag,

      LID

      from LData.qvd(qvd) where exists(LKey) and Converted='0';

       

      Left Join

      Load

      CampKey,

      CampIndicator,

      Okey

      from OCamp.qvd(qvd) where exists(CampKey) and CampIndicator='Y';

       

      Left join

      Load

      OID,

      ConvfromLD,

      Okey

      from OData.qvd(Qvd) where exists(Okey);

       

      Final:

      Noconcatenate

      load

      ActID,

      if(Sum(Resp)+count(if(Flag='Y',LID))+Count(if(CampIndicator='Y',OID))=0,'Yes','No') as DispFlag

      resident Empty group by ActID;

       

      drop table Empty;

        • Re: Getting Error while loading data
          Jyothish KC

          Hi Reddy,

           

          I believe this is because you are joining the same table again in Tab2, when the data is already present in the table Act.

           

          Try to store ACT table into a QVD. Then comment the script  of ACT Table and use the QVD in Tab2.

           

          Regards

          KC

            • Re: Getting Error while loading data
              Sunny Talwar

              Instead of just dropping Empty, try to dropping both Empty and Act.

               

              In addition to this I see that on your tab2 you are doing some left joins into Empty but those won't do anything because you don't have any matches with the actually Empty:

               

              Tab2:-

               

              Empty:

              Load

              ActID,

              Sum(Amt1)+Sum(Amt2)+Sum(Amt3)+Sum(Amt4)+Count(LID) as Resp

              resident Act group by ActID;

               

              Left Join

              Load

              Lkey, //Not Present above in Empty

              CampKey //Not Present above in Empty

              from LCamp.qvd(qvd) where exists(CampKey) and Flag='Y';


              Similarly, none of the other joins will actually do any join. You will only have the intial Empty table which is using a resident load from Act.

               

              HTH

               

              Best,

              Sunny

            • Re: Getting Error while loading data
              Gabriel Oluwaseye

              Hi,

               

              The tab 2 that you're left joining don't have common field. See my comments

              Tab2:-

               

              Empty:

              Load

              ActID,

              Sum(Amt1)+Sum(Amt2)+Sum(Amt3)+Sum(Amt4)+Count(LID) as Resp

              resident Act group by ActID;

               

              Left Join

              Load

              ActID  //This field has to be included in here

              Lkey,

              CampKey

              from LCamp.qvd(qvd) where exists(CampKey) and Flag='Y';

               

              Left Join

              Load

              Lkey,

              Flag,

              LID

              from LData.qvd(qvd) where exists(LKey) and Converted='0';

               

              Left Join

              Load

              Lkey //Same here,

              CampKey,

              CampIndicator,

              Okey

              from OCamp.qvd(qvd) where exists(CampKey) and CampIndicator='Y';

               

              Left join

              Load

              Lkey //Same here,

              OID,

              ConvfromLD,

              Okey

              from OData.qvd(Qvd) where exists(Okey);

               

              Final:

              Noconcatenate

              load

              ActID,

              if(Sum(Resp)+count(if(Flag='Y',LID))+Count(if(CampIndicator='Y',OID))=0,'Yes','No') as DispFlag

              resident Empty group by ActID;

               

              drop table Empty;