Where Statement/ MySQL-Source

[#1]

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 ?

[rustyfishbones]

Try adding ` `

WHERE `modified_time` >= $(vmrD);

[Anonymous]

Try you

LOAD

id,

modified time

Field B,

Field C

FROM `db_main`.statistics

WHERE modified_time  >= '$(vmrD)';

[Bill_Britt]

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

Bill

[Clever_Anjos]

I´m not seeing the

SQL SELECT command into your code

Do you have it?

[rbecher · Accepted solution]

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

[hic]

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

[#8]

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

[rbecher]

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