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
84248members
353348posts

[Imported] SELECT query FROM sub-SELECT query

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.

sbeadle
Janeway Janeway
Janeway
0 Likes
0
255

[Imported] SELECT query FROM sub-SELECT query

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