2 Replies Latest reply on Nov 10, 2017 8:32 AM by Tim Nice

    Business Rule - Using Math to return data from a SQL query

    Tim Nice

      I am trying to add the following SQL query to a MATH statement in a business rule to return the results by email to an end user:

       

      SELECT FULL_NAME,

      COUNT([Incident #]) as '# Incidents'

      FROM [_SMDBA_].[Incident]

      LEFT JOIN [_SMDBA_].[Support Staff]

      ON [_SMDBA_].[Incident].[SEQ.ASSIGNED TO] = [Support Staff].SEQUENCE

      WHERE [STATE:] = 'O' and [SEQ.GROUP] = 1001

      GROUP BY FULL_NAME

      ORDER BY FULL_NAME

       

      This is what it looks like when i add it as a 'Notification' in the details/body:

       

      {MATH, (SELECT FULL_NAME,

      COUNT([Incident #]) as '# Incidents'

      FROM [_SMDBA_].[Incident]

      LEFT JOIN [_SMDBA_].[Support Staff]

      ON [_SMDBA_].[Incident].[SEQ.ASSIGNED TO] = [Support Staff].SEQUENCE

      WHERE [STATE:] = 'O' and [SEQ.GROUP] = 1001

      GROUP BY FULL_NAME

      ORDER BY FULL_NAME)}

       

      It returns no results although when I run it in SQL Management Studio, it produces a table showing the number of open incidents assigned to each person in a group.

       

      When I do a simple query, without joining tables, it works.

       

      Do SDE business rules sending notifications like this work when joining tables?

       

      Thanks!