0 Replies Latest reply: May 30, 2013 10:57 AM by A Sheppard RSS

    QlikView Desktop 10 vs. QlikView Server 9 strange query behavior

      We have a development environment where my desktop version is 10 and the Server and Publisher are 9.  We pulish our qvw files to a iFrame with AJAX for a SAAS software.  I am still a novice to the whole process, but as I understand it, the QV Server houses a .qvw file then the Publisher transforms that file to a html rendered version along with the data and then AJAX picks up the html version and ports it to a iFrame in the application where the user interacts with the report's face without any of the QV controls.

       

      A few revisions back on a file, the load statement was

      SQL
      //most recent mResult date
      select custID,  Max(RunDate) as Max_Run_Date from mResults
      group by custID
      ;
      SQL
      //most recent mResult score
      select custID, min(Score) as Max_Run_Date_Score from mResults
      Where RunDate in (select max(RunDate) from mResults group by custid)
      group by custid 
      ;
      SQL
      //most recent mResult score with review tied to it
      select m1.custID, min(Score) as Has_Review_Score from mResults  m1
      where rundate in (select max(m.rundate) from mResults m 
      join vw_review r on r.mResultsreviewid = m.mResultid
      group by m.custid)
      group by m1.custid
      ;
      

       

      Now it is changed to below due to a changed table structure and more accurate results

      SQL
      WITH T1
          AS
          (
          -- MostRecentScore
            SELECT fs.FinstatementID,CASE WHEN YEAR(fs.[FinStatement.PeriodDATE]) = 1776 THEN NULL ELSE fs.[FinStatement.PeriodDATE] END AS PeriodDate,
            m1.RiskLevel,m1.custID,modID,modResultID,RunDate,Min(Score) AS MinScore
            FROM modResults m1 WITH (NOLOCK)
            INNER JOIN (SELECT custID,MAX(RunDate)AS MaxDate FROM modResults m GROUP BY m.custID) m2
            ON m1.custID = m2.custID AND m1.RunDate = m2.MaxDate
            LEFT OUTER JOIN vw_FinStatement fs
            ON m1.custID = fs.custID AND m1.FinStatementID = fs.FinStatementID
            GROUP BY
            fs.FinstatementID,fs.[FinStatement.PeriodDATE],m1.RiskLevel,m1.custID,modID,modResultID,RunDate
            
          )
          ,T2
          AS
          (
            --MostRecentReviewedScore
            SELECT fs.FinstatementID,CASE WHEN YEAR(fs.[FinStatement.PeriodDATE]) = 1776 THEN NULL ELSE fs.[FinStatement.PeriodDATE] END AS PeriodDate,
            m1.RiskLevel,m1.custID,modID,RunDate,MIN(Score) AS MinScore
            FROM modResults m1 WITH (NOLOCK)
            INNER JOIN (SELECT t.custID,Max(RunDate)AS MaxDate FROM modResults t JOIN vw_review r ON r.modresultsreviewid = t.modResultID GROUP BY t.custID) m2
            ON m1.custID = m2.custID AND m1.RunDate = m2.MaxDate
            LEFT OUTER JOIN vw_FinStatement fs
            on m1.custID = fs.custID AND m1.FinStatementID = fs.FinStatementID
            GROUP BY 
            fs.FinstatementID,fs.[FinStatement.PeriodDATE],m1.RiskLevel,m1.custID,modID,RunDate
          )
          ,T3
          AS
          ( --MostRecentScoredDate
            SELECT c.custID,MAX(RunDATE) AS MAXDate
            FROM modResults mr WITH (NOLOCK)
            RIGHT OUTER JOIN vw_cust C
            ON mr.custID = c.custID
            GROUP BY c.custID
          )
          ,T4
          AS
          (  --MostRecentNONReviewedScore
            SELECT fs.FinstatementID,CASE WHEN YEAR(fs.[FinStatement.PeriodDATE]) = 1776 THEN NULL ELSE fs.[FinStatement.PeriodDATE] END AS PeriodDate,
            m1.RiskLevel,m1.custID,modID,RunDate,MIN(Score) AS MinScore
            FROM modResults m1 WITH (NOLOCK)
            INNER JOIN (SELECT t.custID,Max(RunDate)AS MaxDate FROM modResults t JOIN vw_modresultsnoreview r ON r.modresultsnoreviewid = t.modResultID GROUP BY t.custID) m2
            ON m1.custID = m2.custID AND m1.RunDate = m2.MaxDate
            LEFT OUTER JOIN vw_FinStatement fs
            on m1.custID = fs.custID AND m1.FinStatementID = fs.FinStatementID
            GROUP BY 
            fs.FinstatementID,fs.[FinStatement.PeriodDATE],m1.RiskLevel,m1.custID,modID,RunDate
          
          )
          SELECT 
            t3.custID,
            t1.MinScore AS Max_Run_Date_Score,
            t2.MinScore AS Has_Review_Score,
            m.[mod.mod] AS mod_Name,
            t3.MaxDate AS Max_Run_Date,
            t4.PeriodDate AS Curr_FinState_Date,
            t2.PeriodDate AS FinState_Date_Last_Reviewed,
            t4.RiskLevel AS Curr_Risk_Level,
            t2.RiskLevel AS Risk_Level_Last_Reviewed
          FROM T1 t1
          LEFT OUTER JOIN T2 t2
            ON t1.custID = t2.custID AND t1.modID = t2.modID
          RIGHT OUTER JOIN T3 t3
            ON t1.custID = t3.custID
          LEFT OUTER JOIN vw_mod m
            ON t1.modID = m.modID
          LEFT OUTER JOIN T4 t4
            ON t1.custID = t4.custID AND t1.modID = t4.modID
            
       ;
      

       

       

      The top statement runs and returns results in the report with no problem.  The bottom statement runs with no error, loads records with no error, and will display results on my desktop even if I open the source file that the server is using.  Also, it will open in the app, but with zero records.  I have searched the logs, any log I could find, till I was blue and found nothing.  Also when I open the document on the Server's desktop; which is version 9; all I get is a "failed to open document" close box with no further explaination as to why.  Because we are on a deadline, I switched the load statement back to a more friendly query based code as the first above.  My SA simply says its a compatibility issue, but I don't buy it because its the same file with only the load statement (and associated changes in the gui) is different.  Does anyone know if the second statement is not compatible in ver 9?  If so, what is the problem?  If not, have you ever seen this type of behavior and what did you do?

       

      Any help would be greatly appreciated as I am getting nowhere searching the community or documentation.  I had to look for an hour and a half just to find the logs.

       

      btw; both queries run just fine on SQL Server Management Studio on both the server and a local copy of the db on my local test server.

      Also, the user account that accesses the SQL db is the same on both and has no trouble opening any other document on this server on this db.