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,
WHERE eh.Path like '%\%' and eh.Name like 'F4401W000%'
order by generatorcode
OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERROR)
pls Look at the output here
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?
Could you check the value for AF Attribute 'outpower' in PSE .Is this attribute an output for any of your analysis ?
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?
1 of 1 people found this helpful
Seems like you might need "OUTER APPLY' instead of 'CROSS APPLY'.....
Many thanks Sayan. It did work for me.