Good syntax for SQL SELECT WHERE

[#1]

Hi all,

Now that I reach a large amount of value (3 millions of row) I get an error message : "MySQL ODBC driver ran out of memory". As I check through the forum, I understand that I need to add WHERE condition in my SQL query instead of in the LOAD instructions. Problem is that I don't find the right syntax. I'm on Qlikview 9

LOAD DISTINCT
field_1,
field_2,
field_3 as keyfield;
field_4;

SQL SELECT *
FROM MyDataBase WHERE (field_1 > 0 or field_2>0 or field_3>0 or field_4>0) and date < (today()+1);

The debug tool says the FROM is lacking. And in the script editor WHERE, OR and AND don't take the blue color. I tried different things but nothing works.

If anyone has a clue, it would be very helpful.

Regards,

Julien

[Or]

today() is not a legal MySQL function, so you can't use it in an SQL Where. MySQL's function for getting today's date is CURDATE().

As a rule of thumb, I strongly recommend writing your SQL queries outside of QlikView - use a tool that suits whatever database you're loading from, such as MySQL Workbench, and then copy the SQL statement over to QlikView when you've confirmed it does what you want.

[#3]

Hi,

Ok for the date syntax.

However, when I test the expression without the date condition the problem is still there.

As I write "WHERE...", nothing is recognized included the ";" to close the SQL SELECT FROM instruction

[Anonymous]

If you have to use date condition in SQL, first crerate a variable, and use it this way:
LET Tomorrow = today()+1

SQL SELECT
...
WHERE ... and date <'$(Tomorrow )'

[#5]

Hi,

the problem is really on the WHERE;

when I write the following I get all the results includes rows with field values equal to zero :

SQL SELECT nb_impression, nb_lead, nb_vente
FROM rapport_global ;

when I write, the following it just goes down:

SQL SELECT nb_impression, nb_lead, nb_vente
FROM rapport_global where nb_impression>0 or nb_lead>0 or nb_vente>0;

I can't figure out what's wrong

[jonathandienst]

Julien

If the script that you included in your post is the same that's giving you an error, that would also be due to the semicolon after 'as keyfield;'. This should be a comma.

I am not a MySQL user, but judging from other responses, your MySQL scrip may also be a problem.

Jonathan

[#7]

Hi,

In fact the date condition was the main problem. It works without it.

What is strange is that the WHERE and following conditions after SQL SELECT * FROM don't get colored in blue and green as if there was a mistake.

Thanks all for your help. I learn different tips by the way.

Regards

Julien

[johnw]

Julien wrote:What is strange is that the WHERE and following conditions after SQL SELECT * FROM don't get colored in blue and green as if there was a mistake.

I don't think that QlikView even tries to interpret your SQL. All it does is pass it on to your ODBC driver. So it doesn't surprise me that it doesn't color your statements properly. It's probably using coloring rules from its own syntax, not from SQL syntax, and it's just getting confused. But even if QlikTech really wanted to invest some effort here, there's really no fully-reliable way for it to interpret your SQL since what's valid SQL in one database system may not be valid SQL in another, and you can connect to a whole lot of different database systems.