Records Problem with no record match (Left Outer Join)

[dreman]

Using CR 8.5:
I have the following senario:
tables linked in the follwinfg manner
cust.table --> header_order.table--> detail_order.table
(linked by cust) (linked by invoice #)
(cust_no.cust = cust_no.header_order
and
inv_no.header_order = inv_no.detail_order)

Report prints the following:
customer info, header info (such as order date...), sum of invoice (from invoice_detail)
No problem, Reports prints all customers with sales However
I DO NEED TO SHOW CUSTOMERS WITH NO SALES
I tried using left outer (*=) but it is giving me a problem.
Any thoughts or advices will be very helpfull.
Thank you.
dré

 

[synapsevampire]

What sort of problem does it give you?

You already know part of the solution, a LO.

If it's just that you're not getting parent table rows, it may be because you state that their children must meet a criteria, add an

OR isnull({ChildTable.fieldname})

i.e.;

(
{ChildTable.fieldname} = 'Pick me'
OR
isnull({ChildTable.fieldname})
)

for any child table criteria.

I've had a few discussions along these lines here, it's a common misunderstanding with child tables.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dreman]

Yes you are right it is based on some calculation.
However , even if the record is skipped or does not fall in the criteria I do need to pick it. but do not know how ???
(ie: criteria: skip header_detail.tax='N'
In other word records could be skipped because they have tax= 'N' or doens't have a header_detail. How would I select the customer in either case
select ..... for header_detail<>'N' or
isnull(cust.header_detail)
it still will not select the record.
Please advise thank you.
dré

 

[synapsevampire]

&quot;However , even if the record is skipped or does not fall in the criteria I do need to pick it. but do not know how ???&quot;

If it's NOT in your criteria, you won't get it. If you need all PARENT records of criteria in a child table, than explicitly state the citeria that you want it to meet, hence the isnull() alternative.

It would help if you included your SQL (and specifics), the above is not valid nor the complete SQL.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

dreman,

Make sure that you don't have any fields from the header or detail table in your select expert or selection formula. These will nullify the effect of the outer join. The IsNull() suggested by synapsevampire will bring back in customers who have never placed an order. However if you have customers with one order that doesn't meet your selection criteria, the Outer Join with IsNull still won't include them.

synapsevampire - this is a good example of our discussioin in the other thread. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dreman]

Yes you are absolutely right, and this is my problem. I got to use the SQL in order to speed up the process.
As for My SQL statement, the where includes the following:
WHERE
p21_view_invoice_line.&quot;other_charge_item&quot; = 'N' AND
p21_view_invoice_line.&quot;tax_item&quot; = 'N' AND
p21_view_invoice_hdr.&quot;approved&quot; = 'Y' AND

and I think I need to exclude all the wher in order to bring all customers. But the disadvantage is it will slow the process. Any other advice, thank you.
dré

 

[synapsevampire]

Firstly, nothing las really been nullified, in effect it has, but if you request that the children have a specific criteria, that's what's returned, it does what you ask, it no more really nullifies the LO as does stating 1 = 0.

Isn't this what a LO does, and is supposed to do?

If you want all records from the parent, regardless of the child criteria, don't include the criteria for the child table.

If you want all of the parents without children AND children with a specific criteria, then explicitly state something akin to this:

