Linking used to work...
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=12989
Printed Date: 23 Apr 2025 at 1:06pm
Topic: Linking used to work...
Posted By: ultraca
Subject: Linking used to work...
Date Posted: 21 Apr 2011 at 6:38am
|
So last week I spent hours creating a report. I pulled in two tables from my DB and linked two fields with different names (inner join + not enforced + '=') and everything worked fine. Now that I am trying to run the report again I get "Failed to retrieve data from the database" with the following popup:  My two table.fields are cr.id and cnt.assignee which both contain a users uuid. If I do a left or right outer join I get ERROR:[DataDirect][OPen Access ODBC]parse error at open_date...{ No clue what changed or what I inadvertently did to mess this up... |
Replies:
Posted By: hilfy
Date Posted: 21 Apr 2011 at 6:54am
|
I would try this:
- Delete the link between the two tables
- Close the Database Expert (you'll get a message about the tables not being linked, which you can ignore). This will regenerate the SQL for the report.
- Open the Database Expert again and recreate the link. You do NOT have to make any changes to the properties of the link.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: ultraca
Date Posted: 21 Apr 2011 at 7:49am
|
Hmmmm no dice If it helps... Table 1 (cr) contains Field 1 (assignee) cr = list of tickets and details assignee = uuid identifier of the assigned user Table 2 (cnt) contains field 2 (department_name) and field 3 (id) cnt = list of users and their details department_name = department belonging to user id = uuid identifier of user cr.assignee = cnt.id There are other similar fields whose data match up and I get the same error using those as well. My design has the following: cr.ticket_num, cr.assignee and cnt.deparment_name I feel like I am doing this right, but theres some underlying issue in CR... |
Posted By: hilfy
Date Posted: 21 Apr 2011 at 8:02am
|
The error that's being returned is specifically a database error - it's not a Crystal error. However, that doesn't mean that Crystal hasn't done something screwy.
View the SQL query in Crystal. What type of database are you connectin to? Do you have a tool you can use to query your database directly outside of Crystal? If so, you could copy the query from Crystal and paste it into tool to run directly against the database. That's the best way to troubleshoot a query.
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: ultraca
Date Posted: 22 Apr 2011 at 4:26am
|
So its bombing out on the bolded part, SQL says "Invalid object name 'mdb.casd.cnt'." SELECT "cr"."assignee_combo_name", "cr"."status", "cnt"."department_name", "cr"."open_date" FROM "mdb"."casd"."cnt" "cnt" INNER JOIN "mdb"."casd"."cr" "cr" ON "cnt"."id"="cr"."assignee" WHERE "cr"."status"='OP' ORDER BY "cnt"."department_name", "cr"."assignee_combo_name" I'll update this reply in a bit, I am looking for some more info about something else I noticed on my end... EDIT (I thought Fridays were suppose to be enjoyable?) So a little more info. The query above is CR's version of the query, but the table/field names are somehow converted. I went through the query and rewrote in SQL's query analyzer using the matching table/field names and this is what it looks like, however the results are in binary value so its nothing but codes and numbers. I could put in extra syntax into the SQL but thats not where my problem is. SELECT "call_req"."assignee", "call_req"."status", "ca_contact"."department", "call_req"."open_date" FROM "ca_contact" "ca_contact" INNER JOIN "call_req" "call_req" ON "ca_contact"."contact_uuid"="call_req"."assignee" WHERE "call_req"."status"='OP' ORDER BY "ca_contact"."department", "call_req"."assignee" The problem is that its no longer working in CR and I need to figure out what changed. I saved my report w/ data when it was working so I at least know I didnt imagine that it worked. Thoughts on next steps? |
Posted By: hilfy
Date Posted: 25 Apr 2011 at 5:29am
|
It looks like your table names have changed. On the Database menu in Crystal, go to Set Location. Point the "cnt" table to (I assume) ca_contact and "cr" to call_req. Remember to click on the "Update" button after setting each of the relationships. This will then help you map the old field names to the new ones.
Once this is complete, go to the Database Explorer and change the table names (they'll still be the old table names) to the new ones by right-clicking on each and selecting "Rename".
-Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: ultraca
Date Posted: 25 Apr 2011 at 7:03am
|
I made an attempt at your instructions, but I dont think it will work, but that may also be because of my lack of experience... Here is why. In SQL our db name is MDB and contains tables such as ca_contact, call_req, usp_contact. From Crystal the connection to the db uses what the application vendor calls a Factory view that combines ca_contact with usp_contact to create a new table called CNT. Additionally call_req is also combined with usp_contact to create a new table called CR. This combination does magical conversion of uuid's to names. I could create a new connection in CR that reads the SQL format but then I would need to manually do conversions that was taken care of using the Factory view... |
Posted By: hilfy
Date Posted: 25 Apr 2011 at 7:26am
|
So, the two queries - the one from Crystal and the one that you wrote are NOT doing the same thing, so that is not a valid test. Does the query from Crystal run in the database? -Dell------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: ultraca
Date Posted: 26 Apr 2011 at 7:31am
|
They were doing the same thing, but with different names/database views. I actually gave up, rebuilt the entire thing from scratch using the raw sql view instead of the 'factory' view from the vendor. It took more time to do the conversions but it is actually working now. I am still irritated that this happened :( I really do appreciate the assistance with this hilfy!! |