want to read same field twice, select different values 1

[TRNCOLS]

ORacle 8.05 database
CR 8.5

I am having difficulty designing report that doesn't
take 10 minutes to run.

I am selecting values from a table that records processes, in this case issuance of a permit. First instance of the field shows okay to issue. later the same field will show that permit actually issued. Ex:

ACTION TABLE:
Permit, Date, Process
10000, 07/01/2001, ISSUANCE
10000, 07/10/2001, ISSUED

I am trying to find all permits that have value ISSUANCE within parameter date range but have not been ISSUED. Currently, selecting where (Date in date to date and
process = ISSUANCE) or (process = ISSUED). Then using formula to evaluate (@issued): if process = ISSUED Then 'No Print' Else 'Print'. in select statement Minimum ({@issued}, {@Permit}) = "Print".

takes so long to complete (10 minutes) & seperately can get this info in less than 30 seconds. Is there an alternative to this method?

 

[MalcolmW]

I think I've got it, but I'm rushing to get to dinner...
I'll just put it in pseudo SQL for simplicity,
SELECT T1.*
FROM ISSUANCE AS T1
LEFT OUTER JOIN ISSUED AS T2 ON T1.permit = T2.permit
WHERE T1.date in DateRange and
T2.date >= StartDate and
T1.process = 'Issuance' and
T2.process = 'Issued' and
T2.permit IS NULL
You can build that fairly easily in CR. The last line of the WHERE might have to use a SQL Expression field though.



Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[TRNCOLS]

Thanks permit can't be null value. I asked Crystal and it said my way is the most efficient way. However, this report was orginally created in CR 6. when I upgraded it to 8.5 and reparsed the select statement, processing time dropped. Also, report was slow because all this was put in swap space on client to do last pass. Since serving up via ASP so web server swap space better.

 

[MalcolmW]

Sorry about the first post - should have eaten dinner first...it had a few flaws.
Something like this should be really fast on Oracle, but it would require going "outside the Crystal box", by setting up a simple stored proc that ensure that the processing gets done on the server. I presume you would want to pass the dates as parameters.
If this is not for distribution, then you could also consider just writing the SQL with the Crystal SQL Designer.

SELECT T1.Permit, MAX(T1.ProcessDate) AS ProcessDate, MAX(T1.Process) AS Process
FROM #Action AS T1
WHERE
T1.ProcessDate >= '07/01/2001' AND
(
(T1.ProcessDate <= '07/30/2001' AND T1.Process = 'ISSUANCE') OR
T1.Process = 'ISSUED'
)
GROUP BY T1.Permit
HAVING Max(Process) < 'ISSUED' Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net