3 Replies Latest reply on Aug 29, 2013 3:59 PM by cpan

    Query is not working in PI Data Services

    Srinivas

      Hi ,

       

      I have a query which in the PI SQL commander which pulls the events information from AF database. The same query If I used in the PI data services to  create a new dataset. the following error message being displayed when clicked on the Verify SQL.. I am  using the CROSS APPLY on the transpose function of event frame template.

       

      syntax error near CROSS..

       

      PI Webparts version is 2010 R2 and PI SQL commander is 2012 version...

       

      Request your help to resolve this issue.

        • Re: Query is not working in PI Data Services
          Dan Fishman

          Srinivas,

           

          Can you post your SQL query?  Are you using substitution parameters?  What is the version of PI OLEDB Enterprise on the machine where you are running PI SQL commander and what is the version on the machine where webparts is installed.

           

          Dan

            • Re: Query is not working in PI Data Services
              Srinivas

              Dan,

               

              PI OLEDBEnterpise 2012 (version # 1.3.1.5)

               

              PI webparts 2010 R2  (version# 3.1.1.1)

               

              PI data services (version#  3.1.0.101)

               

              It is a simple query.. looks like CROSS is not being rocognized and.. tried using the funtion table created using transpose funciton.. there is throwing exception saying catlog name is invalid... however, both queres running in sql commander.

               

              Here is the sample query... removed the database/catlog name and replaced it with XXX..

               

              SELECT ef.Name EventFrame, tc.*

               

              FROM

               

              (

               

                   SELECT TOP 100 *

               

                   FROM [XXX].[EventFrame].[EventFrame]

               

                   WHERE EventFrameTemplateID IN

               

                   (

               

                        SELECT ID

               

                        FROM [XXX].[EventFrame].[EventFrameTemplate]

               

                        WHERE Name = 'XXX_EventFrameTemplate'

               

                   )

               

              ) ef

               

              CROSS APPLY [XXX].[DataT].[TransposeEventFrameArchive_XXX_EventFrameTemplate](ef.ID, ef.StartTime, ef.EndTime) tc

               

              OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

               

              regards,

               

              Srinibvas

                • Re: Query is not working in PI Data Services
                  cpan

                  Srinivas

                   

                  I was able to take your query and run it against my AF machine just fine.  Here is what I have.

                   

                  SELECT ef.Name EventFrame, tc.*

                   

                  FROM

                   

                  (

                   

                  SELECT TOP 100 *

                   

                  FROM [PIEFGenSim].[EventFrame].[EventFrame]

                   

                  WHERE EventFrameTemplateID IN

                   

                  (

                   

                  SELECT ID

                   

                  FROM [PIEFGenSim].[EventFrame].[EventFrameTemplate]

                   

                  WHERE Name = 'EFSimOperation'

                   

                  )

                   

                  ) ef

                   

                  CROSS APPLY [PIEFGenSim].[DataT].[TransposeEventFrameArchive_EFSimOperation](ef.ID, ef.StartTime, ef.EndTime) tc

                   

                  OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)

                   

                  I was able to create a Dataset from it in the PI DS dataset page.  The error "Syntax error near cross"  can pop up if the Data Source is not defined correctly in the Data Source field so that is one thing to check.  Make sure that a Correct Relational Data source is defined and that you are able to connect to it and also that your are choosing the right data source when creating the dataset.  

                   

                  If you are still having trouble getting this to work.  It would be a good idea to call into support for additional help.

                   

                  Best,

                   

                  Cheng Pan