0 Replies Latest reply: Feb 7, 2013 8:11 AM by Jarle Eriksen RSS

    problem saving data model when using Oracle DB Pivot feature

    Jarle Eriksen
      I have problem saving a data model - SQL Query that uses the Oracle DB PIVOT feature?
      When I hit save, nothing happends.... have also tried "save as" - nothing is saved
      No error message is shown.
      Is Oracle DB PIVOT not supported by OBIEE?

      The SQL that is causing the problem is this one:

      SELECT * FROM
      (
      SELECT field_name as field,
      CASE
      WHEN col_1 = 'A' THEN 'Name 1'
      WHEN col_1 like 'B_%' THEN 'Name 2'
      END
      as gastype,
      energy
      FROM table_1
      WHERE code = 'K'
      AND trunc(DAYTIME,'YYYY') = trunc(to_date('2012-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'), 'YYYY')
      AND (col_1 in ('A') or col_1 like 'B_%')
      )
      PIVOT (sum(energy) for (gastype) in ('Name 1' as Name1, 'Name 2' as Name2))
      order by field

      We are using OBIEE 11.1.1.6.7 (Win 2008R2) against Oracle DB 11.2.0.3