Hello,
I am having difficulty regarding filtering data using advanced SQL, when I apply a single filter, query is showing the right amount of data, but when I apply multiple filters, the results are wrong. Here I will give the scenario.
This is the query I wrote:
SELECT {ExternalAudit}.[AuditScope], {CommitmentExternal}.[CommitmentNo], {Group}.[Id], {CommitmentExternal}.[StatusId], {ExitMeetingExternal}.[Date], {TindakLanjutExternal}.[DueDate], {CommitmentExternal}.[ReviseDueDate], {TindakLanjutExternal}.[Id], {ExternalAudit}.[Id], {CATExternalDetail}.[Id], {CAT_External_Reviewer}.[UserId]FROM (((((((({CommitmentExternal} INNER JOIN {TindakLanjutExternal} ON ({CommitmentExternal}.[TindakLanjutExternalId] = {TindakLanjutExternal}.[Id])) INNER JOIN {CATExternalDetail} ON ({TindakLanjutExternal}.[CatExternalDetailId] = {CATExternalDetail}.[Id])) INNER JOIN {ExternalAudit} ON ({CATExternalDetail}.[CatExternalId] = {ExternalAudit}.[Id])) INNER JOIN {Group} ON ({TindakLanjutExternal}.[GroupId] = {Group}.[Id])) LEFT JOIN {ExitMeetingExternal} ON ({ExternalAudit}.[Id] = {ExitMeetingExternal}.[ExternalAuditId])) LEFT JOIN {GICMapping} ON ({Group}.[Id] = {GICMapping}.[GroupId])) LEFT JOIN {CAT_External_Reviewer} ON ({CATExternalDetail}.[Id] = {CAT_External_Reviewer}.[CATExternalDetailId])) LEFT JOIN {MappingGICAuditee} ON ({GICMapping}.[Id] = {MappingGICAuditee}.[MappingGICId]))WHERE @isreviewer = 0 or (@isreviewer = 1 AND {CAT_External_Reviewer}.[UserId] = @userid) AND ((@isadmin != 1) OR (@isshowallcae != 1) OR (@isreviewer = 1) OR {CAT_External_Reviewer}.[UserId] IN @userid) AND ((@isadmin = 1) OR (@isshowallcae = 1) OR (@isreviewer != 1) OR 1=1) AND (@isauditeereviewer = 0) OR (@isauditeereviewer = 1 AND {GICMapping}.[AuditeeReviewer] IN @userid) AND (@statusid is null or @statusid = 0) or (@statusid is not null and {CommitmentExternal}.[StatusId] in @statusid)GROUP BY {ExternalAudit}.[AuditScope], {CommitmentExternal}.[CommitmentNo], {Group}.[Id], {CommitmentExternal}.[StatusId], {ExitMeetingExternal}.[Date], {TindakLanjutExternal}.[DueDate], {CommitmentExternal}.[ReviseDueDate], {TindakLanjutExternal}.[Id], {ExternalAudit}.[Id], {CATExternalDetail}.[Id], {CAT_External_Reviewer}.[UserId]
The query I am having problem with is highlighted in red.
CORRECT SCENARIO
When I apply no filter to @statusid, there are 12 rows of data, all showing {CommitmentExternal}.[StatusId] = 12. This is the correct amount of data.
INCORRECT SCENARIO
Now I am gonna apply a number to @statusid, this time I am going to do @statusid = 16. This time time amount of data being shown is wrong, because I got 16 rows of data when I am expecting zero, since there are no data with @statusid = 16, but when I filter @statusid = 16, both data of @statusid = 12 and @statusid = 16 is showing. I am not sure where the problem is.
This scenario applies to any input on @statusid, whenever I input a number on @statusid, the wrong amount of data always show, even if i input @statusid = 12
This is the amount of data showing when I input @statusid = 12. Obviously inaccurate because there should be only 12 rows.
From what I noticed, everytime I input something to @statusid, @userid input is getting ignored, because as I shown, the query should only show data with @userid = 1234, but when I put something to @statusid, somehow that filter is being ignored.
As seen here, @userid other than 1234 is being shown when I enter a number to @statusid
I am not an expert on SQL so I am asking your expert opinion about this issue. Something might be wrong in my query but I could not point it out and I have been looking for answer but couldn't find any.
n.b. do not advice me to change advanced SQL into aggregate, this is the last resort because using aggregate is too hefty for my issue. This was initially an aggregate, but I have to change it to SQL because of that.
Seeking your advice.
As a standalone clause your filter (@statusid is null or @statusid = 0) or (@statusid is not null and {CommitmentExternal}.[StatusId] in @statusid) works, but if it is surrounded by something else like (some stuff) AND (firstpart) OR (secondpart) AND (something else), it will work like ((some stuff) AND (first part)) OR ((second part) AND (something else)), where red expressions will go before the green part. You need to enclose your condition additionaly to ensure correct operation order: (some stuff) AND ((first part) OR (second part)) AND (something else).
Same applies to @isreviewer = 0 or (@isreviewer = 1 AND {CAT_External_Reviewer}.[UserId] = @userid) and (@isauditeereviewer = 0) OR (@isauditeereviewer = 1 AND {GICMapping}.[AuditeeReviewer] IN @userid) AND (whatever else). Red parts have priority over green ones.
Hi Theodorus,
I assume that in the last row (and in the row before that) you wanted to create an expression like
(X) and ((A) or (B)). But there are expressions like (X) and (A) or (B), which evaluate differently and work as ((X) and (A)) or (B).
Hello Daryna,
To clarify, what I want to achieve by this filter: (@statusid is null or @statusid = 0) or (@statusid is not null and {CommitmentExternal}.[StatusId] in @statusid) is when @statusid is equal to 0 or when @statusid is null, do not filter data (show all), but when @statusid is NOT null, filter {CommitmentExternal}.[StatusId] based on the value of @statusid.
Similar case to which I want to apply with:
@isreviewer = 0 or (@isreviewer = 1 AND {CAT_External_Reviewer}.[UserId] = @userid) and (@isauditeereviewer = 0) OR (@isauditeereviewer = 1 AND {GICMapping}.[AuditeeReviewer] IN @userid)
Not sure whether what i wrote is correct or wrong, bad syntax, or wrong position of parenthesis.
Hi Daryna,
Thanks for your insight, I just realized that the query run not as expected because I didn't put parenthesis on the right spot, now I have fixed it as you mentioned and it's working well.
Thank you for your help.
Hi Theo,
Note that since @parameters are coming from OutSystems, not the database, they can never be "null". So testing for e.g. "@statusid is null" will always return False, and "@statusid is not null" will always be true.
Also note that you can't use "IN" with an input parameter that's not expanded in-line, so "CommitmentExternal}.[StatusId] in @statusid" will either return False or trigger an error.
Hello Kilian,
From what I read about expand in line, it can only be used for text, meanwhile @statusid data type is status identifier. So should I change it to {CommitmentExternal}.[StatusId] = @statusid instead for filtering?