2 Replies Latest reply on Jun 10, 2014 3:24 PM by jjzimprich

    SWQL query fails on website, but not in SWQL Studio

    jjzimprich

      I have a pretty simple SWQL query that runs fine in SWQL studio, but for some reason it fails when I try to put it inside a "Custom SWQL Query" resource on the web interface (on an Application Details page).

       

      Here's the query I'm using:


      SELECT

      'Notes' as PROPERTY, c.Notes as VALUE

      FROM Orion.APM.ApplicationCustomProperties c

      WHERE c.ApplicationId = ${ApplicationId}

      UNION

      (

      SELECT

      'WikiURL' as PROPERTY, c.Wiki as VALUE

      FROM Orion.APM.ApplicationCustomProperties c

      WHERE c.ApplicationId = ${ApplicationId}

      )


      *Note: I replace the "${ApplicationId}" variable with an actual ApplicationId when testing in SWQL studio, but it also fails on the web interface if I replace the "${ApplicationId}" variable with a static integer.


      Individually, each SELECT statement works fine in the Custom SWQL Query resource, but not with the UNION statement. I think that syntax is correct, though?


      Any tips/ideas?

        • Re: SWQL query fails on website, but not in SWQL Studio
          tdanner

          Here's what's going on: the Custom Query resource is seeing adding an ORDER BY clause to your query to support sorting and paging. But it is doing it in a dumb way: it's adding "ORDER BY 'Notes'". But you are not allowed to order by a constant when there is a UNION in the query. To work around this problem, add your own ORDER BY clause, like this:

           

          SELECT
          'Notes' as PROPERTY, c.Notes as VALUE
          FROM Orion.APM.ApplicationCustomProperties c
          WHERE c.ApplicationId = ${ApplicationId}
          UNION
          
          (
          SELECT
          'WikiURL' as PROPERTY, c.Wiki as VALUE
          FROM Orion.APM.ApplicationCustomProperties c
          WHERE c.ApplicationId = ${ApplicationId}
          
          )
          ORDER BY PROPERTY
          
          
          1 of 1 people found this helpful