SQL select by year

[#1]

hi, i have a small problem

usually, the whole oracle DB is loaded in a QVD container.

Now, data should be loaded incrementally, so the range of time to be loaded, has to be limited.

DBconsum:

LOAD *;

SQL SELECT * FROM MYDB."consumption_s"

WHERE year(TIME)= 2012 and month(TIME) = 5

;

STORE DBconsum INTO $(vQVDStore)consumption_s_inc.QVD;

usually, that would be no big deal, its very basic sql.

the timestamp (TIME) has the format "41091,083333333" , which is usually readable by qlikview as "01.07.2012 02:00:00" or just "2012".

but it doesnt seem to work with this sql load.

any suggestions ?

[etl_tools]

Do you really need a time part?

May be you can just remove it?

Mike

[#3]

etl_tools wrote:

Do you really need a time part?

May be you can just remove it?

Mike

hi mike, im not really understanding your reply. what do you mean by "remove" ?

the TIME field is needet, otherwise, the data makes no sense.

how else is it possible to select, which months to load ?

[etl_tools]

01.07.2012 02:00:00

01.07.2012 <= Date part

02:00:00    <= Time Part (Do you use it in your dashboard?)

Which database do you work with?

Is it Oracle, MySQL ETC

Mike

[#5]

an oracle DB is used.

all parts of the timestamp are used in the dashboard, but that doesnt matter during load.

i have to load the TIME field (content= "41091,083333333"), get the year, month, and load only record from a certain month in 2012.

[MayilVahanan]

Hi

Try like this

DBconsum:

LOAD * WHERE year(TIME)= 2012 and month(TIME) = 'May';

SQL SELECT * FROM MYDB."consumption_s";

[etl_tools]

As a test can you try the following SQL to see if it works

DBconsum:

LOAD *;

SQL SELECTcast(TIME AS varchar2(40) ) as TIME

FROM MYDB."consumption_s"

WHERE year(TIME)= 2012 and month(TIME) = 5;

STORE DBconsum INTO $(vQVDStore)consumption_s_inc.QVD;

Mike

QlikView connector which works with 28 datasources and has 300 + validation functions

http://www.etl-tools.com/etl-tools/qlikview-connector/overview.html

[etl_tools]

Alexander

I probably misunderstood you.

Are you saing that when you try to load the data the script fails with error?

Or the sql does not return any data?

Mike