Record Selection on right side of Left Outer Join causes Inner Join? 2

[RustyAfro]

Crystal XI, DB2 Database.
When I need a table on the right side of a left join to filter, I input the criteria in the join itself so that I do not force an Inner Join.

LEFT OUTER JOIN PRED.OPERATOR_PROPOSAL OPERATOR_PROPOSAL ON  PROPOSAL.PROPOSAL_ID = OPERATOR_PROPOSAL.PROPOSAL_ID  AND [B]OPERATOR_PROPOSAL.SEQUENCE_NUM = 1[/B]

In Crystal, the Select Expert puts the criteria in the "WHERE" portion of the SQL:

[B]WHERE OPERATOR_PROPOSAL.SEQUENCE_NUM = 1[/B]

This seems to force the database to return the data as an Inner Join, only showing records that have a match on both sides.

Is there a way to just filter the results in Crystal XI after the database has returned it's recordset so that nulls on the right side don't suppress the rows on the left side??

Thanks,

Joel Seguin

 

[synapsevampire]

Yep, that's how Crystal constructs the SQL.

So you cn select Add Command under your data source and put the real SQL in there as the data source for the report.

Alternatives are to create a View of th table with the criteria within, or within Crystal you can create a subreport for the right side table and filter it in there, and then return values using shared vairables, but this is the slowest solution.

-k

 

[lbass]

One way of ensuring the display of all values from the left hand table is just to control the display of fields from the right hand table by using conditional formulas like:

if isnull({OPERATOR_PROPOSAL.SEQUENCE_NUM}) or
{OPERATOR_PROPOSAL.SEQUENCE_NUM} <> 1 then 0 else
{OPERATOR_PROPOSAL.SEQUENCE_NUM}

-LB

 

[synapsevampire]

What LB means is to use conditional suppression using a formula instead of adding the criteria to the right side table.

The Command Object is probably your best bet.

-k

 

[lbass]

No, I didn't mean that.

-LB

 

[synapsevampire]

Did you mean to create formulas for every field displayed from the child table, and in that example setting a value incorrectly to zero if it is not in the child table?

If so, it's similar to adding the criteria to suppressing
the fields, but you're setting a value to 0 wouldn't be correct anyway, right, it should be null.

So in your example you should change to conditional suppression for each field:

isnull({OPERATOR_PROPOSAL.SEQUENCE_NUM})
or
{OPERATOR_PROPOSAL.SEQUENCE_NUM}

So it either displays the results or it's null, which is the case.

Just guessing here, genrally you have very clever solutions, but I think in this case it's incorrect.

Since you didn't elaborate on your intent, I had to guess of course...

-k

 

[lbass]

As far as I can see, nothing incorrect here--the only step I omitted from my post was suppressing if zero. Not sure if the OP was looking to display anything more than the sequence number, but I suppose using conditional formulas for each field is more cumbersome than using the same suppression formula consistently for all fields from the right hand table:

isnull({OPERATOR_PROPOSAL.SEQUENCE_NUM}) or
{OPERATOR_PROPOSAL.SEQUENCE_NUM} <> 1

However, in terms of the overall report display, using the conditional formula method returns only two rows per value in the first table, and I've found this makes it easier to resolve row inflation issues when using multiple tables with left joins.

-LB

 

[lbass]

I shouldn't have said "returns only two rows"--the number of rows doesn't change. I meant "returns only two values".

-LB

 

[synapsevampire]

Suppressing if a field doesn't have a value (they could be numeric, string, date, etc.) and using formulas means doing the same thing twice is my point, and I don't get why you prefer to double code this...

-k

 

[RustyAfro]

lbass/vampire, thanks for posting, I have seen a lot of good stuff from you guys here. I believe I understand and want to elaborate a little on the problem.

{OPERATOR_PROPOSAL.SEQUENCE_NUM} is in a child table that stores sales person's names that are assigned to the record in the main table. If the SEQUENCE_NUM is equal to 1, then that name belongs to a Executive, if 2, a Manager, and if 3, then a Rep.

So for each main record, I could potentially get 3 rows returned. In SQL, I left join the table 3 times to the main table, but in the join itself indicate the SEQUENCE_NUM should be 1, 2, and 3 respectively for each copy of the table. It's inefficient at best but it's how the dbas designed it

I have been able to input the SQL in a command and it's fine, but I am trying to stay away from commands as I am the only programmer in the department and if changes need to be made by other staff, they wouldn't know a thing about SQL. Also, I really like SQL expressions, multi-selection parameters and dynamic cascading parameters which I lose if I have even one simple command in my data source.

lbass, it seems then that I would make a named formula object for each user type like this? Or maybe a SQL expression? I am assuming SQL wouldn't repeat 3 rows if I put the objects into a grouped header....let me know if I am on the right track or if there is a way to do this more efficient in the Record Selection formula....

If {OPERATOR_PROPOSAL.SEQUENCE_NUM} = 1 Then {User_Name}

Thanks again :-Q

Joel Seguin

 

[synapsevampire]

Just use a suppression of:

{OPERATOR_PROPOSAL.SEQUENCE_NUM} <> 1

If you use the grouping technique and a formula, you'll still need to use suppression as well, because you may not have a sequence = 1, which was the point of my post.

If you always have a sequence = 1, then group and place the fields in the group header and suppress the details and group footer, no formulas required.

-k

 

[RustyAfro]

Sorry Vampire, I guess I just don't understand the terminology "suppression". Does the {OPERATOR_PROPOSAL.SEQUENCE_NUM} <> 1 get placed in the record selection formula? Again, I am really new to Crystal, so a lot of this is foreign to me <

 

[synapsevampire]

No worries, suppression is a technique in crystal to not display certain rows after they are in the report.

Right click the section the field is in and select format section->X2 next to suppress and place the formula there.

-k