Left Outer Join Not Working

[bmacdo]

Using Crystal 11 & hoping I am in the right forum and this is coherent...(it's been awhile)

I am trying to create a comparison using a left outer join, which is not working. What I have forgotten is whether or not I can use any selection criteria on the outer joined table at all, even if I look for null values in the outer joined table key.

The first table is a list of possible parts we could have in types of machines. The second table is a list of individual machines with particular parts known to be in them. What I would like as my result set is a list of all the possible parts for a given type machine, whether or not they are installed in the given machine, along with matching parts information from the installed machine where the part number is found, like the following:

A.Part.Type A.PartNum A.PartName B.PartNum B.PartInstalledDate
VIDEOCARD VC111 Acme Vid Card
VIDEOCARD VC222 Bright Vid Card VC222 10/12/2012
VIDEOCARD VC333 XYZ Vid Card

I have printed out the parts list for a given machine type and for a particular machine and find in the Crystal report that I am getting some parts from both tables, but not all the rows I should be be getting from either side. I see no pattern in what’s not appearing.

I am creating the left outer join in the Select statement, something like this for a machine named NYC123:

SELECT Possible.PartNum, Possible.Part.Type, Possible.PartName , Installed.PartNum , Installed.Machine_Name, , Installed.PartInstalledDate
FROM dbo.Possible LEFT OUTER JOIN dbo.Installed ON Possible.Part_Number=Installed.Part_Number
WHERE Possible.Machine_Type=N'TypeA' AND (Installed.Machine_Name IS NULL OR Installed.Machine_Name=N'NYC123')

Thanks in advance for any thoughts and assistance,


Brad

 

[pmax9999]

Including a field from a Left Outer Joined table will generally over-ride the join type, so testing for NULLs needs to be included as you have done.

However, the NULL test should generally be right at the beginning. Accordingly, I would suggest you amend the Record Selection as follows:

(
    IsNull({Installed.Machine_Name}) or
    {Installed.Machine_Name} = 'NYC123'
) and
{Possible.Machine_Type} = 'TypeA'

Does this resolve the issue?

Cheers
Pete

 

[bmacdo]

Thank you, Pete. I can see this is the right direction for me to go in as it looks better with the machine type selection removed (but for getting extraneous machine types), but when I put machine type back in, it goes hinky again.

Brad

 

[pmax9999]

But I wasn't suggesting removal of the Machine_Type - it is still in the Record Selection. Are you saying it still isn't giving you the results you are looking for?

Pete

 

[bmacdo]

Sorry for the delay of my reply. Yes, I made sure the Machine Type was secondary to the NULL test and also taking out entirely, but the selection criteria did not pull all the records from the Possible parts list as it should.

Thanks for your assistance,
Brad

 

[pmax9999]

Please post your Record Selection formula.

Pete

 

[bmacdo]

Pete,

Sorry again for the delay. I was out on med. PTO last week. I am not allowed to use the literal table and field names or values in a public forum, but the current selection formula using aliases is as follows:

isnull({Installed.Machine_Name}) or
{Installed.Machine_Name} = "NYC123"

This is really baffling as I have not had such problems in the past with left outer joins and I appreciate any assistance.

Brad

 

[bmacdo]

For what it's worth, I tried running the same Select in MS-SQL Management Studio and got the exact same incomplete result set as I do in Crystal.

Brad

 

[PeterDimitrov]

What is your goal ?
To get all records where Machine_Name is not set or is equal to NYC123 ? Is NYC123 a hardcoded constant ?

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[bmacdo]

That is correct. "NYC123" is for testing purposes, an arbitrary single site. When I overcome my left outer join issue, I want to substitute it with a formula that will prompt for single machines. Someone else will run the report for those individual machines one by one.

I planned to create count and percentage formulas comparing the possible parts list to the installed parts list, e.g. of all the possible parts that are a parts type of "videocard", there is one match in the installed parts columns, or there is only one possible part number of "monitor" and there is one, same installed part number of "monitor," so that would be a 100% for that parts type, etc. I'm comfortable I know how to do the formulas once I can get the proper rows to work with.

Thanks for your consideration.

Brad

 

[PeterDimitrov]

Try SQLExpression. It might resolve your issue with the left join and will have much better performance. ISNULL is a crystal reports function and will be evaluated on the client side , which means that Crystal will download all records and filtered them locally. If you use SQLExpression Crystal most likely will be able create a server side where clause .

Here is an example for SQLExpression for SQLServer
SQLExpression name is "MachineName" and it looks like this:

ISNULL("Installed.Machine_Name",'')

The record selection formula will be:

({%MachineName}={?@MachineName} OR {%MachineName}='') AND {Possible.Machine_Type} = 'TypeA'

{?@MachineName} is a parameter

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.

 

[bmacdo]

Thanks very much! I am trying the above now and will report back.

Brad

 

[bmacdo]

Alas, I get the same incomplete rows.

Brad

 

[PeterDimitrov]

It will be helpful if you post the generated SQL.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports and SSRS.