Hi Experts,

we have and obiee 11.1.1.7 connected to oracle10g that run the following query fine.

when we upgraded the db to 11.2.0.4 it started to return ora-942

searched oracle support and check DBFeatures.INI,several bugs bugs like YTD bugs but could not find exactly the same scenario

when running query using sqlplus it has success in 10.2 but fails in 11.2 with ora-942 

do we have any difference in "WITH" or "PARTITIONS" queries in 11.2

any help appreciated

================================================================================================================================================================

2017-08-20T15:40:08.440+03:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 961fb14a276067fe:2b4b12a6:15df05a32ee:-8000-000000000003c25e,0:1:9:5:1] [tid: 3b701700] [nQSError: 17010] SQL statement preparation failed. [[

file: server/Query/Execution/DbGateway/Oci10g/../Oci8/Src/SQXDGOci8.cpp; line: 373

Properties: description=<<3087>> DbGateway Exchange: MILEIS - Executive Information System.EISDB_CP; producerID=139857840558456; requestID=1928200224; sessionID=1928200192; userName=weblogic;

[nQSError: 17001] Oracle Error code: 942, message: ORA-00942: table or view does not exist

at OCI call OCIStmtExecute.

********** Task: 1. Running for (mls): 50 **********

Description: <<2756>>DbGateway Prepare

DSN:EISDB_CP;userName:appro

SQL:WITH

OBICOMMON0 AS (select T547211.PERIOD_MONTH as c3,

  T547211.PERIOD_YEAR as c4,

  ROW_NUMBER() OVER (PARTITION BY T547211.PERIOD_YEAR ORDER BY T547211.PERIOD_YEAR DESC) as c5,

  ROW_NUMBER() OVER (PARTITION BY T547211.PERIOD_YEAR, T547211.PERIOD_MONTH ORDER BY T547211.PERIOD_YEAR DESC, T547211.PERIOD_MONTH DESC) as c6,

  T547211.PERIOD_NAME as c7

from

  OBIEE_EIS_PERIODS_HIER_S T547211 /* Dim - Periods */ ),

SAWITH0 AS (select Case when case D1.c5 when 1 then D1.c3 else NULL end is not null then Rank() OVER ( ORDER BY case D1.c5 when 1 then D1.c3 else NULL end ASC NULLS LAST ) end as c1,

  Case when case D1.c6 when 1 then D1.c3 else NULL end is not null then Rank() OVER ( PARTITION BY D1.c4 ORDER BY case D1.c6 when 1 then D1.c3 else NULL end ASC NULLS LAST ) end as c2,

  D1.c3 as c3,

  D1.c4 as c4

from

  OBICOMMON0 D1),

SAWITH1 AS (select min(D1.c1) over (partition by D1.c4) as c1,

  min(D1.c2) over (partition by D1.c4, D1.c3) as c2,

  D1.c3 as c3

from

  SAWITH0 D1),

SAWITH2 AS (select Case when case D1.c5 when 1 then D1.c3 else NULL end is not null then Rank() OVER ( ORDER BY case D1.c5 when 1 then D1.c3 else NULL end ASC NULLS LAST ) end as c1,

  Case when case D1.c6 when 1 then D1.c3 else NULL end is not null then Rank() OVER ( PARTITION BY D1.c4 ORDER BY case D1.c6 when 1 then D1.c3 else NULL end ASC NULLS LAST ) end as c2,

  D1.c7 as c3,

  D1.c4 as c4,

  D1.c3 as c5

from

  OBICOMMON0 D1),

SAWITH3 AS (select distinct min(D1.c1) over (partition by D1.c4) as c1,

  min(D1.c2) over (partition by D1.c4, D1.c5) as c2,

  D1.c3 as c3

from

  SAWITH2 D1)

select sum(T547218.ACT_AMOUNT) as c1,

  D3.c3 as c2

from

  OBIEE_EIS_MBR_S T547218 /* Fact - MBR */ ,

  SAWITH1 D5,

  SAWITH3 D3

where ( T547218.PERIOD_MONTH = D5.c3 and D3.c1 = D5.c1 and D3.c3 = 'FEB-17' and D3.c2 >= D5.c2 )

group by D3.c3

order by c2