nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Evaluate in filter in 12c

Boček Petr

Hi community,

any idea why this logical command in BI 12c fails?

SELECT 0 s_0, s_1, s_2, s_3

FROM "BI_CUBE"

WHERE ("PERIOD"."PERIOD" > (EVALUATE('TO_DATE(''20170101'',''YYYYMMDD'')'))) AND ("PROJECT"."NAME" = 'XXX')

ERROR:

Error Codes: OPR4ONWY:U9IM8TAC:U9IM8TAC:U9IM8TAC:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)

State: HY000. Code: 43113. [nQSError: 43113] Message returned from OBIS. (HY000)

State: HY000. Code: 43119. [nQSError: 43119] Query Failed: (HY000)

State: HY000. Code: 22024. [nQSError: 22024] A comparison is being carried out between non-compatible types BOOL and TIMESTAMP. (HY000)

But if I use equal sign than I get results. Also in 11.9g IT WORKS!! What I don't understand is the BOOL type. Result of both sides is DATE...

Find more posts tagged with

Accepted answers

All comments

Syedsalmancs110

Looks like a known issue and known behavior is OBIEE 12c have a look at below KM documents:

OBIEE 12C - Reports Migrated From OBIEE 11g Generate the Error - State: HY000. Code: 22024. [nQSError: 22024] A comparison is being carried out between non-compatible types TIMESTAMP and VARCHAR (Doc ID 2127741.1)

OBIEE 12c : Few Reports Migrated From OBIEE 11g throw error: "Comparison is being carried out between non-compatible types DATE and VARCHAR" (Doc ID 2190067.1)

Robert Angel

Hi,

I know your Period is probably a date type in Oracle, but in OBIEE terms, as the field has accuracy that includes time, the data type will be datetime.

i.e. sysdate in Oracle DB is 02-JUN-2017 09:02:35 GMT

So if you cast your Period as date in your formula you may find that fixes your issue.

Hope this helps,

Robert.

Boček Petr

Thanks Robert for help, but your idea doesn't work. I'm getting still the same error. And I don't think this is the problem because if I change the operator to = or to IN:

SELECT 0 s_0, s_1, s_2, s_3

FROM "BI_CUBE"

WHERE ("PERIOD"."PERIOD" = (EVALUATE('TO_DATE(''20170101'',''YYYYMMDD'')'))) AND ("PROJECT"."NAME" = 'XXX')

Then all works like a charm...

Boček Petr

Thanks Syed, I'm not sure if your provided links are related to my issue because under certain circumstances the command works. Or better It only works when I use = or IN

Robert Angel

Have you tried it the other way, casting your literal to a datetime?

[Deleted User]

What type of source are you accessing and which version? (Oracle DB 12.2.0.1, Essbase 11.1.2.3.500,...)

What exact data type are the column in the logical layer? In the physical layer? In the actual source?

OBI12 has new hard rules about data types which might pose an issue for you.

Gianni Ceresa

Different question instead of an answer: why do you use EVALUATE to do a TO_DATE ? OBIEE can manage that kind of thing without having to use an EVALUATE and disturb the DB for that kind of job ...

Boček Petr

Hi Gianni,

the whole process is like that. Users have dashboard prompt year. DB partions are set for Period. So the whole thing is about to construct query from BI which will limit data on period column (date) and therefore I use evaluate to get date from year. The original command is the below:

"PERIOD"."PERIOD" BETWEEN EVALUATE('TO_DATE(@{YEAR}{2017} || ''0101'' ,''YYYYMMDD'')') AND EVALUATE('TO_DATE(@{YEAR}{2017} || ''1231'' ,''YYYYMMDD'')')

If I don't go via partitions built on period then generating the report takes ages.

[Deleted User]

Uuhh why not fitler the other way around?

YEAR("PERIOD".PERIOD") = @{YEAR}{2017}

Gianni Ceresa

I knew there was something special

So first of all what Christian just posted: why to make it complicated when you can have an easy solution

Second option is to use OBIEE to build your dates, TO_DATE has an equivalent in OBIEE: TO_DATETIME

OBIEE in IL: OBIEE - TO_DATETIME the secret function