Using Sql for temp tables or cursors to build up query

[DavidSherring]

I am currently working with Oracle, on a database that was very client/data driven. Users could set up practically what they wanted for collecting information, thus we ended up with lots of duplication, and lots of tables containing labels and data. To run reports, to gather all the data, many people before me developed cursors in Oracle, and reported off of that.

Trouble is, there are loads, and they are undocumented, and very very complicated. I would rather bypass them.

I would like to build up something in crystal, in the sql, similar to what I did with foxpro. I would select certain data from table(s), and create a temp table, ie. t1.

Then I would select more data from different sources, putting into t2. Then join (or union) t1 t2, and maybe another table into a large reporting table.

Can this be done within crystal on the sql screen, or do I need to use the back end (Oracle 10g) to do this?

 

[beltmanjr]

As far as I am aware you can indeed do this on the crystal side.

However, instead of building a temp table to have crystal look at, why not create a command which extracts the required information from the database (similar to a view in a database). This wont leave any residue on the database and can be used directly in the report.

If you do so note that you should prevent a command linking with anything else within crystal. Linking a command with tables in crystal slows things down as it will pul in all records of both rather than filtering and using where clauses etc (as far as I'm aware)