night2day

intersect workaround

Discussion created by night2day on Oct 15, 2010
Latest reply on Oct 18, 2010 by night2day

Hello,

 

because there is no intersect statement (union already is) in the PI-OLEDB-SQL i have to find a workaround.

 

The problem is, i have to find a set of timestamps for manual entrys with defined values in different tagnames.

 

Until now i have successful used something like

SQL = "select TIME " & _
    "from PICOMP " & _
    "where TAG = 'TAG1'" & _
    "and SVALUE like '" & strTag1 & "' " & _
    "and TIME in (select TIME " & _
        "from PICOMP " & _
        "where TAG = ''TAG2'" & _
        "and SVALUE like '" & strTag2 & "' " & _
        "and TIME in (select TIME " & _
            "from PICOMP " & _
            "where TAG = 'Tag3' " & _
            "and SVALUE like '" & strTag3 & "' " & _
            ...
            "and TIME between '" & strVon & "' and '" & strBis & "')) " & _
    "order by TIME"

But now i have to add a new case with a new created Tag.  The problem is that the query naturally can't find anything at the time before the new tag was created also with value like '*' defined.

 

Usually I would handle such querys with something like that.

 

SQL = "select TIME from PICOMP where TAG = 'TAG1' and SVALUE like '" & strTag1 & "' " 

If strTag2 <> "*" Then
    SQL= SQL & "INTERSECT select TIME from PICOMP where TAG = 'TAG2' and SVALUE like '" & strTag2 & "' " 
End If


  ...

 

and so on.

 

But intersect is not supported.

 

Do you have any suggestions how you would handle this?

 

Greetings
Wolfgang

Outcomes