Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84646members
353985posts

SQL Full Outer Join for Query List (Geo SCADA)

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Cbhasin
Ensign
Ensign
0 Likes
2
809

SQL Full Outer Join for Query List (Geo SCADA)

Hello Everyone!

 

I would really appreciate some guidance using FULL OUTER JOIN in a Query List. I am trying to query data from 2 tables in Geo SCADA with different columns (and a common NoteNumber column that has unique values in both of the tables). I get this error - "Right Or Full Outer Joins are not supported". Is it not possible to use Full Outer Join in Geo SCADA? I need to do the following:

 

SELECT

     COALESCE (E.NOTENUMBER, W.NOTENUMBER) AS "Note Number", E.EQUIPMENTID, W.WORKORDERID

FROM 

     EQUIPMENTNOTES AS E

FULL OUTER JOIN 

     WORKORDERNOTES AS W

ON

     E.NOTENUMBER = W.NOTENUMBER

WHERE

     E.NOTENUMBER IS NULL OR W.NOTENUMBER IS NULL

 

Thanks!

2 Replies 2
AndrewScott
Commander
Commander
0 Likes
0
805

Re: SQL Full Outer Join for Query List (Geo SCADA)

As the error message says, the Geo SCADA Expert query processor doesn't support full outer joins.

 

The supported join types are documented in the online help:

https://tprojects.schneider-electric.com/GeoSCADAHelp/Geo%20SCADA%202019/Default.htm#SQLGuide/Refere... 


Andrew Scott, Lead Engineer, AVEVA
BevanWeiss
Spock
Spock
0 Likes
0
772

Re: SQL Full Outer Join for Query List (Geo SCADA)

I'm not really sure what you're looking for here either....

 

I don't believe this is valid:

ON

     E.NOTENUMBER = W.NOTENUMBER

WHERE

     E.NOTENUMBER IS NULL OR W.NOTENUMBER IS NULL

 

In SQL Rules NULL does not match with NULL.

So...

You want to filter records so that you ONLY include records where either E.NOTENUMBER is NULL OR W.NOTENUMBER is NULL.

But then you're trying to do an outer join where you're looking for NULL = (anything) or (anything) = NULL, which is guaranteed to fail.

 

You have the COALESCE, so you are expecting that E.NOTENUMBER may be NULL.

What are you actually trying to obtain?

 

Maybe breaking it into parts...

So... you want all entries from the EQUIPMENTNOTES table:
SELECT E.NOTENUMBER as "Note Number", E.EQUIPMENTID, W.WORKORDERID

FROM EQUIPMENTNOTES AS E

LEFT JOIN WORKORDERNOTES AS W

ON E.NOTENUMBER= W.NOTENUMBER

 

and then all the WORKORDER entries that we didn't match before

UNION

SELECT WO.ORDERNOTES as "Note Number", NULL, W.WORKORDERID

FROM WORKORDERNOTES AS WO

LEFT JOIN EQUIPMENTNOTES AS EN

ON WO.NOTENUMBER=EN.NOTENUMBER

WHERE EN.NOTENUMBER IS NULL


Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..