Re: Reporting with dynamic sql

Deltones wrote:
> Err... the GLOBAL TEMPORARY shouldn't be there in one of the query.
> Forgot to remove it before posting. It was from one of my test.

After looking at this thread, I slowly become more convince that the root problem is coding on the fly. Do you have a data model? I mean an abstract logical data model. Did you do an ER diagram for this project? Even unnormalized reporting tables should be modelled first.

Frankly I would do most of what you propose in PL/SQL, on the assumption that most of this is a one time conversion. If that is not the case, I would go back to the design stage, because a PL/SQL solution based on the design I've seen so far, will be SLOW. You can open one cursor, fetch the data, and use that to open/fetch/close another cursor. Nested loops. Basically you give up some of the power of SQL for control of the process by writing it procedurally. It will execute slowly but will be better than trying to create tables dynamically. (I may be repeating some of Sybrand's advice at this point.)

HTH,
   ed Received on Fri Aug 18 2006 - 07:19:57 CDT