Optimised Load with Where

[jindrichk]

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

[#2]

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?