Can someone help me with some SQL code? I cannot figure out why this won't work. I'm trying to get the last history for every contact in the database. I can use this code in Access connect via ODBC and it works just fine. I can also run each select statement by itself in the Dashboard and it works, but when I try to join them together I get a blank dashboard. Any ideas? Thanks.
<item> <key> <string>Last History</string> </key> <value> <string> SELECT LH.END_DATE_TIME, LH.REGARDING, LH.CONTACTID FROM (SELECT CLH.CONTACTID, HLH.END_DATE_TIME, HLH.REGARDING FROM (dbo.CONTACT_HISTORY AS CHLH INNER JOIN dbo.CONTACT AS CLH ON CHLH.CONTACTID = CLH.CONTACTID) INNER JOIN dbo.HISTORY AS HLH ON CHLH.HISTORYID = HLH.HISTORYID) AS LH INNER JOIN (SELECT DISTINCT CLHD.CONTACTID, Max(HLHD.END_DATE_TIME) AS MaxOfENDTIME FROM (dbo.CONTACT_HISTORY AS CHLHD INNER JOIN dbo.CONTACT AS CLHD ON CHLHD.CONTACTID = CLHD.CONTACTID) INNER JOIN dbo.HISTORY AS HLHD ON CHLHD.HISTORYID = HLHD.HISTORYID GROUP BY CLHD.CONTACTID) AS LHD ON (LH.CONTACTID = LHD.CONTACTID) AND (LH.END_DATE_TIME = LHD.MaxOfENDTIME) ;0;TRUE </string> </value> </item> <item>
I'm thinking the issue could be that you are using a sub-select (nested select) statement and since you are working off the SQL Views via the dashboard, you can not create temp tables within the SQL schema since the OLEDB provider is a read only login.
Have you tried the wholoe select statement via Studio Manager?
Thank you for the response. I just tried it in Studio Manager and it worked there too. I have used nested statements in other dashboards without a problem. Not sure why this one's not working.
