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
84262members
353369posts

Relative Object Parameter linking in SQL Exports

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.

Solved
EthanWortho
Crewman
Crewman
0 Likes
3
440

Relative Object Parameter linking in SQL Exports

Hello,

 

I'm trying to create some basic SQL exports so that I can export data on a schedule. 

The data i'm wanting to export are part of templates and I was wondering is it possible 

to use relative linking inside the SQL queries so that the export can be templated so when a new instance is 

created the export+schedule is already configured.

 

Something along the lines of  Select * from CDBHistory where "Id"  = "..Pump1.Running.ID" and RELATIVE TIME BETWEEN....etc .

 

Is it also Possible to use a relative pathname in the SQL Export filename so that the export file name 

will be unique for every instance created?

 


Accepted Solutions
sbeadle
Janeway Janeway
Janeway

Re: Relative Object Parameter linking in SQL Exports

In a template, edit property overrides.

Then choose Select and add the SQLQuery property of the SQl Export object in the template.

Find that in the list of properties and change the Override Type from 'In Instance' to 'By Expression'.

Then in the Expression field you can type your SQL query as a string (wrap with single quotes) and use the variable FullName in the filter. Use double single quotes to escape a single quote in a string.

e.g.

 

'SELECT TOP( 100000 ) "RecordId", "SeverityDesc" AS "~SeverityDesc", "RecordTime" AS "~RecordTime", "Source", "Message", "User", "Category", "Foreground", "SeqNo", "CommentNo" FROM CDBEVENTJOURNAL WHERE ( "~RecordTime" BETWEEN { OPC ''H-23H'' } AND { OPC ''H+1H'' } ) AND ( "Source" LIKE ''' + FullName + '.%'' OR "Source" = ''' + FullName + ''' ) AND ( "Deleted" = FALSE ) ORDER BY "~RecordTime" DESC, "SeqNo" DESC, "CommentNo" ASC'

The above query gets the events for the instance of the SQL Export.

 

 

See Answer In Context

3 Replies 3
BevanWeiss
Spock
Spock

Re: Relative Object Parameter linking in SQL Exports

I haven't tried this, but it may be possible to use the newish Template Parameter stuff to do it (although I can't remember the exact limitations to this, it would at least let you simplify the configuration).

 

The other way I've done similar is with a really long interval OnInterval logic routine within the template.

When it's instantiated, it gets executed, which then configures the various settings you want.

It's not super nice though, so I'd look into the Template Parameter stuff first..


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

Re: Relative Object Parameter linking in SQL Exports

In a template, edit property overrides.

Then choose Select and add the SQLQuery property of the SQl Export object in the template.

Find that in the list of properties and change the Override Type from 'In Instance' to 'By Expression'.

Then in the Expression field you can type your SQL query as a string (wrap with single quotes) and use the variable FullName in the filter. Use double single quotes to escape a single quote in a string.

e.g.

 

'SELECT TOP( 100000 ) "RecordId", "SeverityDesc" AS "~SeverityDesc", "RecordTime" AS "~RecordTime", "Source", "Message", "User", "Category", "Foreground", "SeqNo", "CommentNo" FROM CDBEVENTJOURNAL WHERE ( "~RecordTime" BETWEEN { OPC ''H-23H'' } AND { OPC ''H+1H'' } ) AND ( "Source" LIKE ''' + FullName + '.%'' OR "Source" = ''' + FullName + ''' ) AND ( "Deleted" = FALSE ) ORDER BY "~RecordTime" DESC, "SeqNo" DESC, "CommentNo" ASC'

The above query gets the events for the instance of the SQL Export.

 

 

EthanWortho
Crewman
Crewman
0 Likes
0
336

Re: Relative Object Parameter linking in SQL Exports

Thank you @sbeadle that has fixed the issue i was trying to solve