SOLVED : ORA-01762: vopdrv: view query block not in FROM :-[

[BenziD]

Hi

All my reports that USE TO WORK that union joins are failing

data for Q1 (date, client, revenue,<cast null as numeric> Diff, 'Q1' Title)
Union
data for Q2 (date, client, revenue,<cast null as numeric> Diff, 'Q2' Title)
Union
data for Q3 (date, client, revenue,<cast null as numeric> Diff, 'Q3' Title)
Union
data for Q1 Join with data for Q2 => calculate differences
(Q1.date, Q1.client, <cast null as numeric> Revenue, <Q2.revenue-Q1.revenue >Diff, 'Diff' Title)
Union
data for Q2 Join with data for Q3 => calculate differences
(Q2.date, Q2.client, <cast null as numeric> Revenue, <Q3.revenue-Q2.revenue >Diff, 'Diff' Title)

The error :
UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-01762: vopdrv: view query block not in FROM
RSV-SRV-0042 Trace back:RSReportService.cpp(722)

Any idea?

[MFGF]

Hi,

The message coming back from ORACLE - ORA-01762: vopdrv: view query block not in FROM seems to indicate that you are referencing a view in your WITH statement that you are not then including in your FROM clause.

Might be an issue in the way the query subject SQL has been coded in FM, or perhaps a glitch in the way the query engine is generating your queries. What happens if you switch off the use of WITH clauses in your queries?

MF.

[calson33]

There is an unpublished Oracle bug that cause an error where a "with" clause is not used in the query:

http://oradbastuff.blogspot.com/2012/08/qkswcdriver.html

[BenziD]

SOLVED
i switch off the use of WITH clauses in your queries - 10X MFGF
ill forward the oracle bug to our DBA team for future issues. - 10X calson33

[Maverick111]

Hi,

Could you please share the information on how to disable the WITH clause in queries.

[adam_mc]

I have encountered the same problem on occasions too.
We resolved by setting the "Use SQL With Clause" property of the Query to be No (within Report Studio).

However, if there is a more global solution I'd like to know too.

[MFGF]

In Framework Manager, go to Project > Edit Governors (in the menubar) and uncheck the "Use WITH clause when generating SQL" option.

Save the model and republish your package(s)

Cheers!

MF.