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
84556members
353822posts

Wrong result in Machine SCADA Expert SQL query.

AVEVA Plant SCADA Forum

A support forum for AVEVA Plant SCADA (formerly Citect SCADA). Share new and exciting product information, connect, learn, and collaborate with the ecosystem of Plant SCADA Users. AVEVA Plant SCADA a reliable, flexible and high-performance Supervisory Control and Data Acquisition software solution for industrial process customers. This forum is to connect, share, learn and collaborate new and exciting product information. Feel free to join and share to your Ecosystem of Plant SCADA Users.

Solved
jthyge
Ensign
Ensign
0 Likes
2
771

Wrong result in Machine SCADA Expert SQL query.

Hello

 

I am making a SQL query to a MySQL database. 

 

When I run the query in MySQL, I get the right result. 

 

When I send the SQL statement from Machine SCADA Expert, the result is not right. 
It is like the lase WHERE clause is ignored. 

 

This is the Query: 

SELECT SUM(Minute_In_State) From
   (SELECT
   Time_Stamp,
   State,
   TIMESTAMPDIFF(SECOND, LAG(Time_Stamp) OVER(ORDER BY Time_Stamp), Time_Stamp)
   AS Minute_In_State
   FROM db.xyz_1_state
   ORDER BY Time_Stamp) AS T
Where State = 1;

 

Any suggestion why the result is different, when sent from Machine SCADA Expert?

Tags (1)

Accepted Solutions
jthyge
Ensign
Ensign
0 Likes
0
760

Re: Wrong result in Machine SCADA Expert SQL query.

The input field "SQL statement" is limited to 10 lines, the rest of the statement is not saved. 

 

The solution is to make the SQL statement in one line. 

This makes it way more difficult to oversee the statement, but it works. 

See Answer In Context

Tags (1)
2 Replies 2
jthyge
Ensign
Ensign
0 Likes
1
767

Re: Wrong result in Machine SCADA Expert SQL query.

I found the connection string from Machine SCADA Expert, where the last WHERE clause is missing:

 

Connection String:'26F9D15629', Last command: 'Cursor Open SQL: 'SELECT SUM(Second_In_State) From
(SELECT
Time_Stamp,
State,
TIMESTAMPDIFF(SECOND, LAG(Time_Stamp) OVER(ORDER BY Time_Stamp), Time_Stamp)
AS Second_In_State
FROM db.xyz_1_state
ORDER BY Time_Stamp) AS T, Total Connections: 1]

 

So the problem must be in Machine SCADA Expert. 

Tags (1)
jthyge
Ensign
Ensign
0 Likes
0
761

Re: Wrong result in Machine SCADA Expert SQL query.

The input field "SQL statement" is limited to 10 lines, the rest of the statement is not saved. 

 

The solution is to make the SQL statement in one line. 

This makes it way more difficult to oversee the statement, but it works. 

Tags (1)