Converting datatype of @biServer.variables in OBIEE 12c-CodePudding

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?

CodePudding user response：

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).

CodePudding user response：

It all comes down to data types. If your output is of a numerical type, then by default you will have it with a decimal precision. As in 2021,00.

"2021" is a string, not a number. So if your output is a number then you just need to format the result as "0 decimals".

That said - WHERE do you write that SQL?! In a direct database request?