Use a parameter on a LEFT OUTER JOIN

[#1]

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'

[DellSC · Accepted solution]

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

[former_member731159 · Accepted solution]

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.

[former_member731159 · Accepted solution]

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

[former_member731159]

Dell,

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

Thank you so much,

Paul

[DellSC]

Hi Paul,

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

-Dell

[former_member731159]

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.