Parent-child linking and pseudo null fields 1

[Krickles]

My report deals with three tables. Primary table = customer_order; Secondary table = delivery_times, Tertiary table = employee_times.

The links are:
customer_order.order_id to delivery_times.order_id &
delivery_times.employee_id to employee_times.employee_id

The problem occurs with employees who show delivery details but no clock-in time. If an error occurs with our automated time clocks, no employee information is included in the tertiary table. When this happens, the report drops the employee.

I have tried a right join, reversing links, and multiple formulas using isnull, not(isnull), and other functions. Since there isn’t actually a null, because no information about the employee is in the tertiary table, I can’t find anything that will work. My dream solution is to write a formula that will display the clock-in time or “No time” when no employee information is found in the tertiary table. Thanks in advance for any advice.

 

[AndrewBaines]

An outer join should work, then test for null in the formula - make sure the null test is first.
what's the database? Some ODBC connections don't support outer joins. Andrew Baines
Chase International

 

[Krickles]

I tried the outer join (right and left) and tested for null first. The record number is still skipped. Any other suggestions?

I am using CR 8.5 on Windows XP with a SQL Server ODBC connection to MS SQL Server 2000.

 

[kenhamady]

Make sure the arrows point from Order to Delivery and Delivery to Employee, then make the join between Delivery and Employee to be a left outer join. Last, make sure you don't have any employee fields in the select expert or the record selection formula. That would nullify the outer join. 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

 

[Krickles]

Ken, it worked! I didn't get the result I expected, but the null values do show on the report. Thanks.

Keith Rickles
krickles@bellsouth.net

 

[synapsevampire]

Et Tu Ken?

I had this discussion with Ngolem in another thread without success...

A Left Outer doesn't get nullified. You've just placed a where clause which "explicitly" requires records from the right side to meet a criteria. That same where clause against a SQL database should produce the same result.

To correct for this logic (meaning that you do want all of the left table rows except those outside of a criteria on the right table):

(isnull({MyRightTable.MyField})
OR
{MyRightTable.MyField} > 100000)

This example shows the Left Outer intact, plus allows for the where clause.

Note: I always place the isnull first in the record selection criteria, elsewise you may lose rows in CR - just another CR SQL bug to workaround. If you manually place the SQL into CR, then you can place the isnull 2nd with success.

<shrug>

fahgit abowt iiiit, it's Crystal.

Do you have findings contrary to this?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

BTW, this was tested again using Oracle natively and using CR ODBC. Your results may vary depending upon whatever it is that Crystal does and doesn't do with other data sources...

<fully disclaimerized grin>

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

Here is the problem. Your technique will work for parents with SOME children in the criteria, or NO children at all. It won't work if a parent has ALL children outside the criteria.

If you have a parent record with existing children - but NONE of the children are within your date range, then there is no NULL record to qualify, because the parent finds matches. However since all of those matches are outside the range, no records are returned for that parent. 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]

You lost me with:

&quot;but NONE of the children are within your date range, then there is no NULL record to qualify, because the parent finds matches.&quot;

You requested those within that range only, please explain why you'd expect to returen something other than those in that range? I asked the same thing of Jim and the thread was bascially terminated...

Your original logic sounded like:
show me children that match this criteria, and I expect to see all of the parent records that don't have children too, hence my isnull suggestion to counter your where clause criteria. Made sense.

Now it sounds like:
show me children that match this criteria, and I expect to see all of the parent records that don't have children too, AND the parents of those that DON'T meet this criteria. I suggest you not give it any criteria on the child, you'll get what you seek.

I think it's a question of logic, though it may just be mine

If you still disagree, please include a visual aid to explain your thoughts, such as example table data to be pulled that you believe *nullifies* an outer join.

Parent:

ID Text
1 Me
2 You
3 Everyone

Child:
Parent_ID Child_Date
1 1/1/2001
3 3/1/2001

If you explicitly ask for all records that have child_date 1/1/2002 in a left outer and use isnull preserving the parent records, you should get 1 and 2 back, not 3. There is a parent record for 3, but it is eliminated by your where. The LO was preserved, hence ID 2.

The relationship is fairly complex, but it doesn't become broken, changed or nullified any more than stating where 1 = 0 in a where clause nullifies an equal join, it's just filtering.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

