7 Replies Latest reply: Aug 2, 2010 7:02 PM by John Witherspoon RSS

    Good syntax for SQL SELECT WHERE

      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

        • Good syntax for SQL SELECT WHERE
          Or Shoham

          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.

          • Good syntax for SQL SELECT WHERE
            Michael Solomovich

            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 )'

              • Good syntax for SQL SELECT WHERE

                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

                  • Good syntax for SQL SELECT WHERE

                    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

                      • Good syntax for SQL SELECT WHERE
                        John Witherspoon

                         


                        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.

                  • Good syntax for SQL SELECT WHERE
                    Jonathan Dienst

                    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