5 Replies Latest reply on May 8, 2018 8:02 AM by Martin van Donselaar

    OBIEE , ORA-00942 Table or View does not exists,  However it exists. (it works if change order)

    Camilo Flores

      Hello

       

      I have the following problem , today some reports don't work  (I haven't worked with this envioremwent during the last week)

      However with little changes in the query it works (which is not a solution since it is th equery generated by the OBI SERVER)

       

      Some odd behaviors

      1.- If I change the position of the tables in the Query , moving the last table (a fact table ) to the second last  position it works

      2.- If I Remove  the SAWITH part, it works

       

      Example

       

      1.- This does not work, and is the generated query

       

      WITH

      SAWITH0 AS (select count(distinct T41570.DD_NUM_EVENTO) as c1

      from

           View1 T41664,

           Dm1 T41412,

           View2 T41683,

           FTable 1 T41570

      where  ( T41412.ID_TIPO_EVENTO = T41570.ID_TIP_EVENTO

      and T41412.GLS_TIPO_EVENTO = 'DET'

      and T41570.FLG_ACTIVO = 1

      and T41570.ID_DEMOGRAFICA = T41664.ID_SEGMENTO_DEMOGRAFICO

      and T41570.ID_FECHA_HECHO = T41683.ID_TIEMPO

      and T41664.GLS_MAYOR_EDAD = 'MOE'

      and T41683.COD_ANO = TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'yyyy'), '9999')

      and T41683.FECHA between ( TO_DATE('2018-04-30' , 'YYYY-MM-DD') + TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'DDD'), '999') * -1 + 1 )

      and TO_DATE('2018-04-30' , 'YYYY-MM-DD') ) )

      select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,

           D1.c1 as c2

      from

           SAWITH0 D1 ) D1 where rownum <= 66001

       

      2.- This Works, Switch Positions of FTable and View2

       

      WITH

      SAWITH0 AS (select count(distinct T41570.DD_NUM_EVENTO) as c1

      from

           View1 T41664,

           Dm1 T41412,

           FTable 1 T41570,

           View2 T41683,

      where  ( T41412.ID_TIPO_EVENTO = T41570.ID_TIP_EVENTO

      and T41412.GLS_TIPO_EVENTO = 'DET'

      and T41570.FLG_ACTIVO = 1

      and T41570.ID_DEMOGRAFICA = T41664.ID_SEGMENTO_DEMOGRAFICO

      and T41570.ID_FECHA_HECHO = T41683.ID_TIEMPO

      and T41664.GLS_MAYOR_EDAD = 'MOE'

      and T41683.COD_ANO = TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'yyyy'), '9999')

      and T41683.FECHA between ( TO_DATE('2018-04-30' , 'YYYY-MM-DD') + TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'DDD'), '999') * -1 + 1 )

      and TO_DATE('2018-04-30' , 'YYYY-MM-DD') ) )

      select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,

           D1.c1 as c2

      from

           SAWITH0 D1 ) D1 where rownum <= 66001

       

       

      3.- This Works , Remove the Sawith part and the End of the query, to prove the table does exist

       

      select count(distinct T41570.DD_NUM_EVENTO) as c1

      from

           View1 T41664,

           Dm1 T41412,

           View2 T41683,

           FTable 1 T41570

      where  ( T41412.ID_TIPO_EVENTO = T41570.ID_TIP_EVENTO

      and T41412.GLS_TIPO_EVENTO = 'DET'

      and T41570.FLG_ACTIVO = 1

      and T41570.ID_DEMOGRAFICA = T41664.ID_SEGMENTO_DEMOGRAFICO

      and T41570.ID_FECHA_HECHO = T41683.ID_TIEMPO

      and T41664.GLS_MAYOR_EDAD = 'MOE'

      and T41683.COD_ANO = TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'yyyy'), '9999')

      and T41683.FECHA between ( TO_DATE('2018-04-30' , 'YYYY-MM-DD') + TO_NUMBER(TO_CHAR(TO_DATE('2018-04-30' , 'YYYY-MM-DD'), 'DDD'), '999') * -1 + 1 )

      and TO_DATE('2018-04-30' , 'YYYY-MM-DD') )

       

      Oracle Database

       

      Has anyone faced this issue? Any suggestions?

       

      Thanks

       

      Camilo