I think we agree on what happens, but disagree on how to refer to it. I believe that I actually coined the phrase &quot;nullify the outer join&quot;. It is not a technical term, but is my way of saying, &quot;It is usually pointless to do an outer join if you have criteria on the outer table.&quot;

In my experience, 99% of users are trying to show all 3 people in your example, and they also want to show the activity within a specified time frame. This the pattern I find over and over:

1) When they first link the tables they only get two records, because of the equal join.
2) Then they change the link to an outer join, and they get all three records.
3) Then they put in their criteria and now they only get one record, having nullified the outer join with a criteria.
4) Then they try the IsNull criteria and they are back to 2 records, but not the three they want.

To get all three people, the usual goal of an outer join, you have to remove criteria from the outer table. 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]

I guess what you're saying is that most people don't know what a LO does. I'm no stranger to confusion surrounding complex SQL theory.

I was referring to this:

&quot;If you have a parent record with existing children - but NONE of the children are within your date range, then there is no NULL record to qualify, because the parent finds matches.&quot;

These were explicitly filtered matches you requested, it did it, you can get the non linked parents by using isnull(), yet you state &quot;It is usually pointless to do an outer join if you have criteria on the outer table.&quot;, when it's incomplete criteria that is the culprit. You can, just add the isnull() if you want the parents, the above illustrates it.

BTW, the use of the term nullify seemed OK here because it can render it meaningless if you want, the example and intent to NOT use a LO is what I question and take exception to. A 1=0 in the where can also nullify the LO, but it hasn't really nullified the LO, it's just filtered out records.

Children should be isnulled, not seen?

Sorry, been a long week...

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[synapsevampire]

Ken: I'd really appreciate an example where this does not work.

&quot;Here is the problem. Your technique will work for parents with SOME children in the criteria, or NO children at all. It won't work if a parent has ALL children outside the criteria. &quot;

I found time to test this in Access to emulate your environemnt, and it returned the expected records if:

-Matching the child criteria
-Not matching the child criteria
-No children at all
-No children matching the criteria

Did you mean some other condition?

I'm guessing that the worst case scenario is that there's additional criteria required, or that you really don't want criteria on the child.

A LO works, even with criteria on the child. Usually in SQL people do this with nested selects, but it can also be done in Crystal, at least I think you can, if not, I'd like to see when it fails.

thanks,

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

Just take the 5 records, in two tables that you listed above.

Now you want to always see ALL 3 parents on the report. But, you only want to see their activity for the month of March. If I were to use a LO join, and then put in the date range, I would lose 1 and 2. The IsNull could bring back 2, but not 1. To get 1 to show up you have to remove the date range and use a suppression formula. 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]

Ken: You stated you wanted March, why would you expect to see January parent records? AT first it was just see all of the parents that didn't have children, now the criteria changed, but it's still no a problem, you want conditional values returned.

But first, if you did always see all other parents of children, regardless of their value, that would be problematic I think, because that WOULD nullify your where criteria. That means that when you ask for March, you want March and all other records...

I wouldn't want that, nor does SQL do that, it has nothing to do with Crystal, your request isn't what a LO does. You need a different SQL statement to build a conditional column.

If you want all of those from CR as you decribe, then you'd have to either Union (Nooooooooo!) or explicitly state the conditional return values using a SQL Expression (i.e. in Oracle, use a decode to return what you want based on the child date, not sure of the equivalent in Access), and NOT place an explicit criteria on the child table.

SQL Expressions are also available in MS Access.

Make sense?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

What I have described is a VERY common scenario (in my experience) - people want all parents to show, but want a criteria on the children. That is why I try to clarify the effect of selection on the outer join.

People usually think a LO will solve the problem. I am just trying to make sure they understand that a LO - even with the IsNull() - doesn't do the job in this situation. I find this scenario (in my experience) MUCH more common than scenarios where an IsNull will solve the problem. You may not have had to deal with this requirement, but I can assure you it exists.

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, Ken.

It does seem to be a common misperception that SQL will divine that you want all parent records with null or zeroed child rows created when you explicitly state that you want just certain child records.

You can explicitly state what you want from the parent table, and it's returned. You can explicitly state what you want from child tables and it's returned. And in combination. If you state some criteria for the child, I would assume that's what you want, not also dish up things that don't have any criteria just in case you want those too.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com