is there such a thing as a cursor in Crystal?

[rmwcalum98]

Is there such a thing as a cursor in Crystal? My CR 8.5 reference manual does not make any mention of cursors. It briefly goes over loops, but I am not finding that helpful in my case.

The report I'm working on needs to select all orders older than 30 days that either have no items or have all items voided. This means first I have to first grab all records in the order master table that are older than 30 days. Then I have to go to the order detail table and find out whether there are no records for that order or, if there are, that there is a 0 in the voided column for all the detail records with that sales order number.

My PL/SQL training tells me that as I grab each record in the master table, I should fetch a cursor into the detail table to see if there are any records for that master record, and if there are, see if they have been voided.

Hope this makes sense. I'd appreciate any suggestion. Thank you.

 

[IdoMillet]

Crystal can use Stored Procedures that use cursors...

In your case, I believe the whole thing can be achieved using a single SQL query (using an Outer Join). No need for a cursor.

Cheers,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

The closest thing to a cursor or subquery (cursors generally perform poorly, so try to use subqueries instead) in CR is a subreport.

It's best to create an SP or View on the database for this sort of thing (using CR ver 8.5 or below, ver 9 supports real SQL), but you can get there in CR.

Try a Left outer from Orders to Items and:

In the record selection criteria (Report->Edit Selection Formula->Record):

{table.orders} < currentdate-30

Group by Order number

Add a formula to the group level which will later be used to pass a shared variable back to the main report:

@initvoidtest
shared booleanvar Voidtest := true; // assume null

Now place a subreport linked by the order number in the group header which has only the items table.

It will have a formula in it which checks for there being a record, and whether all transactions are null in the details section.

@initvoidtest
shared booleanvar Voidtest;
if {items.voided} <> False then
Voidtest = false

Now the subreport will return a shared variable with true or false depending upon whether if found one of the following conditions:

True:
No records
Voided are all true

False:
Records
Voided are all false

So in the group footer on the main report you can set the suppression of the group based on this shared variable:

@initvoidtest
shared booleanvar Voidtest;
not(voidtest)

Something like that...

Crystal isn't very good at this sort of thing unless you move to CR 9.

-k

 

[rmwcalum98]

Thank you, synapsevampire. I am new to CR, and you have given me a lot of help. Now, I will try and go use what you have put in your response.

 

[Siggy19]

If you use the outer join between the two tables, it will always return a record for each order.

Assuming that the voided column has a number in it other than zero, if the detail has not been voided;

If you then group on the order and sum the voided column, the value of the sum will either be NULL (if there are no detail records), 0 if they are all voided or another value if some (or all) are not void.

This may be easier to use than synapsevampire's solution, but it assumes that the voided column has a value other than zero in it if it is not void and that all the values will either be positive or negative (otherwise they could cancel out).

 

[synapsevampire]

Good point, Siggy, I'm not sure why I went that route...

It should be the same methodology I used for the shared variable in the subreport, but instead just use a Left Outer of Orders to Items, with a voided check variable to check of all details per order type, making the determination of whether to display it in the group footer.

I think that I'd misread part of the requirements the first go around.

-k

 

[rmwcalum98]

Siggy,
Actually, the void column defaults to 0 and changes to 1 if the item is voided. So, your idea of summing up the rows for each order to see if they are greater than 0 won't work. I liked your idea of grouping, though, and I'm seeing if I can take a similar route.
Thanks.

 

[Siggy19]

It still works...

If the sum equals zero, it's not voided.
If the sum is greater than zero, it's voided.
If the sum is NULL, it's also voided.

(SUM has the value NULL if ALL of the values are NULL. Otherwise, it ignores the NULL values and just sums the non-NULL values)

 

[rmwcalum98]

But ALL items on the order have to be voided for the record to fulfill the report req.
So, if an order has 7 items, then SUM has to be 7. A sum of 1-6, while greater than 0, means that not all items have been voided.
I thought about doing a group, counting the number of rows for that order and then summing up the void column. If count = sum, then all items have been voided. Does that make sense?
Thanks.