Join table based on a key and a condition
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=21623
Printed Date: 07 May 2024 at 3:16pm
Topic: Join table based on a key and a condition
Posted By: bowja
Subject: Join table based on a key and a condition
Date Posted: 30 Jul 2015 at 8:51pm
|
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.  ------------- If you think you can or think you can't you are right - Paraphrased quote Henry Ford |
Replies:
Posted By: kevlray
Date Posted: 04 Aug 2015 at 5:51am
| The issue is with the Ingres ODBC driver. Since we do not use any Ingres databases, I am not sure of it's limitations. |
Posted By: bowja
Date Posted: 04 Aug 2015 at 8:53pm
|
So much grief from this dB I cannot wait for the Oracle migration. Thanks for your help
------------- If you think you can or think you can't you are right - Paraphrased quote Henry Ford |