Derived Table problem

[Former Member]

We needed an extra value brought back from a table depending on other values in the table, so created a derived table with a "CASE" statement. This works perfectly.

There was already a derived table which used the original "real" table, and we replaced this "real" table with our derived one using @DerivedTable(ABC). We're now getting an Oracle error 903 - Invalid Table Name - and looking at the code, the query being passed to Oracle is still in the form @Derived Table() i.e. Business Objects is not replacing it with the sql code correctly.

Earlier, we'd done a sort of "proof of concept" using very simple nested derived tables, and it worked perfectly, so it would appear that this should work?

Everything parses correctly in Designer.

Can anyone suggest what could be causing the error or a way to fix it?

Thanks,

Malcolm

[Former Member]

Firstly, do you have double quotes like @DerivedTable("ABC").

Secondly, if your case statement is just a translation of one or two other columns, consider putting the case statement in the select statement of a dimension. This will keep your schema cleaner and reduce the risk of affecting other dimensions.

[Former Member]

David,

We tried putting double quotes araound the table name as you suggested, and it won't either parse or run.

It's strange - as I said in the original post, we tried the technique on very simple tables first as a Proof of Concept, and it's fine but when we came to use it in the real world code, it appears that Business Objects is not pulling in the code for the derived table as it should.

Is there perhaps some limit to the number of lines of code in a sql statement, or the levels of complexity in the code?

[Former Member]

If you are replacing a derived table already existing you can simply modify the SQl for the old Derived table to meet your needs.

[Former Member]

Thanks for the suggestion - but it's not a very simple replacement!

We have a number of derived tables which accessed the original "real" table. This needed to change to modify the values returned from the table, and so using a derived table to replace this means that (a) we have the code in one place, and it means minimal change to our original derived tables and (b) the original table is used in several places in the existing derived table code, and replacing each occurence with the code for the derived table will make it extremely long and complex (and therefore harder to maintain).

We may be able to re-write the code using a "with" clause, but again this will be major work.

As I said, we did try out the technique on much simpler statements, and it worked perfectly - looking at the SQL generated, Business Objects handles the replacement in the SQL code perfectly and this is what we hope to achieve with our more complex real-life code.

In particular, so that if any future changes to the business rules entail a change, that we only have to make the change in one place, and not in several places in three (or more) scripts.

Malcolm