×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

want to read same field twice, select different values

want to read same field twice, select different values

want to read same field twice, select different values

(OP)
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?

RE: want to read same field twice, select different values

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

RE: want to read same field twice, select different values

(OP)
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.

RE: want to read same field twice, select different values

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close