Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Evaluate in filter in 12c

Received Response
139
Views
21
Comments
Boček Petr
Boček Petr Rank 4 - Community Specialist
edited Aug 13, 2024 6:00PM in Oracle Analytics Cloud and Server

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

«13

Answers

  • Syedsalmancs110
    Syedsalmancs110 Rank 6 - Analytics Lead

    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
    Robert Angel Rank 8 - Analytics Strategist

    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
    Boček Petr Rank 4 - Community Specialist

    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
    Boček Petr Rank 4 - Community Specialist

    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
    Robert Angel Rank 8 - Analytics Strategist

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

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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
    Gianni Ceresa Mod

    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
    Boček Petr Rank 4 - Community Specialist

    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]
    [Deleted User] Rank 2 - Community Beginner

    Uuhh why not fitler the other way around?

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

  • Gianni Ceresa
    Gianni Ceresa Mod

    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