SQL Full Outer Join for Query List (Geo SCADA)

[Anonymous user]

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!

[AndrewScott]

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... 

[BevanWeiss]

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