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
84546members
353802posts

Embedded List Query is reading String1 from all Parameter Tables

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.

Tanquen
Lt. Commander
Lt. Commander
0 Likes
1
605

Embedded List Query is reading String1 from all Parameter Tables

Each site is a template and has templates inside it and they all use the same Parameter Table “_Station Description” in the root of the template to hold the name of the site. A new template was added that also has a Parameter Table with a different name but these are now added to the Embedded List. Is there a way to have it ignore String1 from a certain Parameter Table? I tried adding the correct object name into the SELECT but I’m unsure how it should look. Something like CPARAMTABLE._Station Description.STRING1”

 

SELECT
CTEMPLATEINSTANCE.ID, CPARAMTABLE.STRING1 AS "~Station", CTEMPLATEINSTANCE.FULLNAME
FROM
CPARAMTABLE INNER JOIN CTEMPLATEINSTANCE ON CTEMPLATEINSTANCE.ID = CPARAMTABLE.PARENTGROUPID
WHERE
CTEMPLATEINSTANCE.FULLNAME LIKE 'North%'
ORDER BY
"~Station" ASC

 

 

I was able to add a Not Like to the template name but it seems like it would be better to have it only find the “_Station Description” Parameter Table.

 

WHERE
CTEMPLATEINSTANCE.FULLNAME LIKE 'North%' AND CTEMPLATEINSTANCE.FULLNAME NOT LIKE '%Tags%'

1 Reply 1
BevanWeiss
Spock
Spock
0 Likes
0
594

Re: Embedded List Query is reading String1 from all Parameter Tables

I'd recommend using aliases on your tables to add some self-documentation to the query.

Calling the table CTemplateInstance something else based on your use of it would generally be better, i.e. FROM CTemplateInstance as Site, and then you can call it 'Site' rather than having to type out CTemplateInstance.... all the time.

 

I'd normally try to start with the thing that is relatively static, i.e. the site/station/CTemplateInstance.  Do your initial filtering to get what you want from that.  And then you can LEFT OUTER JOIN in the things that you expect to exist, like the CParamTable entry.

 

SELECT Site.Id, SiteParamTable.String1 as "~Station", Site.FullName

FROM CTemplateInstance as "Site"

LEFT OUTER JOIN CParamTable as "SiteParamTable" ON "Site".Id="SiteParamTable".ParentGroupId

WHERE "Site".FullName LIKE 'North%'

AND "SiteParamTable".Name = 'Station Description'

ORDER BY "~Station" ASC

 

I'm going to assume that your parameter table name doesn't actually have an underscore (_) in it... because that's generally a bad idea.  Underscore is a special character in the SQL world, so is best avoided unless you want to be escaping strings all the time or getting really terrible performance and unwanted behaviour.


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