advanced query table alias name

advanced query table alias name

  

I have wrote sql just like 

--------------------------------------------

Select

    {User}.*,

    Area1.*,

    Area2.*

from

    {User}

    left join

        {Area} Area1

    on  {User}.[AreaCd1] = Area1.Cd

    left join

        {Area} Area2

    on  {User}.[AreaCd2] = Area2.Cd

--------------------------------------------


And I got warning from outsystems.

--------------------------------------------

Unexpected SQL
There is a mismatch between the number of columns SELECT'ed and the output structure in xxx.

----------------------------------------------

I think it is because one column is deleted from Area table.

How to avoid the [warning] message?


thank you very much.


Hello hon,

I'll assume that in the Output Structure folder of the SQL you are defining 3 entities also: User, Area, Area

If so, try to refresh the dependences on this module (assuming Area is defined outside this module).

Cheers,
Eduardo Jauch

Hello,

normally that is because the output Entities/Structures should have the same number of attributes that you are selecting, for example if I do an advanced query:

SELECT {User}.[Name], {User}.[Username] from {User}

and my output is a Structure like this:

I will have the same warning because I am selecting two attributes and my output has only 1 attribute,


Hope that helps to solve your issue,

VC

Solution

It's also dangerous to use * in advanced Queries, mainly because the actually order of attributes might not be the same with the visual order of the attributes.

so, like the others, don;t use *, unless you are very sure.

if you are sure, the warning simply tells you service-studio is unable to verify it, and you simply hide the message :D

(on that note, if the sql is that simple, you actually are better off with an aggregate)



Solution

Thank you very much.

I think  it is not good to use *.

I modify it , and define strutcte .

It is ok.





Hi Hon,

when you use something like 

SELECT {SomeEntity}.* from {SomeEntity}

the platform actually converts it into a SELECT on the individual attributes of SomeEntity.

SELECT SOME_LONG_NAME_FOR_TABLE.[Attr1], SOME_LONG_NAME_FOR_TABLE.[Attr3], SOME_LONG_NAME_FOR_TABLE.[Attr10] from SOME_LONG_NAME_FOR_TABLE

The corresponding database table might have more columns, due to you modifying your entity (when you remove attributes the respective columns are not deleted from the database Table, the platform only stores metadata about which database table Columns are being used by which entity Attributes).

From my experience, when you create an Alias in a SQL statement, the platform doesn't perform this translation, and as such you get all columns from the DB Table (that's what * does in SQL) instead of only the columns that support the Attributes defined for the current version of the Entity).

The simplest solution, for this particular case, would be to use an Aggregate, like J. suggested.

Hi Jorge Martins,

Thank you very much?