10 Replies Latest reply: Apr 27, 2017 3:05 AM by Rahul Pawar RSS

    Join to Oracle

    Darmawan Suria

      Hi,

       

      Anybody can help me, how to do Inner Join to Oracle without loading all record first.

      for example I have script like this :

      GET_ALL_INVESTOR:

      LOAD

          "@1:20"     as INV_SID,

          "@23:42"    as INV_SEC_ACC,

          "@45:49"    as INV_AB_ID,

          "@104:303" as INV_NAME,

          '0' & "@360:399" as INV_ACCT_NO,

          "@402:415" as INV_ACCOUNT_STATUS

      FROM [lib://MIS_Folder/Dashboard_SCM\Investor_All.txt]

      (fix, codepage is 1252, no labels, header is 16 lines)

      where Match("@402:415", 'CLOSE', 'OPEN');

       

      inner Join(GET_ALL_INVESTOR)

       

      Load CUST_NO,

        ACCT_CARD_NO as INV_ACCT_NO,

           ACCT_NAME,

           ACCT_STATUS;

          

      SELECT a.cust_no, a.acct_card_no, a.acct_name, a.acct_status

        FROM ACCT_DIM a;


      There are 50 million records in table Acct_Dim.

      Right now, it looks like Qliksense load 50 million records to memory and then start doing inner Join.

       

      Is there any way to load only the data from ACCT_DIM where ACCT_CARD_NO in file Investor_All.txt ?

       

      Thanks

        • Re: Join to Oracle
          P M

          Try this ..

           

           

          GET_ALL_INVESTOR:

          LOAD

              "@1:20"     as INV_SID,

              "@23:42"    as INV_SEC_ACC,

              "@45:49"    as INV_AB_ID,

              "@104:303" as INV_NAME,

              '0' & "@360:399" as INV_ACCT_NO,

              "@402:415" as INV_ACCOUNT_STATUS

          FROM [lib://MIS_Folder/Dashboard_SCM\Investor_All.txt]

          (fix, codepage is 1252, no labels, header is 16 lines)

          where Match("@402:415", 'CLOSE', 'OPEN');

           

          inner Join(GET_ALL_INVESTOR)

           

          Load CUST_NO,

            ACCT_CARD_NO as INV_ACCT_NO,

               ACCT_NAME,

               ACCT_STATUS;

             

          SELECT a.cust_no, a.acct_card_no, a.acct_name, a.acct_status

            FROM ACCT_DIM a

          where a.ACCT_CARD_NO in ('Investor_All.txt');



          Thanks.

          • Re: Join to Oracle
            Anil Babu

            Your script ok to me but thing is you don't have Common field from two tables

             

            PS: Inner Join - The keyword selects records that have matching values in both tables on common field

              • Re: Join to Oracle
                Darmawan Suria

                Hi Anil,

                 

                This are records in Investor_All1.txt

                ACCT_CARD_NO

                04581339041

                04582614788

                 

                and I try run this script :

                Load CUST_NO,

                  ACCT_CARD_NO;

                    

                SELECT a.cust_no, a.acct_card_no

                  FROM ACCT_DIM a

                  where a.acct_card_no in ('Investor_All1.txt');

                 

                Why The result is 0?

                Lines fetched: 0

                Creating search index

              • Re: Join to Oracle
                Rahul Pawar

                Hello Darmawan,

                 

                If you want to filter the records at database level then apply the INNER JOIN or EXISTS conditions at database level.


                //In both the cases table Investor_All must have to be there in database
                
                LOAD CUST_NO,
                     ACCT_CARD_NO as INV_ACCT_NO,
                     ACCT_NAME,
                     ACCT_STATUS;
                SELECT   a.cust_no,
                         a.acct_card_no,
                      a.acct_name,
                      a.acct_status
                FROM     ACCT_DIM a
                WHERE    NOT EXISTS (SELECT   1
                                     FROM     Investor_All
                                     WHERE    Investor_All.INV_ACCT_NO = a.acct_card_no);
                //OR
                LOAD CUST_NO,
                     ACCT_CARD_NO as INV_ACCT_NO,
                     ACCT_NAME,
                     ACCT_STATUS;
                SELECT   a.cust_no,
                         a.acct_card_no,
                         a.acct_name,
                         a.acct_status
                FROM     ACCT_DIM a
                         INNER JOIN Investor_All ON Investor_All.INV_ACCT_NO = a.acct_card_no;
                
                
                

                 

                Hope this will be helpful.


                Thank you!

                Rahul

                • Re: Join to Oracle
                  Darmawan Suria

                  HI Rahul,

                   

                  I see, so there is no other way to do this


                  Thanks