topic Re: Help with LET variable in QlikView

Help with LET variable

Tue, 02 Dec 2014 22:38:27 GMT

Hello

probably doing something not right but following code throwing error "ErrorSource: OraOLEDB, ErrorMsg: ORA-00933: SQL command not properly ended". I am trying to grab data from Oracle table

Let vNOWTIME = Timestamp(Now()-1);

OLEDB CONNECT32 TO [xxxxxxxxxxxxxxxxx);

SQL SELECT

"UPD_DT",

     CUSTOMER,
     PRODID,
     PROD,
     PRDNM

FROM "XE256"."ORA_78998465"

WHERE "UPD_DT" >= $(vNOWTIME);

UPD_DT has a timestamp where data was last updated.

When i replace statement "WHERE "UPD_DT" >= $(vNOWTIME);" with WHERE "UPD_DT" >= '01/12/2014';" it works fine. Seems like i am not using LET expression correctly?

Re: Help with LET variable

Tue, 02 Dec 2014 22:54:15 GMT

Hi,

It's probably a formatting tweak.

Can you try the below:

Let vNOWTIME = Date(Timestamp(Now()-1), 'DD/MM/YYYY');

Re: Help with LET variable

fkeuroglian — Tue, 02 Dec 2014 22:57:14 GMT

Hi , change your definition in your variable

create like this:

let vNOWTIME = date(today());

the function timestamps will retunr the date,hours,second and mili seconds too.

then in the where clause change to this:

WHERE "UPD_DT" >= '$(vNOWTIME)';

try this good luck

Fernando

Re: Help with LET variable

Tue, 02 Dec 2014 23:15:47 GMT

Yeap seems like a formatting now getting this error

ErrorSource: OraOLEDB, ErrorMsg: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

I guess i probably need to change "UPD_DT" to Date format??

Re: Help with LET variable

Tue, 02 Dec 2014 23:17:39 GMT

tried this too

got following

*ErrorSource: OraOLEDB, ErrorMsg: ORA-00932: inconsistent datatypes:

expected DATE got NUMBER*

I guess i probably need to change "UPD_DT" to Date format??

On Wed, Dec 3, 2014 at 9:58 AM, Fernando Keuroglian <

Re: Help with LET variable

Tue, 02 Dec 2014 23:46:31 GMT

Ok,

Try using the to_date function in the where condition:

WHERE "UPD_DT" >= to_date('$(vNOWTIME)', 'dd/mm/yyyy');

Re: Help with LET variable

Wed, 03 Dec 2014 00:13:20 GMT

Thanks. Seems to have taken this and running successfully, but looks like it is grabbing data more then today date -1...

Re: Help with LET variable

danieloberbilli — Wed, 03 Dec 2014 00:31:48 GMT

what about:

LET vNOWTIME = 'date(today,'DD/MM/YYYY')-1';

where date#(UPD_DT,'DD/MM/YYYY')>= $(vNOWTIME);

Re: Help with LET variable

Anonymous — Wed, 03 Dec 2014 01:27:40 GMT

Hi,

The format of the date you pass in the where clause must be the same of the database field. Since you tried '01/12/2014' and it worked, let's stick to that format. Create your variable like so:

Let vNOWTIME = Text(Date(Today(1), 'DD/MM/YYYY'));

Now, in the where clause you must enclose the dollar-sign substitution in single quotes:

WHERE "UPD_DT" >= '$(vNOWTIME)';

If you don't, your database engine will not receive a string or a date but an expression (02/12/2014) and will make the two divisions, that is, 2 divided by 12 divided by 2014, and compare the result with UPD_DT.

Hope it helps.

- Bruno

Re: Help with LET variable

Wed, 03 Dec 2014 01:49:44 GMT

Thanks a lot . Working well

Re: Help with LET variable

Anonymous — Wed, 03 Dec 2014 01:55:31 GMT

You're welcome. Glad I could help. Please, mark the post as answered so others will that have a similar problem can find the answer more easily.

Cheers!