Issue regarding alias in SQL in Personal Environment

Hi all!

I have a relation from 1 to many between two entities Gameweek and Gameweek User. I'm fetching data from the entity GameweekUser when GameweekId is equal to 1.

 When using the aggregate, it is showing some data (the real one!) and when using SQL with alias it's showing different data. 

Pay attention to the PaymentFree attribute. In the aggregate is always set to False. In SQL with alias is mixed in the wrong way. 

If I remove the alias it works fine, but I need them because I want to do a subquery in the where clause.

Aggregate

SQL



Any help?

Thanks

Best Regards!

Hi Bruno,


It is a strange  behaviour indeed. Query syntax looks fine.


The CreatedOn timestamp is also different for each record. Has your personal environment been down?


If so, did that advanced query already existed before the environment was down?


I ask this because the timestamps for the advanced query are later than those of the aggregate. If you fetched a new environment, the platform may have fetched an earlier capture of the database and created entities and records based on that data. But using that advance query with aliases it may be possible that, under the hood, the paltform is still pointing to the other database/tables.


Check the executed SQL for both aggregate and advanced query and compare them.


Regards


João

Hi,

Can you show the executed query from the aggregate and the SQL tool?

Cheers

João, 

The timestamp is different because they are from different entities.

Cheers

Hi to both and thanks for your answers!

@João, like Eduardo said the timestamps are from different entities. My PE was down on 20/02. The data and the queries were created after that.

@Eduardo, by executed query you mean this?

Aggregate

SQL

SELECT      CurrentGameweekUser.* 
FROM         [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER]  CurrentGameweekUser 
INNER JOIN   [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEK]      CurrentGameweek ON  CurrentGameweekUser.[GAMEWEEKID]    =   CurrentGameweek.[ID] 
WHERE   CurrentGameweekUser.[ISACTIVE]=1 AND 
        CurrentGameweek.[ISACTIVE]=1    AND 
        CurrentGameweekUser.[GAMEWEEKID]=@GameweekId


Thanks for helping, 

Best Regards!

Yep, exactly this. Could you also post the executed SQL for the SQL tool?


EDIT. Sorry, didin0t noticed the other one was for the SQL Tool. Thanks.

Hi,

Can you remove the aliases from the SQL and post here the executed SQL for us to see the difference between them?

I'm seeing a difference between aggregate and SQL, but I wouldn't expect this to give different results...

Sure!

Executed SQL without alias

Thanks!

OK. Sorry, but I need to see the entire executed SQL from both with and without the alias, with the complete SELECT, etc.

Can you paste here (not as image)? 

The first one (with alias) was not exactly the executed, it seems).

Sorry, Eduardo, I'm not sure I'm understanding what you're saying. Following is the query and the executed SQL from both with alias and without. Please let me know if this is not what you want!

With alias:

Query

SELECT      CurrentGameweekUser.*
FROM        {GameweekUser}  CurrentGameweekUser
INNER JOIN  {Gameweek}      CurrentGameweek ON  CurrentGameweekUser.[GameweekId]    =   CurrentGameweek.[Id]
WHERE   CurrentGameweekUser.[IsActive]=1 AND
        CurrentGameweek.[IsActive]=1    AND
        CurrentGameweekUser.[GameweekId]=@GameweekId 

Executed SQL

SELECT      CurrentGameweekUser.* 
FROM         [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER]  CurrentGameweekUser 
INNER JOIN   [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEK]      CurrentGameweek ON  CurrentGameweekUser.[GAMEWEEKID]    =   CurrentGameweek.[ID] 
WHERE   CurrentGameweekUser.[ISACTIVE]=1 AND 
        CurrentGameweek.[ISACTIVE]=1    AND 
        CurrentGameweekUser.[GAMEWEEKID]=@GameweekId

Without alias

Query

SELECT      {GameweekUser}.*
FROM        {GameweekUser}  
INNER JOIN  {Gameweek}  ON  {GameweekUser}.[GameweekId]    =   {Gameweek}.[Id]
WHERE   {GameweekUser}.[IsActive]=1 AND
        {Gameweek}.[IsActive]=1    AND
        {GameweekUser}.[GameweekId]=@GameweekId 

Executed SQL

SELECT       [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[ID], [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[GAMEWEEKID], [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[LEAGUEUSERID], [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[SCORE], [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[PAYMENT], [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[PAYMENTFREE], [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[ISACTIVE], [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[CREATEDBY], [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[CREATEDON], [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[UPDATEDBY], [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[UPDATEDON] 
FROM         [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER]   
INNER JOIN   [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEK]  ON   [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[GAMEWEEKID]    =    [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEK].[ID] 
WHERE    [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[ISACTIVE]=1 AND 
         [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEK].[ISACTIVE]=1    AND 
         [HTGMZR011].DBO.[OSUSR_VAQ_GAMEWEEKUSER].[GAMEWEEKID]=@GameweekId

Please note that the SQL without alias is retrieving the correct data!

Thanks,

Best Regards!

Solution

Hi Bruno,


instead of using:

SELECT      CurrentGameweekUser.*


have you tried querying using only the columns you want to output, like:


SELECT      CurrentGameweekUser.[Id], CurrentGameweekUser.[GameWeekId]


João

Solution

Thanks, Bruno, that's what I needed.

Let me ask you something...

Do the entities involved have, somehow, deleted attributes?
You can use João«s suggestion to select the attributes, to avoid a problem (maybe?) that is the query fetching attributes that are deleted from the entity but exists in the database table...

As for now it is the only thing I am thinking of...

Something like:

Original table in database

Attributes: A, B, C

Then you deleted and put back an attribute:

Attributes: A, B(Deleted), C, NewB

When you so a select Alias.*

It will fetch all the columns and then, your newB attribute, that maybe, in the entity, is in the same position as the previous (deleted) one, and is of the same data type (then you don't have errors), will have values that do not make sense or are not correct... 

I understand, Eduardo. I think yes, I have deleted some attributes from GameweekUser entity during the development.

Just tested with selecting only the required fields and it's working with alias!

Thank you both for your help! 

Best Regards!