help suppressing records..

[jaburke]

Hi,
I have a report where i want to only show certain records. Here's an example:

Table1 Table2
J280 J281
J281 J281
J285 J284
J286 J286

I want to only display the line with J285 and J284. If there is a match in Table1 to Table2, I dont want to display either record. For example, I want to suppress

Table1 Table2
J280 J281
J281 J281

because there is a row for J281 (Table2) that matches. But, I want to suppress both rows with the J281. Does this make sense? How can I do this?

Thanks!

 

[lupins46]

If there is a match then it is obvious how the 'line' would be created - even if you don't want it!
If there is no match then what constitutes a 'line' - why would you print J285 with J284 rather than J299?

 

[lbass]

You could use an equal join from Table1 to Table2 and then add an alias table1_1 and use a left join from Table2 to Table1_1.

Table1 = Table2 -lj->Table1_1

Then use the ID fields from Table1_1 and Table2 only.

-LB

 

[jaburke]

lupin - i'm not sure what you're asking me.

the joins you suggested didn't work for me - it was giving me the same results as doing a regular join - unless i was doing something wrong. i tried using the add command to write my own 'not exists' query and it totally blew up crystal. that is what i want to do. get the data that doesn't exist and then use it in a subreport to pull the rest of the data.

 

[lbass]

Sorry, I had it backwards. I'm not sure why there are duplicates in Table2, but if this is the data pattern, then do a left join FROM Table2 to Table1 on the ID field (the field that results in J###) and then add a record selection formula of:

isnull({Table1.ID})

-LB

 

[lupins46]

You say:
'I want to only display the line with J285 and J284'
What I am asking is why these two records should be on the same line.

If you have records in table1 J285, J287 and J302 which do not have matches in table2, and in table2 you have J200 and J286 which do not have matches in table1 why should any two records appear on the same line rather than just as a list with one on each line.

In any case if you want a list of records that only appear in one table you need to use a union query built from two left join queries

Select Table1.Keyfield as key, "1" as tbl from table1 left join table2 on table1.keyfield = table2.keyfield where table2.keyfield is null
Union
Select Table2.Keyfield as key, "2" as tbl from table2 left join table1 on table2.keyfield = table1.keyfield where table1.keyfield is null

 

[jaburke]

I ended up writing a stored procedure to do the 'not exists' and now i'm doing the second part.

Here's a new question. How do I suppress the details section of a main report if the subreport returns nothing?

Thanks for all the help!!!

 

[TedLiu]

Hi,

You can use shared variables which will be public to both main and sub reports.

In Subreport, create a formula xx:

Shared numberVar IsBlank;

If Isnull(yourfieldname) then
IsBlank := true
else
IsBlank := false
;

** insert the formula xx into subreport **

in Main report, section suppress formula:

Shared numberVar IsBlank;

whilePrintingRecords;
IsBlank;

No sure about the syntax because I didn't use it for long time, but it should work.

Hope it helps.

Ted