Community
Showing results for 
Search instead for 
Do you mean 
Reply

Dashboard SQL Code

Copper Contributor
Posts: 10
Country: USA

Dashboard SQL Code

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

Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: Dashboard SQL Code

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?

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog
Copper Contributor
Posts: 10
Country: USA

Re: Dashboard SQL Code

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

Copper Contributor
Posts: 136
Country: USA

Re: Dashboard SQL Code

Your code works fine.
Thanks,
Inge van Gemert
ACT! Certified Consultant
The Netherlands
inge@inkey.net
Bronze Elite Contributor
Posts: 2,115
Country: United_Kingdom

Re: Dashboard SQL Code

Do you have a very large Histories tbl? If so you could be getting timeouts via the Dashboard.

Vivek Gargav
Caldere Associates Ltd.
www.caldere.com
vgargav@caldere.com
My Blog