cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Using Common Table Expressions (CTE) in SQL Reports

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;

Labels (1)
0 Kudos
12 Replies
MVP
MVP

Put a semicolon before the 'WITH' statement. I don't know why this works, but it does.

I thought I was going mad!  Thanks for this!

0 Kudos

Odd, but that worked!   Thanks so much!  Is that a bug w/ the SQL parser?

0 Kudos

You are most welcome.

0 Kudos

No, it is not a bug.  When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.  Seeing as SolarWinds is running the statement, we can not be sure that there isn't something putting in before the statement/s that we have entered.  A lot of people always put a semicolon right before the 'WITH' to avoid this issue.

Makes sense.  THough I would expect the semicolon should be after the previous statement in the batch, and not be required at the beginning of a script.  In either case, it works now, so I'll update my documentation and move on with life.  Thanks again!

0 Kudos

I remember a while ago where i had attempted to use a CTE and couldnt get it to pass the validation checker.  I was able to rework the query to get what i needed without a CTE and that worked.  Had to be way more elaborate and tricky with the sql but it got done.

- Marc Netterfield, Github
0 Kudos
Level 9

Thanks.  I'll try the 3 part table name.  So it sounds like you can successfully use CTE in your queries then?

0 Kudos

I'm guessing the 3 part name won't help but it doesn't hurt to try.

I do not have permissions to use SQL in Reports (which is very frustrating) so I can't try it out to let you know if they work or not.  Sorry.

0 Kudos
Level 9

Probably because I edited it to remove sensitive information when I pasted.  Looks like I removed the quote

It does run fine in mine unmolested. .

0 Kudos

A quote is missing and a right paren.   It would help if you run your queries that you are pasting to make sure they run.  It just makes things easier for anyone trying to help you.

You could try changing to use three part table names (<database name>.<schema name>.<table name>) in your script and see if that helps when running in the report.

0 Kudos
Level 10

I copied and pasted your code into SSMS and ran syntax check on it and it gave me errors. 

0 Kudos