Making this SQL code Compatible with QlikView

[#1]

How do I make this compatible with QlikView?

USE MPL

  SELECT ITMSNumber,Date_Chg,DATEDIFF(dd,Date_Chg,GETDATE()) AS tDays

  FROM tblSTRATEGY Strgy

  inner join

   ( SELECT ChangeKeyValue AS ITMS_Chg,MAX(ChangeDate) AS Date_Chg

  FROM tblCHANGE Chg

  WHERE ChangeFieldName='DATALOADSTATUSID'

  AND ChangeNewValue='Quarantined - DSC'

  GROUP BY ChangeKeyValue

  ) Chg1 ON Chg1.ITMS_Chg=Strgy.ITMSNumber

  WHERE Strgy.DATALOADSTATUSID=30

  ORDER BY TDAYS

I need to be able to load ITMSNumber and tDays to make this work right-

Right now these numbers above should add up to 41.

[jonathandienst]

How is that code incompatible? Drop the USE statement and append SQL in front. The statement is passed to the SQL server and executed by that server, not by QV. QV can then consume the  data.

Your OLEDB or ODBC connection will point you to the MPL database/catalog, that's why would not normally need the USE statement.

CONNECT .....

[Data]:

SQL

SELECT ITMSNumber,Date_Chg,DATEDIFF(dd,Date_Chg,GETDATE()) AS tDays

  FROM tblSTRATEGY Strgy

  inner join

   ( SELECT ChangeKeyValue AS ITMS_Chg,MAX(ChangeDate) AS Date_Chg

  FROM tblCHANGE Chg

  WHERE ChangeFieldName='DATALOADSTATUSID'

  AND ChangeNewValue='Quarantined - DSC'

  GROUP BY ChangeKeyValue

  ) Chg1 ON Chg1.ITMS_Chg=Strgy.ITMSNumber

  WHERE Strgy.DATALOADSTATUSID=30

  ORDER BY TDAYS;

...

[#3]

Thank you!

[#4]

It still doesn't add up to 41 though. With this code it only adds up to 38...

[#5 · Accepted solution]

[Data]:

SQL

  SELECT ITMSNumber,Date_Chg,DATEDIFF(dd,Date_Chg,GETDATE()) AS tDays

  FROM tblSTRATEGY Strgy

  inner join

   ( SELECT ChangeKeyValue AS ITMS_Chg,MAX(ChangeDate) AS Date_Chg

  FROM tblCHANGE Chg

  WHERE ChangeFieldName='DATALOADSTATUSID'

  AND ChangeNewValue='Quarantined - DSC'

  GROUP BY ChangeKeyValue

  ) Chg1 ON Chg1.ITMS_Chg=Strgy.ITMSNumber

  WHERE Strgy.DATALOADSTATUSID=30

  ORDER BY TDAYS;