oracle-databaseoracle12cobiee

Converting datatype of @biServer.variables in OBIEE 12c

I'm using one of the repository variables for extracting year, here is the SQL code:

SELECT "Time_D"."Year"
  FROM "Order D"
 WHERE "Time_D"."Year" <= '@{biServer.variables[' Current_year ']}'
   AND "Time_D"."Year" >= '2020'
 ORDER BY "Time_D"."Year" DESC

But the result is:

2021,00
2020,00

How can I remove ',00' in front of the year?

Solution

  • Khm, it's actually behind it.

    As far as Oracle itself is concerned, you could try with substr or regexp_substr, e.g.

    select substr("Time_D"."Year", 1, 4)         as year_1,
       regexp_substr("Time_D"."Year", '\d+') as year_2
from ...

    Or, if that's an item in OBIEE, set its format mask (to remove decimals).