6 Replies Latest reply: Feb 8, 2016 3:05 PM by Massimo Grossi RSS

    COUNT() in SQL Script not Working

    Travis Lyon

      Hi There, I'm trying to figure out what I may be doing wrong with a Qlik file that seeks to perform counts in a temp table via SQL. The script is longer than what I have posted below, but I'm able to get this to return results in a different query application.

       

      When I add it into my qlikView document, it doesn't throw an error, but it also doesn't return results.

       

      I've been able to narrow it down to the COUNT() function, but I don't understand exactly WHY this isn't working in qlikView... is it a reserved keyword it's not compiling properly, or something else?

       

      SELECT ROW_NUMBER() OVER(PARTITION BY t.Record_ID ORDER BY t.Order_Placed) AS rowNumb,

        t.Record_ID,

        t.Order_Shipped,

        t.Value

      INTO #treatmentSummary

      FROM #treatments AS t

      WHERE t.Section_Field_Name = 'Scheduled Treatment #'

       

      SELECT d.Record_ID

        , COUNT(ts.RowNumb) OVER(PARTITION BY d.Record_ID) as No_of_Orders

      INTO #temp_orderCount

      FROM #Data AS d

        LEFT JOIN #treatmentSummary AS ts

        ON d.Record_ID = ts.Record_ID

       

      When i replace the above red text with

      , 1 as No_of_Orders

      the code runs.

       

      I know there are some dependent tables missing in the above queries, but does anyone know why this is happening, and how I might be able to get around it? Thanks in advance for your insight!

        • Re: COUNT() in SQL Script not Working
          Srikanth P

          You can't use your already loaded table on other SQL statement.

           

          Are you executing any procedure in Qlikview ? Please share your script ?

            • Re: COUNT() in SQL Script not Working
              Travis Lyon

              Thanks Dathu, and sorry if I'm being unclear. This is all being handled in SQL code, of which COUNT() is a valid function. I'm not 100% sure what you mean by "executing procedures in Qlik", but the entirety of the SQL code is being executed in Qlik via an OLEDB connection.

               

              The script itself should be fine; it executes in other querying tools like Management Studio; I'm trying to understand why it doesn't execute in Qlik while not throwing any errors.

            • Re: COUNT() in SQL Script not Working
              Marcus Sommer

              Quite often it's helpful to make your transformations within qlikview and not within the sql-part which will be executed by the database, see: Preceding Load.

               

              - Marcus

                • Re: COUNT() in SQL Script not Working
                  Travis Lyon

                  Thank you Marcus; I'm not sure that will be possible in this case. The temp table has a lot of dependencies to other elements in the SQL script.

                   

                  I figured out a workaround for this instance, but I'm still just trying to understand WHY this wouldn't work in Qlik. Count() is a pretty common function; why wouldn't Qlik be able to handle it within the SQL code?

                    • Re: COUNT() in SQL Script not Working
                      Marcus Sommer

                      It hasn't anything to do with qlikview if a sql-statement didn't worked then qlikview don't execute the sql - the order is the following: qlikview connects to your database and gives the sql-statement to the odbc/oledb-driver which communicate between database and qlikview which will only return the result of the query.

                       

                      In your case it could be that your odbc/oledb isn't appropriate for your database respectively to your kind of sql-statement - many driver provides only old standard-sql and not quite new and/or specialized sql-features.

                       

                      - Marcus

                  • Re: COUNT() in SQL Script not Working
                    Massimo Grossi

                    Regarding this statement

                     

                    SELECT d.Record_ID

                      , COUNT(ts.RowNumb) OVER(PARTITION BY d.Record_ID) as No_of_Orders

                    INTO #temp_orderCount

                    FROM #Data AS d

                      LEFT JOIN #treatmentSummary AS ts

                      ON d.Record_ID = ts.Record_ID

                     

                    I would check if the 2 # tables have data before this statement and if the left join between the 2 tables gives some result.

                    I would check what happens removing the INTO .......; do you get some rows in Qlik?

                    Also, maybe you can replace the COUNT....OVER with a group by?