Comparing 2 files looking for records that do not match 1

[DaveMaastricht]

I am trying to find descrepencies between two files.

File A (F03b11). is left outer linked to file B (F0006).

I am trying to return the values from the primary File A. that do not exist in the Linked file B.

I have linked the field which I am trying to find the execption. File A (f03b11.RPMCU) to File B (f0006.MCMCU).

I have records in the primary file that I know do not exist in the secondary file. I have tried inputing into the Select Expert the following fomula which has not returned any results.

isnull({F0006.MCMCU})

any sugguestions
Thanks
Dave

 

[lbass]

On the surface it looks like you have done everything correctly. You might want to check your two places to make sure you do not have "Convert null values to default" checked. Go to file->options->reporting->uncheck "convert null field value to default" and also go to file->report options->and uncheck this setting, if checked.

Otherwise, try laying the fields on the report canvas and report back with a sample of your results. Did you get any error messages when you did the left join between {f03b11.RPMCU} and {f0006.MCMCU}?

-LB

 

[DaveMaastricht]

It still is not working, I reran the report after Unchecking the boxes as directed. I am trying to return two fields from file A. Document Number and Business Unit.

Dave

 

[lbass]

If you can verify that you have the left join FROM f03b11 TO F0006 then you should be able to use:

isnull({F0006.MCMCU})

...in your record selection statement. However, you cannot use any criteria in your statement that refer to the F0006 table or it will be treated like you are using equal join.

Saying that something doesn't work doesn't provide helpful feedback. If possible, explain the results you get or show sample data. It might help if you copied your selection formula into this thread. Go to report->edit selection formula->record and copy the formula and paste it here.

-LB

 

[DaveMaastricht]

this is what is showing in my SHOW SQL QUERY



SELECT
F03B11."RPDOC", F03B11."RPMCU",
F0006."MCMCU"
FROM
{ oj "WPD7333"."F03B11" F03B11 LEFT OUTER JOIN "WPD7333"."F0006" F0006 ON
F03B11."RPMCU" = F0006."MCMCU"}
WHERE
F0006."MCMCU" IS NULL



this is what came from the Edit Selection Record

isnull({F0006.MCMCU})


I did not receive any error messages on the Join, and the values I am expecting to be returned on the report are not being returned.

Dave

 

[DaveMaastricht]

The report is not returning any results if I use the ISNULL statement. If i do not use this statement all valid values are being returned from the F03b11 are being returned. Currently in the F03b11 there are records that do not match the f0006 in the MCMCU, therefore I would expect to see these records being displayed. I hope this clears up my previous statement. Thanks for the help.

 

[lbass]

As long as you have "convert null fields to default" unchecked in both locations, your approach should work. Are you sure you have nulls and not blanks?

Try this in your record selection formula:

isnull({F0006.MCMCU}) or
trim({F0006.MCMCU}) = ""

-LB

 

[DaveMaastricht]

I tried your new formula with still the same result. Here is the example of what I am trying to return. I am wondering if I truely am trying to return a NULL value?

in the f03b11 i have a Document # 307769 whose RPMCU is 1011. In the f0006 the MCMCU 1011 does not exist. So is it correct that the f0006 ISNULL? I am trying to return on the report.

Doc Business Unit
RPDOC RPMCU

Thanks for the help.

 

[lbass]

Yes, you are correct. Im stymied. If you remove your record selection formula and place {F03B11.RPMCU} and {F0006.MCMCU} on the detail section, what are the results you get for Document # 307769? {F0006.MCMCU} is a string, isn't it?

I would also try adding a formula:

len({F0006.MCMCU})

Then report back with whether the length is greater than 0. Although, the trim({F0006.MCMCU}) = "" should have addressed the issue of spaces added into the field...

-LB

 

[DaveMaastricht]

I am able to return the value if I Left outer join the F03b11 to the F0006, and in my select expert set Rpdoc = 307769. then my report returns the following.

Doc Business unit
RPDOC = 307769 RPMCU = 1014

If i add MCMCU to the detail section of the report this record no longer appears, and the report is blank. the same is true if i add any of the previous formulas to the report. any time I add MCMCU to the report through formulas the record no longer appears on the report. I have validated that the Convert null values... boxes are unchecked.

Dave

 

[lbass]

FO3B11 is your left table, and F0006 is your right able, with the left join FROM F03b11 TO F0006, right? What happens if you remove all fields from F03B11 and only add the MCMCU field? Does it display, and if so, does it display the same way as RPMCU from the other table?

Another thought. In some cases, changing the "Options" section doesn't take hold until you design a new report. You might want to open a new report after removing the check mark on "convert null fields to default values", and see if you get the same behavior.

Also, please go to database->verify database and make sure your tables are up to date.

-LB

 

[DaveMaastricht]

Thank you so much for your help!!!!! After all of this your first post was accurate what was tripping me up was SECURITY of all things. Apparently I did not have access to "BLANK" values in the F0006. So when I tried to return a blank MCMCU security prohibited me from viewing the record. That has now been corrected and this works wonderfully.

Again thanks for the help.

Dave