Background: I am rewriting part of an ASP classic app into crystal reports using my own SQL. I am using sub-reports and groups. Language is PL/SQL, Crystal Reports XI.
SQL: My SQL returns the data correctly in Toad. 9 rows sorted by transfer date descending.
Problem: When using a CR group, it considers the data to be distinct. However in fact, on one date two transfers were made, the result being that my report suppresses the duplicate data although I must show it. All the data in the table is duplicated somewhere, so any field I group by drops rows. Just TransDate comes the closes only 1 row short.
Needed: I guess I need a form of “On the Fly temp unique ID”. A field for this instance that will ensure that each row has a unique ID I can “Group BY” in Crystal. I cannot create a view & I cannot change the originating table. I need an on the fly id of some sort created when my sql is run. I am looking for ideas for a simple way if one exists, not using triggers etc. I have looked at sequences but they do not seem to offer what I need and MAX(???) + 1 AS WHATEVER, but max of course takes the highest date so not really helpful either.
Any suggestions would be appreciated greatly.
