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
2

Answers

  • Boček Petr
    Boček Petr Rank 4 - Community Specialist

    Hi Christian, the period is set to datetime accros all layers. Source DB is Oracle Database 12c Enterprise Edition Release 12.1.0.2.0. If I would now what is the bool and how it happened maybe I would be able to find some solution. But actually I'm quite despered about the BOOL type.

  • Boček Petr
    Boček Petr Rank 4 - Community Specialist

    Gianni, Christian, I did not the year function because I think it will not go through partitions in DB. But I will test it and let you now.

  • Boček Petr
    Boček Petr Rank 4 - Community Specialist

    So the YEAR("PERIOD".PERIOD") = @{YEAR}{2017} solution is not hitting partition because the db first convert all values to year so the report takes ages again. So the challenge is to keep period.period as it is and play with right side of condition.

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

    Ok then yet another way to write it without evaluate:

    "PERIOD"."PERIOD" between CAST((@Year}{2017} || '/01/01') AS DATE) and CAST((@Year}{2017} || '/12/31') AS DATE)

    Or with mathematical lingo

    "PERIOD"."PERIOD" >= CAST((@Year}{2017} || '/01/01') AS DATE) and <= ((@Year}{2017} || '/12/31') AS DATE)

  • Gianni Ceresa
    Gianni Ceresa Mod

    Going back to the original question of "where does the boolean come from" : I would say the boolean is the result of your EVALUATE. EVALUATE doesn't have a type and OBIEE has no way to know it's a date. There is some flexibility with types and that's why your = or IN works I guess, but to use > or < you need to cast the result of EVALUATE as a date.

    Which of course can be done as Christian just posted removing the EVALUATE which is also going to remove the error of mismatching types.

  • Boček Petr
    Boček Petr Rank 4 - Community Specialist

    if I put this to value field in filter I got pop up window saying this, If I put this as SQL then it is not working either as without evaluate it can work with functions and operators

    pastedImage_0.png

    as SQL:

    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: 27002. [nQSError: 27002] Near <@&gt;: Syntax error (HY000)
    State: HY000. Code: 26012. [nQSError: 26012] . (HY000)
    SQL Issued: @{call NQSGetLevelDrillability('SELECT "PERIOD"."YEAR" saw_0, "PERIOD"."YEAR_MONTH" saw_1, "PROJECT"."NAME" saw_2 FROM "BI_POLAND" WHERE ("PERIOD"."PERIOD" IN (CAST((@Year}{2017} || ''/01/01'') AS DATE)))')}

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

    Well of course it has to fit your date picture. I was just writing pseudo code - I don't know the server-internal setup of your environment.

    Although if you give me an open URL I'm sure I can fix it in about 2 minutes

  • Boček Petr
    Boček Petr Rank 4 - Community Specialist

    hmmm maybe I need to retype you suggestion.... gimme a sec :-)

  • Boček Petr
    Boček Petr Rank 4 - Community Specialist

    Christian thanks for leading me to solution. I had to just little modify your command. So the working one is CAST('01/01/'||'@{YEAR}{2017}' AS DATE).

    Thank you all for your effort I hope I'll be able to help you too soon with anything :-)

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

    So no trip to Czech Rep for me? Damn...thanks that destroyed my weekend :-(