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
80820members
346892posts

Wrong result in Machine SCADA Expert SQL query.

Solved
jthyge
Ensign
Ensign
0 Likes
2
687

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
676

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
683

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
677

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)