1511
Views
14
Comments
Advanced Query Pitfalls - using alias and wildcards
Question
A common development problem that I find while working @ Product Support is with the use of Advanced Queries in two very similar scenarios:
  • Alias (for more complex queries);
  • Wildcards.
Documentation actually explains this very well for wildcards (the use of {Entity}.* instead of *) but misses an explanation about alias. It also does not explain what happens when you fail to use the recommended patterns.

You can follow this explanation with the attached OML, if you want.

Let me start out with a simple example: let's look at a very simple Contact entity and populate it with some data:

          

The above screenshot is from the following query:
SELECT {Contact}.*
from {Contact}
However, since this is the initial structure (first publish of the eSpace), the following query will yield the exact same result.
SELECT *
from {Contact}


In the next step, I will move the Email attribute - it now comes after Phone instead of before:



After publishing the new version of the eSpace, the same (correct) advanced query works OK - but now the email will appear at the end:

SELECT {Contact}.*        
from {Contact}

However, if you now use the version with a simple *, things are not looking so good:

SELECT *
from {Contact}                 

What happens is that:
  • When you changed the order of the columns in the entity, the database table was not changed - then only thing that happens is that the Entity object (in the eSpace) now has the fields in a different order;
  • When you write {Contact}.* in an advanced query, what is actually being sent to the database is {Contact}.[Id], {Contact}.[Name], {Contact}.[Phone], {Contact}.[Email] , and thus you get the fields in the correct order;
  • However, when you just write *, the Agile Platform does nothing, and you get whatever fields are coming from the database, in whatever order they are sent.
Note that this only worked because both columns Phone and Email are of type Text - if there were type mismatches you would have an error message.

This gets even stranger if you add or delete columns. In my next example I will delete the Email and Phone attributes and add a new attributes StreetAddress and ZIPCode.

As expected, the (correct) query will show me the empty columns (I did not bootstrap them):

SELECT {Contact}.*                      
from {Contact}

However, with the other query, it will try to re-use the existing data from the other columns!

SELECT *
from {Contact}                               

A similar effect happens when you use alias in your queries, for the exact same reason - the Agile Platform will not try to parse them.
So an innocently similar construct will also result in problems:

SELECT contactlist.*
from {Contact} contactlist           

If you do need to use alias (in a much more complex query than this one) make sure to expand the fields one-by-one.

If you have anything to add to this post, feel free to do so.
Cheers,
AdvQueriesPitfalls.oml

mvp_badge
MVP
Rank: #155
Hello Acácio,

Nice post, very clear how the agile platform works with advanced queries. I'm glad mine advanced queries are ok :).

Two questions regarding you're post:

1) You say that "SELECT {Contact}.* from {Contact}" is transformed by the platform, is this also making the query (a little) slower since it needs to be converted?

2) Regarding the alias in queries: If I'm correct the sql code, generated in a simple queries when testing, also uses alias table names? Does this mean that when you use this sql code in a advanced query (copy pase) and don't change the alias table names to the original table names that
it gives the same problems? Or is this solved since a simple query always generate the select with all attributes instead of other table name?

I've see people who uses simple queries to make the sql code and set it in a advanced query and changes some things (select attributes and group by statements), but leave the join tables names the same.

Kind regards,
Evert
Staff
Rank: #88
Hi Evert

About your questions:

1) You say that "SELECT {Contact}.from {Contact}" is transformed by the platform, is this also making the query (a little) slower since it needs to be converted?
The conversion (from {Entity}.* to an enumeration of all the attributes in the correct order) is done in compile time (when you do 1CP of the eSpace), so this will not make it slower - the final result is actually the same as if you wrote {Entity}.[Attr1], {Entity}.[Attr2].... in your query.


2) Regarding the alias in queries: If I'm correct the sql code, generated in a simple queries when testing, also uses alias table names?
Yes, it does.
 
Does this mean that when you use this sql code in a advanced query (copy pase) and don't change the alias table names to the original table names that it gives the same problems? Or is this solved since a simple query always generate the select with all attributes instead of other table name?
The code generated by simple queries is something like:

SELECT ENContact.[ID] o1, ENContact.[NAME] o2, ENContact.[STREETADDRESS] o3, ENContact.[ZIPCODE] o4
FROM {Contact} ENContact
ORDER BY ENContact.[NAME] ASC

So you are not affected by the problem of alias because each attribute is individually declared (even if with an alias). As long as you individually declare each attribute you are SELECTing, alias are safe.
However, you will be affected if, after creating the query, you change the order of the attributes - since that operation will not swap the order of the attributes in the query text like {Entity}.* would do.

That is actually one of the reasons why advanced queries selecting individual attributes should always use custom structures instead of the query records.

There are still other (safe) constructs you could use, though these could make your queries (slightly - or not) slower:

SELECT {Contact}.*
FROM {Contact}
WHERE {Contact}.[ID] in (SELECT  [...] /* your arbitrarily complex query here */)
ORDER BY ENContact.[NAME] ASC

Cheers,
mvp_badge
MVP
Rank: #35
Acácio, got a quick question as on the conversion from {Entity}.* to an enumeration of all the attributes.

I was just trying something in an Advanced Query that would be something like:

SELECT {Contact}.*, Count({Contact}.[ID])
FROM {Contact}
WHERE /* your arbitrarily complex query here */
GROUP BY {Contact}.*

Does the platform also converts the {Entity}.* in the GROUP BY clause? Cause I didn't get an error while testing it, but i currently don't have any data on the entities to be sure of the results.

Best regards,
PC
Hello Acácio,

Thank you for the heads up.

This actualy explain a problem I had in one client with Oracle DB. We made some advanced queries with alias.* and the platform gave warnings about the output being diferent from the entities structures.

As it was a warning, and everithing seemed to work, we disregard it. Now everithing is clear thanks to your article. I think that instead of a warning, an error would be best used in this cases.

Kind regards,

Nuno
Rank: #183
Hello.
I'm aware of the alias problem in advanced queries.
But how can I avoid using aliases in the following advanced query? Is it possible?
I know I should use simple queries whenever possible. This is just an example of a more complex advanced query.
Thank you.
SELECT
    {MY_ENTITY}.*,
     group1.*,
     group2.*
FROM
    {MY_ENTITY} 
    INNER JOIN {GROUP} group1
        ON (group1.[Id] = {MY_ENTITY}.[Group1_Id])
    INNER JOIN {GROUP} group2
        ON (group2.[Id] = {MY_ENTITY}.[Group2_Id])

Staff
Rank: #12
Hi all,

Imho I'd go to not using wildcards at all in advanced queries :)

Easily you fall into another pitfall when you start putting things like

SELECT
             {MY_ENTITY}.*
            , group1.*
            , group2.*
            , {ANOTHER_TABLE_WITH_300_ATTRIBS}.*
            , {TABLE_INCLUDING_NTEXT_ATTRIBS}.*            
FROM
....

It is a best practice to only return the columns you need; it might give you a little more work but it pays off in the long run.

Cheers,
André
Ricardo Silva
Rank: #0

Hello sbd da,


The query example on Tiago's post does not warrant use of Advanced Query. You can do that query using an aggregate without any problem and let the platform generate the correct aliases and attribute expansions.


Do you have a specific example of a query which you can't do on an aggregate which you need help with ?

Ricardo Silva