Advanced SQL Verification

The syntax of the advanced queries you type on the logic canvas are validated at development time:

when you press the Verify button in the Advanced query editor;
during the verification of your eSpace.

The SQL verification executed, in Service Studio, takes into account the following:

Names of Entities must be written as {Entity name}. If not, a warning message is presented notifying you that the SQL syntax is not correct.
Names of Attributes must be written as {Entity name}.[Attribute name]. If not, a warning message is presented notifying you that the SQL syntax is not correct.
There must always be an output structure, even with some dummy information. If not, an error message is presented notifying you that the SQL syntax is invalid.
If you want to have text in the SQL statement, you must use ''. For example:

select {customer}.[id] from {customer} where {customer}.[name] = 'John'

The syntax of the SQL must be correct, according to SQL definition. If not, an error message is presented notifying you that the SQL syntax is invalid.
If you are using a SELECT statement, the definition of the Output structure is also validated:

The order and the number of attributes in the Output Structure must match the result of the select. If not, a warning message is presented notifying you that the Output structure definition is not consistent with the statement. Service Studio also warns you if it detects that you are selecting a complete entity, in the SQL, into a different entity in the Output structure.

There are, however, some situations where it is not possible to warn you about possible inconsistencies:

If you use SELECT * instead of SELECT {Entity name}.*
If you use inline input parameters in the select statement like, for example, SELECT @attrs and attrs is an inline parameter.

However, this verification does not guarantee that the statement will be correctly executed in the database, since Service Studio only verifies the syntax and the consistency of the Output Structure. There is no verification, for example, related to the database permissions.