session variable in sql query

SyntaxEditor Code Snippet

I wrote the following code in order to Display data of customers whose country is same as the SalesAgent(User). For Logged in User info i tried to use the Session variables. Please help me where I am going wrong . Thanks in advance.

SELECT {CustomerName}.*,{SalesAgent}.*,{User}.*
FROM (({CustomerName}
     INNER JOIN {User} ON ({CustomerName}.[CountryCode]={User}.[MobilePhone]))
     INNER JOIN{SalesAgent} ON ({SalesAgent}.[Name]={User}.[Name]))     
WHERE
({SalesAgent}.[Name]='$_SESSION[Username]')
 
Solution

Hi Poorvi,

To record the user against an entity normally we use the UserId from the System User entity.  Then use the function GetUserId() to check for the current user.

Otherwise, if you need to use the username, you can JOIN on the User table with {User}.[Username].

Also, you might find it more effective to use an Aggregate rather than Advanced SQL to produce your query. This will create the joins for you and allow OutSystems to optimise the query.

So using User.Id, in AdvancedSQL, add a parameter called CurrentUserId as type User Identifier, set the parameter to GetUserId() and in the query use @CurrentUserId, something like this:


SELECT {CustomerName}.*, {SalesAgent}.*, {User}.*
FROM {CustomerName}
INNER JOIN {Customer} ON {Customer}.[Id] = {CustomerName}.[CustomerId]
INNER JOIN {SalesAgent} ON {SalesAgent}.[Id] = {Customer}.[SalesAgentId]
INNER JOIN {User} ON {User}.[Id] = {SalesAgent}.[UserId]
WHERE {User}.[Id] = @CurrentUserId


If you need to use username, join like this, however this could mean that when a user is deactivated, and another user created with the same username, the SalesAgent entity would be referencing the new user; which may not be want you want.  The Is_Active clause helps ensure the Username join does not return 2 records.

SELECT {CustomerName}.*, {SalesAgent}.*, {User}.*
FROM {CustomerName}
INNER JOIN {Customer} ON {Customer}.[Id] = {CustomerName}.[CustomerId]
INNER JOIN {SalesAgent} ON {SalesAgent}.[Id] = {Customer}.[SalesAgentId]
INNER JOIN {User} ON {User}.[Username] = {SalesAgent}.[Username]
WHERE {User}.[Id] = @CurrentUserId AND {User}.[Is_Active] = 1


I hope this helps!

Kind regards,

Stuart

Solution

Thank a lot Sir .... It worked :)