union in impromptu

[Viper6]

Is there any way to perform a union operation in impromptu? The situation I have is that I have two large tables and I need some info from one and some info from the other.
ie. Table1 = Health Claims
Table2 = General Claims
The data in Table1 is at a more detailed level than Table2.
What I need is all health claims + all general claims that are not included in Health claims.
The only way I've found to do this is by writing two queries and using union to compile the sets, but I can't seem to get impromptu to do this. Is there a way?

Thanks,

 

[griffindm]

The only way I have found to do this in Impromptu is to create a database view that houses the SQL code combining the two tables, and then add that view to an Impromptu catalog. Performance may be affected if the view does not resolve quickly with the other filtering information in the report. If the view does not automatically appear as a table in the Impromptu Catalog Tables dialog, you will need to turn off the Retrieval Database Tables option via the Tools | Options setting under the Database group. Then you will have a Retrieve Tables button when editing the Catalog | Tables menu. When selected, you will be prompted for the types of database objects to include. Be sure to check Views.

HTH,

Dave Griffin

 

[Viper6]

I found a way to do the union by writing my own query in the edit sql box, but this limits me to having to write the entire query in SQL.

 

[griffindm]

While this is technically possible, the restrictions on future changes in the report make it a limited option. Using the same SQL to create a view and then adding the view to the catalog is much more flexible.

Dave Griffin

 

[Viper6]

Thanks,
The database we're using is updated weekly and while we can't change the current structure of the database, we can add tables. I think we'll create a table every week instead of a view to increase performance.