4 Replies Latest reply on May 24, 2017 1:46 AM by sweta.agarwal

    PI SQL Commander Query Help

    sweta.agarwal

      SELECT eh.Path + eh.Name Element, tir.[Time], tir.[Outside Air Temp],tir.[Return Air Temp],tir.[Mixed Air Temp],tir.[Supply Air Temp Sp],tir.[ACE Rule 1]

      FROM [ACE].[Asset].[ElementTemplate] et

      INNER JOIN [ACE].[Asset].[Element] e

           ON et.ID = e.ElementTemplateID

      INNER JOIN [ACE].[Asset].[ElementHierarchy] eh

           ON e.ID = eh.ElementID

      CROSS APPLY [ACE].[DataT].[TransposeInterpolateRange_ICS: OFC AHU]

      (

           eh.ElementID,

           DATE(N'2017-04-15'),    /*StartTime*/

           DATE(N't'),        /*EndTime*/

           N'1h'            /*TimeStep*/

      ) tir

      WHERE et.Name = N'ICS: OFC AHU'

      OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

       

      I am trying to use AND WHERE eh.Path like '%zyz%'

       

      However it is giving me this error -> Extraneous '.', expecting <IDENTIFIER>

       

      Can someone help me in customizing this query?

       

       

        • Re: PI SQL Commander Query Help
          messingerj

          Hi Sweta,

           

          So simply adding the AND clause should be sufficient for you:

           

          SELECT eh.Path + eh.Name Element, tir.[Time], tir.[Outside Air Temp],tir.[Return Air Temp],tir.[Mixed Air Temp],tir.[Supply Air Temp Sp],tir.[ACE Rule 1]
          FROM [ACE].[Asset].[ElementTemplate] et
          INNER JOIN [ACE].[Asset].[Element] e
                ON et.ID = e.ElementTemplateID
          INNER JOIN [ACE].[Asset].[ElementHierarchy] eh
                ON e.ID = eh.ElementID
          CROSS APPLY [ACE].[DataT].[TransposeInterpolateRange_ICS: OFC AHU]
          (
                eh.ElementID,
                DATE(N'2017-04-15'),    /*StartTime*/
                DATE(N't'),        /*EndTime*/
                N'1h'            /*TimeStep*/
          ) tir
          WHERE et.Name = N'ICS: OFC AHU'
          AND eh.Path like N'%zyz%'
          OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
          

           

          I ran this against my sample NuGreen database and received the expected results, constrained to a specific branch in my element hierarchy. This is the modified version I tested:

           

          SELECT eh.Path + eh.Name Element, tir.[Time], tir.[Fuel Gas Flow],tir.[Water Flow]
           FROM [NuGreen].[Asset].[ElementTemplate] et
           INNER JOIN [NuGreen].[Asset].[Element] e
                ON et.ID = e.ElementTemplateID
           INNER JOIN [NuGreen].[Asset].[ElementHierarchy] eh
                ON e.ID = eh.ElementID
           CROSS APPLY [NuGreen].[DataT].[TransposeInterpolateRange_Boiler]
           (
                eh.ElementID,
                DATE(N'2017-04-15'),    /*StartTime*/
                DATE(N't'),        /*EndTime*/
                N'1h'            /*TimeStep*/
           ) tir
           WHERE et.Name = N'Boiler'
           AND eh.Path LIKE N'%Houston%'
          OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
          

           

          When you received the error you quoted, did the output also specify a position or line number within the query?

           

          John

          3 of 3 people found this helpful
          • Re: PI SQL Commander Query Help
            tmcmanus

            Hi Sweta,

             

            As John mentioned, you can include multiple operators (eg. AND, OR) within the same WHERE statement. Here's some documentation on SQL syntax: SQL AND, OR, NOT Operators