Question Re: Use a parameter on a LEFT OUTER JOIN in Technology Q&A

Use a parameter on a LEFT OUTER JOIN

former_member731159 — Mon, 10 May 2021 15:21:23 GMT

Hello,

I have a parameter that I'm passing to a sub report. I would like to use the parameter on the LEFT OUTER JOIN line instead of the WHERE area.

Example:

Reference "B"

SELECT * FROM A

LEFT OUTER JOIN B

ON (A.Id = B.Id)

LEFT OUTER JOIN C

ON (A.Id = C.Id)

LEFT OUTER JOIN D

ON (A.Id = D.Id)

WHERE parm = 0 AND

r = 12345 AND

s = 'abc' AND

t = 'nn'

Instead do this

SELECT * FROM A

LEFT OUTER JOIN (Select * FROM B WHERE Name = parm) B

ON (A.Id = B.Id)

LEFT OUTER JOIN C

ON (A.Id = C.Id)

LEFT OUTER JOIN D

ON (A.Id = D.Id)

WHERE

r = 12345 AND

s = 'abc' AND

t = 'nn'

Re: Use a parameter on a LEFT OUTER JOIN

DellSC — Mon, 10 May 2021 17:40:10 GMT

To do this you need to go to the Select Expert, show the formula and then manually edit the formula. You'll add something like this to the formula (NOTE: the parentheses are crucial for getting this to work right!):

(
  IsNull({b.Name}) OR
  {b.Name} = {?Parameter}
)

Since this is in a subreport, you'll create the parameter in the subreport itself. Then in the Subreport Links, you'll

1. Select the field or parameter from the main report in the top left.

2. UN-check "Select data in subreport based on field:" in the bottom right.

3. Select the subreport parameter that you created in the bottom left.

4. Click on OK.

This will give the parameter in the subreport the value that it needs to filter correctly.

-Dell

Re: Use a parameter on a LEFT OUTER JOIN

former_member731159 — Mon, 10 May 2021 18:27:05 GMT

When I do this and then do a show query, the logic still comes up at the end of all of the LEFT OUTER JOIN statements. The logic is not placed into one of the LEFT OUTER JOIN statements. I need to filter one of the tables in one of the LEFT OUTER Join statemetns. Every filter that I try goes to the bottom of the whole SQL expression.

Re: Use a parameter on a LEFT OUTER JOIN

former_member731159 — Mon, 10 May 2021 18:52:24 GMT

Ok, disregard the last comment. I see what you are doing now. The isNull is allowing null values to pass through, which means that we get data when there is no match. I testing it now.

Re: Use a parameter on a LEFT OUTER JOIN

former_member731159 — Thu, 13 May 2021 16:27:55 GMT

Dell,

Thanks for showing me a way to get around this problem. Your solution works perfectly.

Thank you so much,

Paul

Re: Use a parameter on a LEFT OUTER JOIN

former_member731159 — Mon, 14 Jun 2021 14:31:42 GMT

Hello Dell,

The solution works fine, but I have a twist on the problem. This solution works when the fields in the "ON" clause do not match, causing a NULL value. What if the fields in the "ON" clause do match, but you want to filter on other fields that are not in the "ON" clause. When I have multiple LEFT OUTER JOINS and this one LEFTER OUTER JOIN does not have the necessary data, it causes the whole query to pull no data, when there is data is present.

Thanks,

Paul

Re: Use a parameter on a LEFT OUTER JOIN

DellSC — Fri, 25 Jun 2021 17:07:21 GMT

Hi Paul,

Please mark my answer as the correct solution in order to close out the question. Thanks!

-Dell