Sometimes when using the Advance Query, there is an error just in case the query (sql) is like this:
select {AgingReport_D}.*
from {AgingReport_D }
and the output is of type entity, like:
as there in 'Dirty' column in the physical table (in the select {AgingReport_D}.* ) and the result is mapped to the AgingReport_D without that 'Dirty' column (column that exists before in the entity and then deleted, but still remains in the physical table to keep versioning) which raise an error - column not exists-.
And it is very irritating when in production, it works first, and as the development is always happening after that there is an odd that a column is deleted and makes that error.
To work around, just use structure and do the mapping and or entity, but do not rely on select *, instead use select {entity}.column1, {entity}.column2,etc .
regards
I never heard about this, I have asked OutSystems for feedback, if it is true, it reads like a bug to be fixed.
Hi @ibox ,
If you write your sql inside the sql widget, doing a select {entity}.* with the entity as output structure, will ensure that OutSystems matches the correct physical columns to the correct query output attributes. Deleted columns (which you call dirty) will not take the place of others, and it will deal well after rearranging the sequence of attributes in your datamodel.
It is a select * that should not be used, as is also warned about by Service Studio.
Also, enumerating all attributes should not be done if you want all of them anyway, that one would break if attribute sequence of the structure or entity of the output gets changed.
I thought that maybe your problems were caused by the fact that you seem to be sending the sql as an input parameter, but when I test, even that deals fine with deleted or rearranged attributes.
Can you share more details about what exactly you are doing, like maybe it is related to you query being run in a different module withouth refreshing dependencies ??
Dorine
Yes, i ever experienced this as there is no match number of columns between the dirty physical table and the new entity, so it works if i select using {entity}.[column1], etc..
it also works if i'm using {entity}.*
using {entity}.[attr1], {entity}.[attr2],... is breakable if you rearrange the attributes of the output structure you are using.
That's why i prefer using Structure to Entity for Output as it is more flexible, u can add other extra column (say concatenate between two columns).
i get that,
very often, having a dedicated structure to match a complex query is the way to go, and as there is a very tight connection between the structure and the query it is used for, the structure will probably not suddenly change and break the sql.
Typically, you use sql for more complex things, and it is very likely that their output wouldn't match with any entity directly.
But this is an entirely different subject than scaring the community about a practice that is also perfectly safe to use.