12 Replies Latest reply: Nov 25, 2015 4:12 AM by Settu Periyasamy RSS

    left join (in initial load from database)

    Christian Wylezol

      Hi,

       

      I load now data from an Oracle database and store them in a qvd-file. But this storage needs to get an additional column, which I cannot load during the Initial load. Normally I would do a left join, but this dont work, because I just load and store them, I dont have a table to join.

       

      Does anybody have an idea?

      How can I load twice from the same database and store them in one file?

       

      Example:

      (THIS Works!!!)

      OAS_DETAILS:

      Load

      ....

      autonumber(CMPCODE&'-'&DOCCODE&'-'&DOCNUM) AS DOCLINEKEY,

       

      VALUEHOME,

      PAYDATE,

      MATCHLEVEL

       

       

      SQL SELECT
      // DOCHEAD
      DH.CMPCODE, DH.DOCCODE, DH.DOCNUM, DH.USRNAME,
      DH.STATUS,
      DH.YR, DH.PERIOD, DH.CURDOC, DH.DOCDATE, DH.INPDATE, DH.XREF,
      // DOCLINE
      DOCLINENUM, EL1, EL2, EL3, EL4, EL5, EL6, EL7, EL8, DUEDATE, VALDATE,
      VALUEHOME, VALUEDOC, DOCRATE, STATUSER, STATREC, STATPAY,
      STATPAYINT, DL.DESCR, REF1, REF2, REF3, REF4, REF5, REF6,
      LINETYPE, "DEB_CRED_IND", TAXLINECODE, DOCTAXTURN, HOMETAXTURN, DOCSUMTAX, HOMESUMTAX, CUSTSUPP,
      PAYDATE, STATREM, REMDATE, SEVERITY, LETTERSEV, MATCHLEVEL
      FROM "OAS_DOCHEAD" DH, "OAS_DOCLINE" DL
      WHERE DH.CMPCODE LIKE '$(COMPCODE_SQL)'
      AND DH.DOCCODE = DL.DOCCODE 
      AND DH.DOCNUM = DL.DOCNUM
      AND DH.CMPCODE = DL.CMPCODE
      //AND DH.STATUS = '$(DOC_STATUS)'
      AND DH.YR >= '$(varMinJahrFix)'
      AND DH.YR <= '$(varMaxJahrFix)'
      ;

       

       

      (THIS DONT WORK)

       

       

      LEFT JOIN(OAS_DETAILS)
      LOAD

      autonumber(CMPCODE&'-'&DOCCODE&'-'&DOCNUM) AS DOCLINEKEY,
      PAYDATE AS paymatchdate
      RESIDENT Buchungen
      FROM "OAS_DOCHEAD" DH, "OAS_DOCLINE" DL
      WHERE DH.CMPCODE LIKE '$(COMPCODE_SQL)'
      AND DH.DOCCODE = DL.DOCCODE 
      AND DH.DOCNUM = DL.DOCNUM
      AND DH.CMPCODE = DL.CMPCODE
      AND DL.MATCHLEVEL <> '0'
      AND DH.STATUS = '$(DOC_STATUS)'
      AND DH.YR >= '$(varMinJahrFix)'
      AND DH.YR <= '$(varMaxJahrFix)'
      ;

       

       

      I have in a first load 4 columns generated and just wanted to add a 5th column with the second load.

      As I am not an IT-Guy, I would be very thankful, if you could write to me, what I should copy into Script for the second load.

       

       

      Thank you in advance.

      Chris