Can't we use Where Class to QVD file while reading data from QVD file

[#1]

Hi Guys,

I was trying for Incremental load and so written below query:

FACT_DATA:

LOAD

* FROM (QVD)

WHERE (DATE_KEY<$(V_FROM_DATE_KEY) AND DATE_KEY>$(V_TO_DATE_KEY))

But It is not loading any data even-though I have data to (QVD) and also not throwing any error.

[jonathandienst]

Hi

What format do the date key fields and variables have? Perhaps you need quotes around the variables, like

     '$(V_TO_DATE_KEY)'

Have you tried substituting the values of the variables into the where clause to ensure that it is working as expected?

HTH

Jonathan

[#3]

Hello Jonathan,

I tried this also

FACT_DATA:

LOAD

* FROM (QVD)

WHERE (DATE_KEY<20150124 AND DATE_KEY>20150126 ) ;

but did not get any record

But this is working (When I comment one condition and left one)

FACT_DATA:

LOAD

* FROM (QVD)

WHERE (DATE_KEY<20150124

//AND DATE_KEY>20150126

)

Or

FACT_DATA:

LOAD

* FROM (QVD)

WHERE (

//DATE_KEY<20150124 AND

DATE_KEY>20150126

);

I doubt, We can't use AND in QVD query? Or it is allowed to use only one condition in WHERE class of QVD?

[jonathandienst]

Hi

You most certainly can use AND in a QVD query - I have done so in many applications.

However your AND conditions conflict - a date cannot be less than 20150124 and at the same time be more than 20150126. It seems that you have your min date and max date mixed up. Try:

WHERE (DATE_KEY>=20150124 AND DATE_KEY<20150126 )

HTH

Jonathan

PS: using a query like this will prevent an optimised load of the QVD

[#5]

Hi Jonathan,

You are right. I got confused.

I tried running same query in SQl Server and it was not returning anything.

SELECT * FROM STAR.FACT_DATA WHERE DATE_KEY<20150125 AND DATE_KEY>20150126--Wrong

My requirement is get all records WHERE DATE_KEY NOT BETWEEN 20150125 AND 20150126

I tried below query in SQR-SERVER and working .

SELECT * FROM STAR.FACT_DATA WHERE DATE_KEY NOT BETWEEN 20150125 AND 20150126

ORDER BY DATE_KEY DESC

How Same can be achieved while query .QVD file

[anbu1984]

FACT_DATA:

LOAD

* FROM (QVD)

WHERE Not (DATE_KEY>=20150124 AND DATE_KEY<=20150126 ) ;

[Colin-Albert]

You  need to use OR instead of AND

SELECT * FROM STAR.FACT_DATA WHERE DATE_KEY<20150125 OR DATE_KEY>20150126

LOAD * FROM (QVD)

WHERE DATE_KEY<20150125 OR DATE_KEY>20150126

[jagan]

Hi,

Check this

FACT_DATA:

LOAD

* FROM (QVD)

WHERE DATE_KEY > 20150124 )  AND DATE_KEY < 20150126 ;

You need to change > and < values.

This is working for me.

Regards,

Jagan.