Hello,

I have had this issue with my datasource for years and had been working around the issue by creating an Access dB to connect to in addition to the ingres dB.

The slightly simplified version of the issue is that I have an ingress dB with a a tables called case_contact,  case_examination and case_location.

The case_location table has three columns: location_no, location_category and location_description.

The location_no is the number which is linked to from both the case_contact.location_no and case_examination.location_no fields however the numbers are not unique.  The case_location.location_category field contains a value of either 'EXAMINATION' or 'CONTACT' and this needs to be used to identify how the tables are joined.

I am not very sharp on SQL but I have been trying things like the following to no avail. 

SELECT case_referrals.*,cfm_locations_tbl.*
FROM cfm_locations_tbl
LEFT JOIN
cfm_locations_tbl
ON
cfm_locations_tbl.location_no = case_referrals.location_no AND cfm_locations_tbl.location_category = 'CONTACT'

I have been able to use a Command Select SQL statement to bring in two pseudo table. Using two statements as per below:

SELECT location_no, location_description, location_status, timestamp
FROM cfm_locations_tbl
WHERE location_category = 'CONTACT'

Then I link using expert and all looks good until I drop a field from either pseudo table onto the report.  This gives the message:  Failed to retreive data from the database. Delails: 5000R.[Ingres ODBC Driver][Ingres] API function cannot be called in the current state. [Database Vendor Code: 13172742].

I am hoping there is an easy way to go about getting the location_description for both "tables" onto the report.

So much grief from this dB I cannot wait for the Oracle migration. Thanks for your help