How to use Correlation name in SQL widget

How to use Correlation name in SQL widget

  

Hi ,

In my sql i am calling the {user} table twice and hence i have the bellow error.How to use the correlation name to distinguish them.Below is my sql.

SyntaxEditor Code Snippet

SELECT {SRJournal}.*,{User}.[Name],{SRStatus}.[Label] ,{EntityGroup}.[EntityDesc],{SRPriority}.[PriorityName],{User}.[Id] 
 from {SRJournal}
 inner join {SRStatus} on  {SRStatus}.[Id]={SRJournal}.[StatusId]
 and {SRStatus}.[Label] in ('Pending Approval','Request For Info')
 inner join {User} on {User}.[Id]={SRJournal}.[ModifiedBy]
 inner join {EntityGroup} on {EntityGroup}.[Id]={SRJournal}.[EntityGroupId]
 inner join {SRPriority} on {SRPriority}.[Id]={SRJournal}.[PriorityId]
 inner join {SRApprvLog} on {SRApprvLog}.[TicketNo]={SRJournal}.[TicketNo]
inner join {User}  on {User}.[Id]={SRApprvLog}.[ApproverId]
 where{SRJournal}.[ModifiedBy]=@PUserId

Does anyone has idea on this?Help!!{user} table.

hi Johnson Lim,

i go through your query, and found 

 inner join {User} on {User}.[Id]={SRJournal}.[ModifiedBy]
 where{SRJournal}.[ModifiedBy]=@PUserId

you are using SRJournal in inner join and also in where condition, i suppose you can remove that join since you already using it in the where condition, this should work. You can try that.

Regards,

Manish Jawla

Hi Manish,

Thanks for your prompt feedback.Below are my replies

This is to get the userId who perform the transaction

where{SRJournal}.[ModifiedBy]=@PUserId

This is to get the name of the user who perform the transaction

inner join {User} on {User}.[Id]={SRJournal}.[ModifiedBy]

I need to join with {User} table again to get the approver name who will be approve the transaction.

Do you have any idea on the correlation?

hey Johnson,

i got your point but i don't think by using both the condition you can achieve the desired result. There might be some different approach to do that but since you are already narrowed your result in where condition, join will not have much impact on the result. You can give a try :). Regarding correlation i don't have much idea.

Regards,

MJ  

Solution

Johnson Lim wrote:

Hi Manish,

Thanks for your prompt feedback.Below are my replies

This is to get the userId who perform the transaction

where{SRJournal}.[ModifiedBy]=@PUserId

This is to get the name of the user who perform the transaction

inner join {User} on {User}.[Id]={SRJournal}.[ModifiedBy]

I need to join with {User} table again to get the approver name who will be approve the transaction.

Do you have any idea on the correlation?

Hi Johnson,

I guess the root cause of your problem is because you use User entity twice.

I can understand why you need to use it twice, but the system will reject it regarding the same exposed names.
What you can do here is to add an alias for each User to let the system know the difference between both.


Here is the example:

from {SRJournal}
 inner join {SRStatus} on  {SRStatus}.[Id]={SRJournal}.[StatusId]
 and {SRStatus}.[Label] in ('Pending Approval','Request For Info')
 inner join {User} FirstUser on FirstUser.[Id]={SRJournal}.[ModifiedBy]
 inner join {EntityGroup} on {EntityGroup}.[Id]={SRJournal}.[EntityGroupId]
 inner join {SRPriority} on {SRPriority}.[Id]={SRJournal}.[PriorityId]
 inner join {SRApprvLog} on {SRApprvLog}.[TicketNo]={SRJournal}.[TicketNo]
inner join {User} SecondUser on SecondUser.[Id]={SRApprvLog}.[ApproverId]

Bold text are the alias of entity 'User'.


Hope it will hep you.


Cheers,

Lady

Solution

Hi Johnson,

I'm going to say the same thing I said before in a reply to a different post, slightly different worded: don't use the SQL statement. Use Aggregates instead.

Aggregates are the #1 way to fetch data from the database for an OutSystems platform app. Aggregates let you easily do what you want, and creates aliases automatically in case you join the same table twice (the name of which you can change). Of course, there are circumstances where you can only achieve something with SQL instead of an Aggregate, like subqueries, IN(), WITH() and so on, but those are pretty advanced and you typically don't need them.

I've seen people coming from a background of writing SQL queries that are reluctant to use Aggregates when first encountering the Platform, but in your case, it seems you don't know SQL very well at all, so I'm at a loss to come up with a reason why on earth you're trying to use SQL instead of an Aggregate. Again, don't do that. Use Aggregates. There, I've made my point.

Thanks Lady,Kilian and Manish