Do I need an array for this? 1

[AZdesertdog]

CRXI-ORACLE 9

I'm trying to write an exception report that for each grouping compares characteristics of column A with those of Column B (and vice-versa). The goal is to find out when the values in one column do NOT exist in the other. Example data would be:

GP 1> ID12345
Col A Col B
OLDACCT PROJCODE
OLDACCT PWNONPAR
OLDACCT PWREFUSE
OLDACCT TYPEUSER
PWNONPAR PROJCODE
PWNONPAR PWNONPAR
PWNONPAR TYPEUSER

With this data just need the ID flagged because one of the items in Col A (OLDACCT) does not appear in Col B and many from Col B don't appear in Col A (PROJCODE,PWREFUSE,TYPEUSER,PROJCODE)

GP 1> ID21234
Col A Col B
PROJCODE TYPEUSER
PWNONPAR PWREFUSE
PWREFUSE PWNONPAR
TYPEUSER PROJCODE

For this group there is no problem and I don't need to see it as each value exists in both columns.

Any help appreciated.

-DDog

 

[lbass]

You could handle this by using a command as your datasource:

select 'ColA' "whichcol", "table"."colA" "col", "table"."ID"
from "table"
union all
select 'ColB' "whichcol", "table"."colB" "col", "table"."ID"
from "table"

In the main report, insert a group #1 on {command.ID} and a group #2 on {command.col}. Then go to report->selection formula->GROUP and enter:

distinctcount({command.whichcol},{command.col}) < 2

Drag the Group #1 groupname into the group #2 header.

-LB

 

[AZdesertdog]

That works! Thanks, LB.