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
//most recent mResult date
select custID, Max(RunDate) as Max_Run_Date from mResults
group by custID
//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
//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
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
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.