6 Replies Latest reply: Apr 16, 2015 3:36 PM by neha mishra RSS

    Query fetching result in sql editors, not in Qlik.

      I am facing an issue. I have below load:


      Connection string

       

      Load *;

      SQL Select field1,

                        field2,

                        field3....

              from tables

              where conditions;


      • The select query used in the above load runs fine on toad.
      • Total record set is 88000 records and query executes in ~30 secs
      • The connection string is fine
      • The query loads data if I add another where clause for a single record.
      • Another queries which take around same time or even more loads fine.

       

      Since the query loads result for one record, I failed to identify why it should not fetch data for all the records if execution time is not an issue. Can someone help to point some other things I should look for.

       

      Thank you

        • Re: Query fetching result in sql editors, not in Qlik.
          Ramon Covarrubias

          when you say it not loading, are you trying to apply any transformation ?

           

          are you able to apply a where with the condition will be equal to true for all the items you want ?

            • Re: Query fetching result in sql editors, not in Qlik.

              1) No, just the load, by clicking the reload button.

               

              2) conditions are like below ones.

              Select

              from Table1, Table2, Table3, Table4, Table5, Table6

              where Table1.key1 = Table2.key1

              and other join conditions

              and Table3.DateCol1 >= ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -16) and Table3.DateCol1 <= TRUNC(SYSDATE, 'Q')

              and Table2.Col3 IN ('','','')

              and Table5.Col4 IN ('','','')

               

               

              In toad I am able to see the data.

                • Re: Query fetching result in sql editors, not in Qlik.
                  Paul Scotchford

                  If you're using SQLServer for example , the connection to the Instance from QlikView you'll need to ensure the

                  credentials are set the same. As you're using TOAD , I'm just guessing an Oracle connection maybe.

                   

                  As I say ensure the QlikView credentials are authorized in the host database server to select from those tables.

                   

                  E.g. if connection is from QV Server then the service name credentials (which are usually the least priv'd) need to be authorised at the db server.

                   

                  Or if running direct from a standalone QlikView installation (i.e. Not a server install using a lease) then

                  as said above, check the connection from QV uses the same credentials as your Toad connection.

                    • Re: Query fetching result in sql editors, not in Qlik.

                      Yes using Oracle, I can say the connection is fine, as if I change below condition in query

                       

                      and Table3.DateCol1 >= ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -16) and Table3.DateCol1 <= TRUNC(SYSDATE, 'Q')    --(1 Jan 2014 to 1 April 2015 data)


                      to


                      and Table3.DateCol1 <= TRUNC(SYSDATE, 'Q')-2 and Table3.DateCol1 <= TRUNC(SYSDATE, 'Q') --(30 March 2015 to 1 May 2015 data)


                      the load completes and does not hangs. 


                      Is there any way to gauge how much time load will take place in Qlik, as query in Toad takes around 30 secs but in Qlik I kept load running for three hours but still it didn't complete.


                        • Re: Query fetching result in sql editors, not in Qlik.
                          Paul Scotchford

                          As a rule, QV will do nothing with the query, other than to pass the SQL string to the target server for execution.

                          If it works in Toad it should work in QV unless credential issues exist in connection, but you say it loads if you change the query.

                           

                          As for time, basically and I only speak for my SQLServer experience, generally the load time is roughly the

                          same as testing the query directly in the UI for SQLServer, of course caveats apply, server loads, server Config etc.

                           

                          Can you create a view that does the query and just load using Select * from <view> ? I tend to abstract everything in views in the db server then I don't need to rely on any sql logic contained in QV.

                           

                          What is your environment?