>>Message imported from previous forum - Category:ClearSCADA Software<< User: florian, originally posted: 2018-10-25 17:04:06 Id:249 This is a re-posting from the obsoleted (October 2018) "Schneider Electric Telemetry & SCADA" forum.
**_nminchin:_** **_It's been a while since I've used ClearSCADA..._**
**_I'm trying to get a list of DNP3 AIs and BIs and their PointNumbers, so I've got the query:_**
_SELECT * FROM (SELECT ID, ID, FullName, PointNumber FROM CDNP3AnalogIn UNION SELECT ID, ID, FullName, PointNumber FROM CDNP3BinaryIn)_ _tmp_ _WHERE FullName LIKE 'Sites.%'_
**_however getting an error at the UNION area saying i'm missing a ')' at the first index.... I recall having to format this strangely years ago, but can't remember._**
**_Edit: putting the where clause inside both sub queries and removing the SELECT * FROM works, but if I had a more complex query, how would I do this?_**
sbeadle: The UNION keyword can be used in two ways:
1. To be the 'innermost' operation, 'merging tables', e.g.
_SELECT_ _ID, ID, FULLNAME, POINTNUMBER_ _FROM_ _CDNP3ANALOGIN UNION CDNP3ANALOGIN_ _WHERE_ _FULLNAME LIKE '%'_
For this to work the columns must all have the same names, which does work in your case.
2. to be the 'outermost' operation of two or more complete queries, with only an ORDER BY, but without any WHERE clauses:
_( SELECT ID, ID, FULLNAME, POINTNUMBER FROM CDNP3ANALOGIN WHERE FULLNAME LIKE '%' )_ _UNION_ _( SELECT ID, ID, FULLNAME, POINTNUMBER FROM CDNP3ANALOGIN WHERE FULLNAME LIKE '%' )_ _ORDER BY_ _"FullName" ASC_
Search help for SQL UNION and you can see the syntax.