Hi Killian,

Thanks a lot for the heads up. Indeed, at first sight that doesn't seem right.

However, what is preventing you from doing

SELECT {USER_MASTER}.*
FROM {USER_MASTER}

in your advanced query, out of curiosity?

I am forwarding this to our technical support, to enter in our  maintenance backlog.

Regards,

Paulo Tavares

Ah, I know I should've mentioned that, but I was in a hurry :). The reason is that I join the same user table twice (user last modified and user created), so I have to use the aliases to distinguish the two.

Ok, I see!

Well, I added that comment to the issue report.

In the meanwhile, you'll probably have to declare the individual attributes in the query, unless or someone knows of a better answer/workaround for it.

Regards,

Paulo Tavares

Hi Kilian...

and what stops you to do that in a simple query? Using advanced queries when simple queries can do the job isn't a development best practice.

Did you know If you're using a simple query in a screen action the platform does the optimal tunning to get only the used attributes instead of the entire Entity attributes?

Cheers, 
RNA

Hi Killian

The behaviour you're reported is not as much a bug as it is a miss-feature.

What happens is that the Advanced Query SQL syntax parser of the Service Studio does have some limitations in comparison to say, the SQL Server Management Studio query parser, as it doesn't understand the full syntax and vocabulary of the PL/SQL language. In this case, it doesn't understand the use of alias in select statements.

The Service Studio SQL Parser was designed considering the most common PL/SQL patterns, but it allows the developer to create complex queries nonetheless. That's why the Service Studio generates a warning instead of a validation error, like if saying " I can't confirm that SQL syntax because I don't understand it, but I trust that you know what you're doing".

Support for Service Studio to understand the ALIAS on advanced queries as been already requested to OutSystems R&D, to be included in a future Agile Platform version.

Despite all this, I would like to reiterate the suggestions made by Paulo and Ricardo, regarding the usage of Simple Queries, and add another, which is: in advanced queries, beware of using ALIAS.* syntax, since it may cause runtime errors due to data types mismatch. This can happen because using an ALIAS.*, will let the database engine to expand the columns of the query, instead of telling the Agile Platform to do, and if the data model has more  columns then the attributes defined on the entity being queries, or it has a different order of the columns, causing data type mismatch errors like the one you've got on the Test Query.

You either use {ENTITY}.*, which will let the Agile Platform expand the attributes list automatically, instead of the database engine, or if you need to use ALIAS, you should always expand the attributes by hand on the Advanced Query, like you've done on your workaround.

Hope this information is helpful in understanding a little more about the Agile Platform internals on advanced queries.

Cheers

Miguel Simões João

@Ricardo: "and what stops you to do that in a simple query. Using advanced queries when simple queries can do the job isn't a development best practice" - I agree, but neither is it best practice to post a 50-line advanced query when two lines are enough to explain your problem. The real query gathers data based on filter settings, and contains various LIKE and other 'advanced' features a simple query cannot handle.

@Miguel: Thanks for the explanation, I fugured it must've been something like that. What got me on the wrong foot first is that the platform does give a meaningful error when doing e.g. "SELECT * FROM {SOMETABLE}", explaining that "SELECT statements with '*' cannot be validated against the output structure (...). Runtime errors may occur." And though, as you say, ALIAS.* "will let the database engine to expand the columns of the query, instead of telling the Agile Platform to do", the platform at least seems to be aware of the number of actual columns that ALIAS.* will produce, as it (rightly) complains about a column count mismatch. Anyway, again thanks for clearing things up, and I hope this will be fixed in a future update soon.

Hi Killian,

I think that you get the error :  "Could not assign '' to 'USER_MASTER.CreatedBy'... Input string was not in correct format"  because in your database the UserMaster Entity has more or less columns than the UserMaster Entity on your EnterpriseManager eSpace. For example, if you delete the LastName column from your UserMaster Entity in ServiceStudio and Published it the LastName Column disappears on ServiceStudio but it remains in your database.

Best regards,

Tiago Reis