(
isnull(p21_view_invoice_line.&quot;other_charge_item&quot AND
isnull(p21_view_invoice_line.&quot;tax_item&quot AND
isnull(p21_view_invoice_hdr.&quot;approved&quot
)

or

(
p21_view_invoice_line.&quot;other_charge_item&quot; = 'N' AND
p21_view_invoice_line.&quot;tax_item&quot; = 'N' AND
p21_view_invoice_hdr.&quot;approved&quot; = 'Y')
)

If the LO functioned in a manner where it automatically disregarded the child criteria and always returned the parents, yet only the child rows that matched the criteria (I guess you'd expect some default value for the filtered conditoin which might be either null or 0???), you'd be in deep Kim Chee because then you'd have to ALWAYS filter at the CR level if you did NOT want the parent records, whereas the way it works, you can do either.

Ken: I reposted the example solving what you believe to be the problem, I think that a misunderstanding of a LO is the culprit for most problems.

Now there are complications in the solution I describe, but I don't think I'll need to describe that.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

The complication is that many people want an option you aren't describing, and then don't understand why it doesn't work. I am just clarifying why it doesn't work. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

Where's the example of where it doesn't work?

The only condition I've heard is that you want rows with a child that has a certain criteria, and all parents, whether they have the criteria or not. Those are 2 different things, code accordingly.

A LO will NOT give you all of the parent rows if you explicitly code for the child, unless you also qualify rows for the parent.

What I think is needed is an example of how to convert a value from the child table to zero or blank or null if a certain child criteria is not met, and always return all of the parent rows.

Hence my other example post with an Oracle *Decode* in it.

The decode (or whatever your row level function is called to do the same) resolves the values you seek from the child, and returns all rows as expected.

It's a misunderstanding of what a LO produces, but the desired results can be attained using a LO.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

>>Rows with a child that has a certain criteria,
>>and all parents, whether they have the criteria or not

That IS the example I am referring to, just as I described in the other thread. It is quite common. Many people try to do this with a LOJ and don't understand why it doesn't work. People even suggest using the IsNull and don't realize that it won't help the person meet their requirement. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

<smiling>

I understand what you're saying, some want all rows, but add a criteria which is contrary, which adds to their general confusion.

And I guess that if they wanted only those records with parents that had a criteria on the child side or were null (but not those that don't match the criteria), I suspect that they'd think that a LO should do that too using the exact same methodology, then somehow the database would resolve how they need the where clause applied.

This thread stemmed from your suggesting that users NOT use a LO because the criteria will nullify the LO. I first addressed this by showing that with an isnull(), it will return the parents. The problem switched to it not showing those that explicitly don't match the criteria.

I don't understand why a user would think that they would get rows for things they explicitly state that they don't want? The solution IN CR is to still use a LO, and to fully qualify the where, meaning that it return all rows required, and that one of the columns is conditional (in lieu of a where on the child, because that's not how a where on a child works).

I'm sure that you understand this correctly, but our approaches to resolving are different. If the report will always have a small data set, then avoiding the where on the child makes sense, but if you have a large data set, the users/network/report will be best served by offloading the work to the database, not just applying a filter inside of the report.

My favorite definition of insanity is to repeat the same process over and over and expect a different result.

<glancing back up at our threads>

Yup, we're nuts.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

My suggestion (here and in the other threads) is that they still DO use a LOJ but that they DON'T use criteria on the outer table. Usually that means suppressing the details that they don't want within CR. This is not practical in all situations.

In my experience when a user says:

&quot;I DO NEED TO SHOW CUSTOMERS WITH NO SALES&quot;

as dreman did at the top of this thread, 9 out of 10 users really mean no sales within a selected reporting period or with some other criteria. That is whay you see a change in the requirement and I see this as the original requirement. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

OK. I give...

Use the LO without criteria means to not use it with to me.

It depends on the situation, Dremen apparently needed all customers (though he seemed uncertain based on &quot;I DO NEED TO SHOW CUSTOMERS WITH NO SALES&quot, and didn't understand a LO, so adding criteria to the child is a mistake.

That doesn't mean that users should disregard the power of passing through SQL in a LO because your experience is that many people don't get how to do this properly. Suggest that they fully qualify rows in the where. If they want ALL rows, then don't add a where, this just slows it, if they want a subset, fully qualify it.

People understandably respect your opinion because you understand the product very well, but please don't suggest to users in general statements that they disregard performance because it's easier to design, I make part of my living cleaning up that sort of thing.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[dreman]

Gee, I thought I would get an easier reply instead I got tons of replies.
I fully understand the LO and it seemed that my criteria using the where clause caused a problem, and by inquiring on my problem, I wan't it to see if there are easier way to resolve the issues.
Isn't what tek-tips is all about to discuss and discover new methods ?
Thanks
dré
By the way, End or open a new thread for this discussion.
Thank you.

 

[synapsevampire]

I thought we were discussing the method.

New and easier methods include doing this on the server side, or using a LO join and creating a conditional field in the select for the sum of invoice only for the specified criteria.

How you construct this conditional sum is either server based (using a SQL Expression), or within CR using some conditional sum, which will be slower.

Hope this is more helpful, and my apologies for harping on the point.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com