A Concerning SQL issue

Scriptcase 9 General
#1

have a grid where the SQL command is based on an inner join

It has always worked.

Today it gave me incorrect selections. I cut and pasted the SQL statement into the systems SQL Database Builder and it worked, so I knew it was correct.

I created a new app with same join, copied the newly generated SWL statement back into my app, and it worked

Somehow the SQL in the app got corrupted. Anyone else have this issue? Its worrisome.

#2

When we first started with SC we did have issues with anything other than single-table SQL. Sometimes SC would get a bit confused or not function as expected. Since then we’ve always used MySQL views and we have hardly any issues with the SQL statements in our apps. I’d recommend creating a view for every grid if you can.

#3

Try

SELECT field1, field2, field3 FROM
(SELECT .. AS field1, ... AS field2, .. AS field3
   FROM tabla
   LEFT JOIN ....
   LEFT JOIN ....
) AS TableAlias
#4

Yes, I’ve experienced similar issues with Scriptcase in the past. Sometimes, the stored SQL statement in the app gets corrupted due to hidden characters or formatting bugs, especially after edits. Re-copying or rebuilding the SQL often fixes it, as you did. It’s a good idea to clear the Scriptcase cache and always back up your projects regularly. Keeping Scriptcase updated can also help prevent such issues.