2 Replies Latest reply: Jul 3, 2012 1:53 AM by jagan mohan rao appala RSS

    QVD files instead of SQL

    Gerard Bakker

      Hi there,

       

      Can anyone help me with using QVD files instead of an SQL. The problem for me is the JOIN clause in combination with RESIDENT.

              

      This is the SQL:

           

      SQL SELECT
      TGM.BOEKJAAR as YEAR,
      TGM.PERIODE as PERIOD,
      TGM.CDGRBREKENING as CDGRBREKENING,
      TGM.DEBET as DEBET,
      TGM.CREDIT as CREDIT,
      TGM.BOEKINGSDATUM as BOEKINGSDATUM,
      TGB.CODERUBRIEK1 as CODERUBRIEK1,
      TRS.OMSCHRIJVING as OMSCHRIJVING
      FROM SYSADM.GROOTBOEKMUTATIES TGM

      JOIN SYSADM.GROOTBOEK TGB

           ON TGB.BOEKJAAR = TGM.BOEKJAAR

           AND TGB.CDGRBREKENING = TGM.CDGRBREKENING

      JOIN SYSADM.RUBRIEKSCHEMA TRS

           ON TRS.CODE = TGB.CODERUBRIEK1

      WHERE TGM.BOEKJAAR >= 2010
      AND TGM.PERIODE >= 0
      ORDER BY
      TGM.BOEKJAAR,
      TGM.PERIODE,
      TGM.CDGRBREKENING

            

               

      The QVD files are:

          

      1. GROOTBOEK.qvd
      2. GROOTBOEKMUTATIES.qvd
      3. RUBRIEKSCHEMA.qvd

            

               

      Many thanks!!

      Gerard.

        • Re: QVD files instead of SQL

          Almost the same

           

          load BOEKJAAR , CDGRBREKENING , ...

          from GROOTBOEKMUTATIES.qvd

          where

          BOEKJAAR >= 2010

          AND PERIODE >= 0;

           

          join

          load

          BOEKJAAR , CDGRBREKENING , CODERUBRIEK1...

          from GROOTBOEK .qvd; (it will auto join the same field names)

           

          join

          CODE as CODERUBRIEK1,

          ...

          from RUBRIEKSCHEMA .qvd

           

           

          no resident needed if you make the joins when loading the qvds.

           



          • Re: QVD files instead of SQL
            jagan mohan rao appala

            Hi,

             

            There is no need of resident join, you can straight away join tables while loading those.

             

            LOAD

                 *

            FROM GROOTBOEK.qvd;

             

            JOIN

            LOAD

                 *

            FROM GROOTBOEKMUTATIES.qvd;

             

            JOIN

            LOAD

                 *

            FROM RUBRIEKSCHEMA.qvd;

             

            Hope this helps you.

             

            Regards,

            Jagan.