topic Re: Where Statement/ MySQL-Source in QlikView

Where Statement/ MySQL-Source

Wed, 13 Nov 2013 20:28:29 GMT

Noob Question:

I am trying to use a WHERE Statement in a QV-Script with an ODBC-Connect to a MySQL-Server an a local qvw.

...

Let vmrD = Peek('modified_time', -1, 'statistics');

...

LOAD

id,

modified time

Field B,

Field C

FROM `db_main`.statistics

WHERE modified_time >= $(vmrD);

...

But WHERE doesn't work with the given Data-Source [even if i ad (mq)] 😉

Is there an alternative way or a workaorund ?

Re: Where Statement/ MySQL-Source

rustyfishbones — Wed, 13 Nov 2013 20:34:30 GMT

Try adding ` `

WHERE `modified_time` >= $(vmrD);

Re: Where Statement/ MySQL-Source

Anonymous — Wed, 13 Nov 2013 20:46:39 GMT

Try you

LOAD

id,

modified time

Field B,

Field C

FROM `db_main`.statistics

WHERE modified_time >= '$(vmrD)';

Re: Re: Where Statement/ MySQL-Source

Bill_Britt — Wed, 13 Nov 2013 20:54:54 GMT

If you are running 11.20 SR4 there is an issue with Grave Accents `` you will need to user [ ] or "

Bill

Re: Where Statement/ MySQL-Source

Clever_Anjos — Wed, 13 Nov 2013 20:55:13 GMT

I´m not seeing the

SQL SELECT command into your code

Do you have it?

Re: Where Statement/ MySQL-Source

rbecher — Wed, 13 Nov 2013 20:57:51 GMT

Hi,

if field modified_time is a date/timestamp you shoud use MySql STR_TO_DATE function:

WHERE modified_time >= STR_TO_DATE('$(vmrD)','%Y-%m-%d %h:%i:%s');  -- if timestamp format is YYYY-MM-DD HH24:MI:SS

Please also take a look here:http://www.sqlines.com/mysql/functions/str_to_date

- Ralf

Re: Where Statement/ MySQL-Source

hic — Thu, 14 Nov 2013 08:36:35 GMT

You need to use a SQL SELECT if you want to connect to the database. A Load ... From will load from a file source, e.g. Excel.

HIC

Re: Where Statement/ MySQL-Source

Thu, 14 Nov 2013 09:11:22 GMT

Is there a way to use WHERE after the SQL-Connect? Something like:

SQL SELECT * FROM `db_main`.statistics

WHERE modified_time >= $(vmrD);

or is it necessary to store the Tables in a file source first and LOAD (again) ?

11.20 SR3 x64

Re: Where Statement/ MySQL-Source

rbecher — Thu, 14 Nov 2013 09:19:30 GMT

Hi,

the Where Clause is part of the SQL statement against the database.

If field modified_time is a date/timestamp you shoud use MySql STR_TO_DATE function:

WHERE modified_time >= STR_TO_DATE('$(vmrD)','%Y-%m-%d %h:%i:%s'); -- if timestamp format is YYYY-MM-DD HH24:MI:SS

- Ralf