Factored subquery in Reports 6i 1

[navrsalemile]

I tried to use a factored subquery (WITH ... AS ... SELECT ... ) in a Reports 6i data query section of data model. I got error:
"REP-0303 The statement you have entered is not a SELECT statement"

I am using Oracle 9iR2 and Reports 6i. The statement is valid Oracle 9i SQL statement. Is there a workaround?

many thanks,

 

[nagornyi]

You may use inline views or temporary tables instead of the names defined in the WITH. Here is an example:

http://www.dba-oracle.com/t_sql99_with_clause.htm

 

[navrsalemile]

Thanks for link, but why Oracle Reports displays error when it must have sent the query to Oracle DBMS 9i for parsing and 9i should have returned status OK for that statement?

 

[navrsalemile]

Strange thing happens when I try to change the query of the existing report: all of sudden the names of database columns change and report layout field names change, so existing column formulas become invalidated!? Why is this happening and how can I get the old field/column names?
All I did was to import external SQL query in data model?

many thanks,

 

[nagornyi]

Oracle Reports does not send queries directly do the DB, but does some processing first. For example, it substitutes values for parameters and replaces lexical parameters with their values. It's likely that Oracle Reports does some simple syntax checkup and the query starting with WITH does not pass it.
Regarding the second question, when you delete query from the data model, it is still in the memory. When you add a new query with the same column names, the report assumes the names already exist and replaces them. Usually it just adds number at the end of column name. The oly way to fix this that I am aware of is to supply new names to program units and field sourcing.

 

[navrsalemile]

I managed to compile with the new names suggested by Reports after changing the column names inside program units and triggers which is time consuming, but all I did was to edit existing query Q_1, replacing one SQL with another in the same Q_1 element, and not creating a new one. Does this mean that if I want to edit an existing query I first have to delete it (that practicaly means editing is not possible?)

 

[nagornyi]

Not nessesarily. Sometimes Report Builder preserves old names, sometimes gives new ones. Probably it depends on how much query was changed. I am not aware of any rules for this and would be happy to learn more. Anyone?