Left outer join doesnt return all records 3

[cubeblock]

FIrst I must explain that I am very new to crystal 9.0

I am trying to write a simple report from our trouble ticket system using two tables.

The Priority.table contains 3 values 1,2 and 3 in the priority field.
The Ticket.table contains a call number field, date field and priority field.

I have linked the priority.table to the ticket.table using a Left Outer Join on the priority field of each table.

The report uses the following fields

priority.table priority
ticket.table call number
ticket.table date
ticket.table priority

The report is grouped on priority.table priority

The report then outputs all the trouble ticket numbers grouped by the priority value as I would expect.

If I set a filter restricting the date range using the date field of the ticket.table the report only returns the ticket numbers where the value 1,2 or 3 have been used. If there are no priority 1 tickets, nothing is shown.

I need the report to show the number of tickets against each priority and if there are zero tickets I need it to return the value of 0.

I can't see where I am going wrong with my logic

 

[Turkbear]

Hi,
Any criteria applied to the Right-most table will convert a Left Outer Join to an Equi-Join and that explains your issue..

You could place the ticket.table data in a subreport, I believe and link it thru the priority field.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cubeblock]

Thanks for the quick reply. Alas this is a subreport of a main report and I know of no way of getting a subreport into a subreport of the main report.

 

[Turkbear]

Hi,
(Note: It would have helped to know that)
You cannot (Crystal does not allow nested sub-reports).


Try this:
Create a Command Object that restricts the ticket.table by the date range you need ( can be a parameter ) - use this, instead of the ticket.table, as the link to the priority table and the Left Outer join should do what you want.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Madawc]

You can do a test on the subject of a Left-Outer if you start with an IsNull. At least I have got it to work on occasions.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You could use a command like the following which would preserve the left join:

select priority.`table priority`,
ticket.`table call number`,
ticket.`table date`,
ticket.`table priority`

from priority left outer join ticket on
priority.`table priority` = ticket.`table priority` and
ticket.`table date` >= {?StartDate} and
ticket.`table.date` < {?EndDate}+1

The trick is to add the criteria in the 'from' clause instead of the 'where' clause. You might have to add parens after the "on" enclosing the rest of the statement, depending upon your database, and of course you need to adjust the punctuation to that of your database.

-LB

 

[kenhamady]

Madawc,

There is a reason why the IsNull only works some times. It works only when you have a primary record with no matches at all in the outer table. The IsNull will pick those up.

If the primary record does have even one match in the outer table, but that match doesn't meet the select criteria, then the IsNull won't help maintain that record. Because there is a match nothing is Null. And because the match doesn't meet the filter criteria the composite record is skipped over.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[mercurymik]

Sometimes when the field of the left outer join is used in the selection criteria I have had to include a statement like:

{Joinfield} = {?parameter} or IsNull({Joinfield})

or when trying match the field I have had to use this:

({LeftJoinTableField} = {JoinField} or
IsNull({LeftJoinTableField}))

 

[kenhamady]

mercurymik,

You might want to read my 5/13 post, the one right above yours.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[cubeblock]

Thank you for the examples that are being posted. Report writing is not my area of expertise so the actual examples being posted are very helpful for me to try and muddle through to a solution.