Comparisons? 1

[sammybee77]

HI All,

If tableA = TableB (+) (TableB has multiple rows) I want to find all records in A where TableB.Field1 = 12 but <> 15. The problem is that for every ID of 12 there should be another ID of 15 I want to find those that don't have a match. E.g. For the row in Table A , Table B may contain 5 ID's of 12 and 4 ID's of 15 So I would want that to appear on the report, but if Table B contained 5 IDs of 12 and 5 ID's of 15 I wouldn't want that row to appear in the report.

Any Ideas?

Many thanks

Sam

 

[Naith]

I don't know what database you're using, but the painless way to do this would be to handle it on the database. In the case of an Oracle database, for instance, you'd use a minus or not exists between two select queries - one for 12 and one for 15.

The difference between the two is what goes into the report.

Doing this in Crystal - and if you have a weak disposition, look away now - you would have to use a subreport to bring back all the 12 and 15 rows, passing a count of 12 vs 15 per group of TableA.Field1 to shared variables, and then calling the variables in the main report.

As you can no doubt imagine, that wouldn't be very sexy at all - so I reiterate my vote for doing this in sql.

Naith

 

[dgillz]

Create two formulas:

@12check
If {Field1}=12 then 1 else 0

@15check
if {Field1}=15 then 1 else 0

Sum these two formulas by whatever field it is in table 1 that makes it a unique record.

Then in your GROUP (not record) selection formula enter the following:

Sum({Field1},{@12check})<>sum({Field1},{@15check})

This will give you everything where the numbers of 15s and the number of 12s are not the same. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[Naith]

Beautifully simple, dgillz. How it hadn't occured to me, I'll never know.

 

[dgillz]

Its early Naith. Software Training and Support for Macola, Crystal Reports and Goldmine
714-348-0964
dgilsdorf@mchsi.com

 

[sammybee77]

Superb - Cheers!!