AnsweredAssumed Answered

Why am i getting this error : No suitable function overload for '='

Question asked by GregMcFarlane on Jan 16, 2020
Latest reply on Jan 17, 2020 by GregMcFarlane

What I am doing is looking for elements that have  name like 'Engine%'  ex:  'Engine 1', 'Engine 2' , etc

I also want to filter down to the specific piece of equipment defined by the Attribute EquipId.   ex:  '10020'

 

These are big pieces of equipment w multiple engines. I am interested in all engines on a single piece of equipment.

 

I realized part of the issue was that I was trying to compare to an attribute value. I guess PI has no way of knowing which value out of the time series to use.

 

Studying things in the compendium, my next idea was to cross apply and get a SampleValue.  The EQUIPID is an attribute whose value does not change, so it doesnt matter if i get the first one or last one.I can make this work and it does show me the EQUIPID value  (1220).  This works for me.

 

Even after doing this , I can not compare that single value to a constant. I am getting the error  No suitable function overload for '=' could be found.

 

This doesnt really make a lot of sense to me. At this point, it should just be a plain column value. 

 

My final attempt was to wrap all that work in a view. My thinking is that would convert the data to a simple data table and I could then make the comparison. Even this does not work. 

 

 

I am calling this code from a SQL Server Linked Server using PI SQL Client. 

I can execute the code w/o the EQUIPID filter, but I get back everything. 

I am able to filter once SQL server reads the open query into the table.  

This would be fine until we start adding lots of equipment. 

 

My QUESTION TODAY  -   WHAT DOES THIS MESSAGE MEAN.  (  No suitable function overload for '=' could be found.)  IS THERE ANY WAY TO GET AROUND THAT ON THE PI SIDE OF THINGS SO I CAN PULL BACK JUST WHAT I NEED TO THE REMOTE SERVER.

 

 

 

Here is my most recent attempt.

 

 Create View Master.Element.GregTriesAgain
 as
 
SELECT   data.*
    
FROM
(
 
    SELECT e.ID, e.Name, e.Template, e.PrimaryPath
        , ea.Name [AttrName], ea.Value [AttrValue]
        , GetEquipID.Value 
       FROM [Master].[Element].[Element] e
       join  Master.Element.Attribute ea on e.ID  = ea.ElementId 
                and ea.Name = 'EquipId'
    CROSS APPLY Master.Element.GetSampledValue
    (
             ea.ID
             , '9/1/2019' /*@StartTime*/
    ) GetEquipID
    WHERE  1=1 
    and e.Template = N'Engines'
    and e.Name like N'Engine%'

) data
 where  1=1
     and data.Name like 'Engine%'   -- works. really not necessary
    and data.AttrName = 'EquipId'  --works, really not necessary
    -- and e.AttrValue = '1220'   -- No suitable function overload for '=' could be found.
    -- and e.Value_String = '1220' -- No suitable function overload for '=' could be found.

 

/*
- Even from a view, i get the error....  No suitable function overload for '=' could be found.
select * from Master.Element.GregTriesAgain where Value =  '1220'  

*/

 

 

 

Given Gregor's answer, I wanted to post the working version of my query 

 

I removed the outer subquery

I removed the Cross Apply

Most importantly,  I changed  ea.Value = '1220'  --> ea.value_string  = '1220'

 

SELECT e.ID, e.Name, e.Template, e.PrimaryPath
        , ea.Name [AttrName], ea.Value [EquipId] 
        
    FROM [Master].[Element].[Element] e
        join  Master.Element.Attribute ea on e.ID  = ea.ElementId 
                and ea.Name = 'EquipId'
                and ea.Value_string = '1220'    --<---- !!!!! This was the real problem   !!!!
 where  1=1
    and e.Template = N'Engines'
    and e.Name like N'Engine%'

 

 

 

I am now back to a place where I am writing exactly the query I want to write and getting exactly the data I need. Very nice. 

Outcomes