[Imported] SELECT query FROM sub-SELECT query

[sbeadle]

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

**_Any ideas?_**

**_Cheers_**

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

This is in the Core Reference Coding SQL Guide

Steve