11 Replies Latest reply on Feb 22, 2017 10:45 PM by Steven Klassen

    Using Common Table Expressions (CTE) in SQL Reports

    jdchaiken

      Is there a way to use Common Table expressions in my custom SQL reports?  It does not appear so, but maybe I'm missing something.  I'm trying something like this, which works in SQL management studio, but not when adding the query to the report.

       

      WITH    Counts ( C )
                AS ( SELECT   COUNT(*) C
                     FROM     ( ( ( Nodes
                                    INNER JOIN Interfaces ON ( Nodes.NodeID = Interfaces.NodeID )
                                  )
                                  INNER JOIN CustomPollerAssignment ON ( Nodes.NodeID = CustomPollerAssignment.NodeID
                                                                    AND Interfaces.InterfaceID = CustomPollerAssignment.InterfaceID
                                                                    )
                                )
                                INNER JOIN CustomPollers ON ( CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID )
                              )
                              INNER JOIN CustomPollerStatus ON ( CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID )
                     WHERE    ( ( ( CustomPollers.UniqueName = 'cpaePortCapabilitiesEnabled'
                                    AND CustomPollerStatus.Status LIKE '0%'
                                  )
                                  OR ( CustomPollers.UniqueName = 'hpicfUsrAuthMacAuthAdminStatus'
                                       AND CustomPollerStatus.Status = '2'
                                     )
                                  OR ( CustomPollers.UniqueName = 'jnxPaeAuthConfigMacAuthStatus'
                                       AND CustomPollerAssignment.Status = 0
                                       AND InterfaceType = 53
                                     )
                                )
                                AND ( Nodes.Location = SWITCH' )
                                AND NOT ( Interfaces.Caption LIKE '%Router%'
                                          OR Interfaces.Caption LIKE '%Switch%'
                                          OR Interfaces.Caption LIKE '%Uplink%'
                                          OR Interfaces.Caption LIKE '%Exception%'
                                        )
                                AND InterfaceType IN ( 6 )
                                AND 1 = 1
                              )
                     UNION
                     SELECT   COUNT(*) C
                     FROM     ( ( ( Nodes
                                    INNER JOIN Interfaces ON ( Nodes.NodeID = Interfaces.NodeID )
                                  )
                                  INNER JOIN CustomPollerAssignment ON ( Nodes.NodeID = CustomPollerAssignment.NodeID
                                                                    AND Interfaces.InterfaceID = CustomPollerAssignment.InterfaceID
                                                                    )
                                )
                                INNER JOIN CustomPollers ON ( CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID )
                              )
                     WHERE    ( ( (CustomPollers.UniqueName = 'jnxPaeAuthConfigMacAuthStatus'
                                  AND CustomPollerAssignment.Status = 0
                                  AND InterfaceType = 53
              
                                )
                                AND ( Nodes.Location = 'SWITCH' )
                                AND NOT ( Interfaces.Caption LIKE '%Router%'
                                          OR Interfaces.Caption LIKE '%Switch%'
                                          OR Interfaces.Caption LIKE '%Uplink%'
                                          OR Interfaces.Caption LIKE '%Exception%'
                                        )
                              )
                   )
          SELECT  SUM(C) Num_Disabled_ports
          FROM    Counts;