create a package using a view with dynamic sql (automate the "where" condition)

[sakthivelmani]

Hi Friends,

I have a view that pulls millions of records and am creating a Cognos package with that view. The view pulls data from multiple tables and uses year in the where condition as the tables are huge. So my user wants me to create a package for every year as we cannot remove the where condition. If we remove it, the query hangs. I am trying to automate it somehow but am not getting any idea. Has anyone gone through this issue and found any solution. Please give me some directions.I dont think i can use parameter maps, session parameters or bind variables.


Thanks,
Sakthi

[Lynn]

I would try removing the where condition from the view and adding a prompt macro for year.

[sakthivelmani]

Thanks for the reply Flynn.

The problem is this view will be created in the database. The view will not compile with macros in it. It will give error like "Invalid character"

[Lynn]

Thanks for the reply Flynn.

The problem is this view will be created in the database. The view will not compile with macros in it. It will give error like "Invalid character"

The prompt macro would go in the Framework, not on the view.

[sakthivelmani]

Thanks Lynn. It worked. You nailed it. I really appreciate it