6 Replies Latest reply on Jan 15, 2016 6:07 PM by Rashmi

    PI Point base table




      In Pi AF, for a particular attribute, I've defined Data Reference=Pi Point and some other the values have been hard coded.


      Just want to know is there any pre-defined system table available for Pi Point so that it would help me in my SQl query for dat extraction?



        • Re: PI Point base table

          Hi Rashmi,

          You have tables shown in the picture below each having different purpose.


          • Re: PI Point base table

            Hi Akash, Looks like I'm not given access to the above tables. 


            However, I used the below query and gives me the output of generatorcode 'F4401W0001H0060'. For this gen code we have tag available for 'outpower' attribute and the value of 'height is taking from 'Snapshot' template.


            SELECT substr(eh.Name,1,5) farmcode,eh.Name generatorcode,tc1.height,

            if tc.speed is null then 'NaN' else cast (tc.speed as variant) as Speed,

            if tc.direction is null then 'NaN' else cast (tc.direction as variant) as direction,

            if tc.temperature is null then 'NaN' else cast (tc.temperature as variant) as temperature,

            if tc.outpower is null then 'NaN' else cast (tc.outpower  as variant) as outpower,

            if tc.state is null then 'NaN' else cast (tc.state as variant) as state

            FROM (SELECT Date('15/01/2016') Time) t, [HYREF].[Asset].[ElementHierarchy] eh

            CROSS APPLY [HYREF].[DataT].[TransposeSnapshot_WindGeneratorTemplate](eh.ElementID) tc1

                CROSS APPLY [HYREF].[DataT].[TransposeArchive_WindGeneratorTemplate](eh.ElementID,

                t.Time,t.Time) tc

                WHERE eh.Path like '%\%' and eh.Name like 'F4401W000%'

            order by generatorcode


            pls Look at the output here

            SQL result.bmp


            From  generator code F4401W0002H0060 to F4401W0052H0060, we have no PI tag available at this moment for attributes (Speed,direction,temprerature,outpower, state). Hence, I should see NaN for these columns. Outpower also should display me as NaN for F4401W0002H0060 to F4401W0052H0060.


            I don't see NaN value for generator code F4401W0002H0060 and henceforth.  height is always constant value for all generator codes. I'm unsure what's wrong in my query and why the NaN values are not displaying for other generator code's attribute values. Please could you advise?

              • Re: PI Point base table

                Hi Rashmi,

                Could you check the value for AF Attribute 'outpower' in PSE .Is this attribute an output for any of your analysis ?

                  • Re: PI Point base table

                    for attribute 'Outpower', I defined data reference=Pi Point  on AF since we have created Tag for this only for generator code F4401W0001H0060. Hence, value displaying. Since no tag avilable for other genertaor codes, it should show me NaN for all other attributes.  my query is working only for first generator code.


                    I also added this line

                    INNER JOIN [HYREF].[Asset].[

                    ft_GetPIPoint] gp ON gp.ElementAttributeID = ea.ID, but not working.


                    Is there any way to add in where condition like data reference !=Pi Point for 2nd generator code (for attributes Speed,Direction, temperature, outpower, state) so that it would display me NaN for 2nd generator code onwards?


                    Regards, Rashmi

                  • Re: PI Point base table

                    Seems like you might need "OUTER APPLY' instead of 'CROSS APPLY'.....

                    1 of 1 people found this helpful