Conditional Outer Join

[kskid]

CR 8.5, Oracle DB

I have a question on conditional outer join.

I have 2 tables, Table_Name contains the person's name with vital information such as SNN and DOB. Table_ID contains various ID's with an ID_code and a common ID Number field that a person may have such as Driver's License (DL) , Insurance Number (IN), Passport number (PI), Employee ID (EI), etc.

The 2 tables are linked via a system-generated person_id number

I want to extract a group of people whose name begins with "A" and their driver's license (if one is present).

In SQL, I can write the following and get what I need.

Select distinct
Name,
ID_Number
from
Table_name,
Table_ID
where
Table_ID.ID_CODE(+)= 'DL' and
Table_ID.Person_id(+) = Table_name.Person_id and
Table_name like 'A%'

This resulted in all names starting with the letter A and optionally, their driver's licence.

Without placing the Table_ID.ID_CODE(+)= 'DL' in the Show SQL Query, is there a way of achieving this in CR without the use of subreports?

When the statement, Table_id.ID_Code = "DL", was placed in the select expert, then result were names with DL only and missing those names that did not have a DL

I also tried writing a formula for the ID number but that resulted duplicate records, one for each id record where ID_Code <> "DL"


//ID formula
if table_id.id_code = "DL" then
ID_Number
else
""

Any suggestions?

Thanks,

Larry

 

[lbass]

One approach would be to create a left join FROM {Table_Name.Person_ID} to {Table_ID.Person_ID} and then use a record selection formula like:

{Table_Name.Name} startswith "A"

Group on {table_Name.Person_ID} (or if you want the names in alphabetical order, group on a formula that concatenates name and ID) and then create a formula:

if isnull({Table_ID.ID_Code} or
{Table_ID.ID_Code} <> "DL" then "" else
totext({Table_ID.ID_Number},0,"")
//delete the totext( ,0,"") if the ID Number is already a string field

Then right click on this formula and insert a maximum. Drag this to the group header where you should display {Table_Name.Name} and then suppress the details.

-LB

 

[kskid]

LBass,

That worked but I forgot one important criteria. There may be more than one id number for a given ID_Code. For example, whenever a person is arrested, a mug shot is taken and assigned a Mug ID (ID_Code="MG"). Need to know all the MUG id's not just the max.

 

[lbass]

Then instead of using a maximum, allow the formula to display in the detail section. Then go to format section->details->and check "Suppress Blank Section". Then format the group header section to "Underlay following sections."

I think that will give you the display you want.

-LB