SSN/Name Comparisons

[twilliamson2]

Hi,

I am using CR10 with an Oracle database. I need help with,

-figuring out how to check employees with the same name and different SSNs

-figuring out how to check employees with different names and the same SSNs

I have to do the above comparisons within two different databases.

Do you have ideas on how to do the comparison? Can you share sample formulas?

Thank You!

 

[lbass]

If the databases (here shown as separate tables, although they could be separate owners in the From clause) are on the same server, you could potentially do a union statement to combine the databases, by using the 'add command' option as your datasource as in:

Select 'Table1' "whichtable", "table1"."name","table1"."ssn"
From "table1"
union
Select 'Table2' "whichtable", "table2"."name","table2"."ssn"
From "table2"

Then you could create a string parameter {?groupby} with options "Name" or "SSN". Then create a formula {@grp}:

select {?groupby}
case "Name" : {command.name}
case "SSN" : {command.SSN}

Insert a group on {@grp} and then go to report->selection formula->GROUP and enter:

(
{?groupby} = "Name" and
distinctcount({table.SSN},{@grp}) > 1
) or
(
{?groupby} = "SSN" and
distinctcount({table.name},{@grp}) > 1
)

Run the report twice, once for SSN dupes, and once for name dupes. Add {command.whichtable} to the detail section so you can identify which table the results are coming from.

-LB