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...

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...

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?


Edited by ultraca - 22 Apr 2011 at 5:00am

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...

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!!