Can't restrict on a sometimes null field

[TJ1776]

Can't restrict on a sometimes null field

Crystal Reports 11
MSSQL 2000 DB

Vehicle maintenance database:

Attempting to write a two-table report that will show vehicle numbers (from Table1) and a *subset* of vehicle inspections (from Table2). Not all vehicles in Table1 have inspections listed in Table2, but my report needs to show all vehicles from Table1 along with a subset (if any) of inspections listed in Table2. Example:

Table1:
vehcile_number
3556
3557

Table2:
vehicle_number Inspection
3556 STATE
3556 EMISSONS
3556 SAFETY

I've joined Table1 and Table2 on vehicle_number with:
Join Type: Left Outer Join
Enforce Join: No Enforcement
Link Type: =

Restrictions:
With no restrictions, output looks as expected, below.

vehicle_number Inspection
3556 STATE
3556 EMISSIONS
3556 SAFETY
3557

However, I want to restrict SAFETY inspections from the report. When I restrict thusly: Inspection <> "SAFETY", the SAFETY inspection is restricted from the report (OK), but the vehicles with *no* inspecitons are also restricted from the report as below (NOT OK).

vehicle_number Inspection
3556 STATE
3556 EMISSIONS

It's missing vehicle number 3557 (with NULL value in the Inspection field)

How do I write the restrction so that the SAFETY inspection is excluded while including those vehicles with *no* inspections? Any help is greatly appreciated.

Jeff

 

[lbass]

Instead of using a record selection formula, create a formula {@inspection} in the formula editor->new:

if isnull({table2.inspection}) or
{table2.inspection} = "SAFETY" then
"" else
{table2.inspection}

Use this instead of your inspection field on the report. You can then suppress the main report vehicle number when there are other inspections types for a vehicle number by using:

{table2.inspection} = "SAFETY" and
distinctcount({@inspection},{table.vehiclenumber}) > 1

...assuming you have a group on vehiclenumber.

-LB

 

[TJ1776]

Thank you, LB. I got this formula solution that worked from another site:

(if isnull({Table2.inspection}) then true else {TABLE2.inspection} <> "safety")

Thanks much for your response.

Jeff

 

[lbass]

Jeff,

If you are using that as your record selection formula, you will lose any vehicle numbers that ONLY have one record = "safety", since it is neither null or <> "safety".

There is a way of selecting in a command where you build the criteria into the from clause which would preserve the left outer join. Let me know if you are interested in that route. The command would have to be your sole datasource.

-LB

 

[TJ1776]

LB,

Yes, I would definitely be interested in the scenario you point out for vehicles that have only one record = safety. This is a big (and helpful) learning experience for me. Please advise.

Jeff

 

[lbass]

You can use my earlier suggestion, or you can write a command to use as your sole datasource, like this:

select table1.vehno, table2.inspection
from (table1 left outer join table2 on
table1.vehno = table2.vehno and
table2.vehno <> 'Safety'
)

The trick is to put the criteria in the from clause instead of the where clause.

-LB