08-03-2011 06:21 AM
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>
Todd Viau
Aviva LLC
St. Louis MO
08-03-2011 07:40 AM
Hi Todd,
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?
08-03-2011 10:58 AM
Hi Vivek,
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.
Todd Viau
Aviva LLC
St. Louis MO
03-01-2013 02:42 PM
03-19-2013 07:28 AM
Do you have a very large Histories tbl? If so you could be getting timeouts via the Dashboard.