I recently had a request to enhance a crystal report (8.0) that shows
what parts we manufacture and filters via the "Like" statement in the
query on the part number for a particular part number format. The
origional report runs in 5-10 minutes and starts sending records
immediately when run. The request was to add Total Labor hours and
Setup Hours for the most recent Job completed for that part number.
The original report used the PART table and the PARTPLANT table. In
reviewing the request, I found that I would need to include the JOBHEAD
table and the JOBASMBL table. JOBHEAD gives me the JobCompletionDate,
and JOBASMBL gives me the TotalLaborHours data. But the problem occurs
when I attempt to join the new tables onto the existing report. In
fact, any attempt to join these tables together seems to result in a
hung up report (still running with no return after 6 hours!). My last
test was an attempt to see what might be hanging the tables up, and I
wound up only linking two base tables... Shown Below:
(Left Outer Join, as I may have parts with no jobs!)
PART JOBASMBL
-------------- -----------------------
Company ----------------> Company
PartNum ----------------> PartNum
TypeCode JobComplete
TypeCode = "m"
Not PartNum = "---1"
JobComplete = True
It seems to me that this basic join fails, yet there seems to be an
index in the database that supports it for each table.
PART table index = TYPEPART: Company,TypeCode,PartNum
JOBASMBL table index = CMPLPART: Company,JobComplete,PartNum
I even removed the explicit "LIKE" format for the base test and used a
dummy part number. Basically, I should get back EVERYTHING from the
table! Oddly enough, if I make the JOBASMBL table the leftmost table
and join it to the Part table, then the report runs as expected; it
starts sending records immediately.
Any ideas??
Andrew Reed
Office Automation Specialist
Crestview Aerospace Corporation
[Non-text portions of this message have been removed]
what parts we manufacture and filters via the "Like" statement in the
query on the part number for a particular part number format. The
origional report runs in 5-10 minutes and starts sending records
immediately when run. The request was to add Total Labor hours and
Setup Hours for the most recent Job completed for that part number.
The original report used the PART table and the PARTPLANT table. In
reviewing the request, I found that I would need to include the JOBHEAD
table and the JOBASMBL table. JOBHEAD gives me the JobCompletionDate,
and JOBASMBL gives me the TotalLaborHours data. But the problem occurs
when I attempt to join the new tables onto the existing report. In
fact, any attempt to join these tables together seems to result in a
hung up report (still running with no return after 6 hours!). My last
test was an attempt to see what might be hanging the tables up, and I
wound up only linking two base tables... Shown Below:
(Left Outer Join, as I may have parts with no jobs!)
PART JOBASMBL
-------------- -----------------------
Company ----------------> Company
PartNum ----------------> PartNum
TypeCode JobComplete
TypeCode = "m"
Not PartNum = "---1"
JobComplete = True
It seems to me that this basic join fails, yet there seems to be an
index in the database that supports it for each table.
PART table index = TYPEPART: Company,TypeCode,PartNum
JOBASMBL table index = CMPLPART: Company,JobComplete,PartNum
I even removed the explicit "LIKE" format for the base test and used a
dummy part number. Basically, I should get back EVERYTHING from the
table! Oddly enough, if I make the JOBASMBL table the leftmost table
and join it to the Part table, then the report runs as expected; it
starts sending records immediately.
Any ideas??
Andrew Reed
Office Automation Specialist
Crestview Aerospace Corporation
[Non-text portions of this message have been removed]