BusinessObjects Board

Derived table refresh

I am having an issue with refreshing a derived table due to new fields added to main table. Refresh structure works on the main table but not on the derived table. I am on BOXI V4.2 Sp6 patch5. Any ideas please.

Does your derived table explicitly list out your columns or do you just have “SELECT *…”?

I have a select statement bringing back a subset of results.

I am not sure what you are expecting here. Refresh structure isn’t going to add the new columns to your derived table select statement. Is it not letting you add them?

When I edit the derived table I can see the new fields in the table view but I cannot see the new fields in the Derived Table view. Also I don’t see an option to be able to add fields to the derived table in the UDT. I need to be able to select the field form the derived table to create a new objects. It’s like the sql script is not running to refresh the derived table.

UDT? Is this a .unv universe?

There is no “option” to add fields to a derived table. You update the SQL to include them.

If your derived table was select fielda, fieldb from table x and you’ve now added fieldc, you have to update the sql statement to say select fielda, fieldb, fieldc from table x.

Works the same in UDT and IDT.

2 Likes

The problem is that the sql statement does not seem to be updating the table with the new fields. I have a Select * in the statement and the new fields do appear in the derived so that I can select them to create an object. is there a way to force the sql statement to run to populate the derived table?

I am now getting the following error when trying to run the select statement
Query Timeout expiredstate HYT00.

What is the current SQL statement and what do you want it to be?

Current statement is
Select * from incident_view with (NOLOCK) where incident_view.name like ‘%-Restricted’

This should then populate the derived with all the fields but keep getting the timeout error.

Using * is bad practice in production SQL, even for derived tables.
Rebuild your SQL statement in SSMS, test it works there, then bring it into the universe to replace the existing SQL statement.

There may also be an unsupported data type in incident_view that you may need to cast as a different data type for it to be supported.

3 Likes

If you are doing a SELECT *, try creating an alias of incident_view and put a self restricting join on it for your filter?

I think I have fixed the issue. I have changed the sql statement to include the database name so
<database_name>.dbo.incident_view and this seems to allow the script to run.

Thanks for all your help.