1 Reply Latest reply: Apr 22, 2013 9:56 AM by Jindrich Kratky RSS

    Optimised Load with Where

      Hi,

       

      I would like to load a data from QVD but use filtering and keep it optimised. I know there is only one way how to do it when using WHERE clause and it is via EXISTS.

       

      Let say we have this simple QVD named TableSource.qvd:

       

      TableSource

      LOAD

           *

      INLINE [

           TableData1, TableData2, TableData3

           'A', 1, 1

           'B', 2, 0

           'A', 4, 1

           'C', 3, 0

           ] 

       

      STORE

           *

      FROM

           TableSource

      INTO

           TableSource.qvd (qvd);

       

      I would like to load to table "TableResult" data from this QVD which has "A" in the column TableData1.

       

      I tought I should be able to do something like this

       

      TableResult:

      LOAD

           *

      FROM

           TableSource.qvd (qvd)

      WHERE

           exists(TableData1, 'A');

       

      or use the similar logic with third flag column. But it doesn't work.

       

      I found a way using the INNER JOIN but it is without WHERE. I'm sure there has to be way using WHERE clause.

       

       

       

      Just for info the optimised way via INNER JOIN is this:

       

      LOAD

           *

      INLINE [

           TableData1

           A

           ];

       

      INNER JOIN

           ([TableRestriction])

      LOAD

           *

      FROM

           TableSource

      INTO

           TableSource.qvd (qvd);

       

      Without WHERE it just load all data from TableSource.qvd and filter it aftewards. Using WHERE it filter it when loading rows.

       

      Can anybody see some more suitable way?

       

       

       

      Regards,

      Jindra

       

      Message was edited by: jindrichk

        • Re: Optimised Load with Where

          You cannot load from one table into another if they are not the same.

           

          You have join you tables on.

           

          A where clause is your filter and have nothing to do with loading from one to another, but it can be beneficial in form of loading correct data.

           

          An exists is checking whether data from A also exists in B. Are they the same you can use an exists and only get ex. the newest data.

           

          And you can just put ur where clause after your join?