Non-System Session Variable based on Date data type is throwing ORA-01861: literal does not match fo — Oracle Analytics

Oracle Analytics Cloud and Server

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

Non-System Session Variable based on Date data type is throwing ORA-01861: literal does not match fo

Received Response
53
Views
9
Comments
Manoj Dixit
Manoj Dixit Rank 6 - Analytics Lead

Hi,

I have a non-system session variable defined as under.

Initialization Block: (tried the three SQLs below one after another)

SQL 1=

select trunc(sysdate) from dual 

SQL 2=

select to_date(to_char(trunc(sysdate), 'YYYY-MM-DD'), 'YYYY-MM-DD') from dual

SQL 3=

select dte from dim_date where date_val = trunc(sysdate)

I have a target variable (non-system session) SV_Cutoff_Date which is initialized as  DATE '2018-09-20'  .

I am using the SV_Cutoff_Date in a complex join as

DIM_TEAM.TEAM_ID = FACT_Q.TEAM_ID AND DIM_TEAM.START_DATE <=  VALUEOF(NQ_SESSION."SV_Cutoff_Date") AND DIM_TEAM.END_DATE >  VALUEOF(NQ_SESSION."SV_Cutoff_Date")

A Request (OBIEE 10.1.3.4.2) works fine on its own.

However, it's a prompt (combined with the Request), I am getting the below error.

ORA-01861: literal does not match format string

A couple of Logical SQLs are:

SQL Issued: SET VARIABLE SV_Cutoff_Date='2018-09-20';SELECT "Date Dimension".Date saw_0, "Team Dimension"."Team Name" saw_1, "Team Dimension"."Team Owner" saw_2, "Q Fact".Count saw_3 FROM "Sales" WHERE ("Date Dimension".Date = date '2018-09-20') ORDER BY saw_0, saw_1, saw_2
SQL Issued: SET VARIABLE SV_Cutoff_Date='2018-09-17';SELECT "Date Dimension".Date saw_0, "Team Dimension"."Team Name" saw_1, "Team Dimension"."Team Owner" saw_2, "Q Fact".Count saw_3 FROM "Sales" WHERE ("Date Dimension".Date = date '2018-09-17') ORDER BY saw_0, saw_1, saw_2

A Physical SQL is given below.

SELECT  T42529.DTE AS c1, T59551.TEAM_NAME AS c2, T59551.TEAM_OWNER AS c3, COUNT (T45210.C_FLAG) AS c4FROM (FACT_Q T45210 INNER JOIN DIM_DATE T42529 ON T42529.DATE_ID = T45210.DATE_ID)LEFT OUTER JOIN DIM_TEAM T59551ON T59551.END_DATE      > '2018-09-17'AND T59551.START_DATE <= '2018-09-17'AND T45210.TEAM_ID     = T59551.TEAM_IDWHERE (T42529.DTE       = TO_DATE ('2018-09-17', 'YYYY-MM-DD'))GROUP BY T42529.DTE, T59551.TEAM_NAME, T59551.TEAM_OWNERORDER BY c1, c2, c3;

Literal '2018-09-17' is causing the issue but I have failed to understand how to fix it. I have tried to use CAST as well in the complex join but that is equivalent to

CAST ('2018-09-17' AS DATE) which still throws the same error.

How can I specify the date format as per the WHERE clause inside the complex join?

Could you please help?

Thanks and regards,

Manoj.

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist
  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    If this is against Oracle DB then default date data type typically has default date format of DD-MON-YY i.e. '21-SEP-18' - have you tried this?

    The bottom line is that this will not work; -  (and in IT relying on implicit conversion is never wise!!!)

    1. ON T59551.END_DATE      > '2018-09-17' 
    2. AND T59551.START_DATE <= '2018-09-17'

    You could have; -

    ON T.59551.END_DATE > to_date('2018-09-17','YYYY-MM-DD')

    and T.59551.START_DATE <= to_date('2018-09-17','YYYY-MM-DD')

    Note also if this Oracle DB then your date includes time so the effect of this is to filter as below; -

    ON T.59551.END_DATE > to_date('2018-09-17 00:00:00','YYYY-MM-DD hh24:mi:ss')

    and T.59551.START_DATE <= to_date('2018-09-17 00:00:00','YYYY-MM-DD hh24:mi:ss')

    So end date greater than 17th September 2018 midnight

    and

    start date smaller or equal to 17th September 2018 midnight

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    ORA-01861: literal does not match format string

    ^ means OBIEE sees the date/timestamp different than you think it does ... refer to that link from @joel_acha for more info.

  • Manoj Dixit
    Manoj Dixit Rank 6 - Analytics Lead

    Thanks for the above replies.

    I understand the issue but I am not able to resolve it.

    As per step 3.1, I have varied my SQL statements in the initialisation block.

    As per step 3.2, the 'Results' window shows me SV_Cutoff_Date value as 2018/09/21 00:00:00 .

    As per step 3.3, the Session Manager dialog box shows me the SV_Cutoff_Date as 2018/09/21 00:00:00 and its data type as DATETIME.

    I think I am using the session variable properly (the request individually runs for the current date) - Step 3.4.

    In order to avoid sysdate issue (Step 3.4.2) I have tried using 'YYYY-MM-DD' format in the IB (SQL 2) and also as a filter condition on the Date dimension is working (Line 10 of the Physical SQL) I have tried using the Date dimension in the IB (SQL 3). I have used trunc(sysdate) too in the IB (SQL 1). I have tried using CAST too in the complex join.

    Am I guessing it correctly that due to the session variable's data type is DATETIME, the error is thrown? If so, looking for ways to fix it.

    Effectively, I want to understand how I can specify the date format for lines 7 and 8 (filter on the session variable) of the physical query as per line 10 (filter on the date dimension).

    Thanks once again.

    Regards,

    Manoj.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Hi Manoj,

    datetime includes time, so it is expecting; -

    pastedImage_0.png

    So if you cast anything to datetime then it needs the 'extra' detail like this; - 2018/09/21 00:00:00

    If you have have to compare dates to datetime, first of all my sympathies, but second make sure you convert them to be the same datatype first, never rely on implicit conversion.

    And if you have to convert and convert back, then again, explicit conversion at every step, I have experienced it work and then not work, with all things being equal with OBIEE.

    So if you need to cast date to datetime then you can (there are other ways) cast date to char in the format you want, concatenate the extra ' 00:00:00' on the end and cast to datetime.

    The OBIEE gods are now weeping, I join them too, as the best way to handle dates is by good data warehouse design, then you don't get into all of this mess....

    Hope this helps / makes sense?

  • Manoj Dixit
    Manoj Dixit Rank 6 - Analytics Lead

    Nice to know that the OBIEE gods (and angels) are helping me. Thank you, thank you very much. But I am not sure what I have done to make them weeping?

    The date attribute of the date dimension is of date data type (both in the database and the RPD) so no time aspect is expected. Team dimension's start date and end date too are date date type (both in the database and the RPD) so no time aspect is expected. If it has, it will always be 00:00:00. Our DW architects have always chosen the correct data types and our DBAs have mentioned to avoid implicit data type conversions. It makes sense what they say and you too are saying.

    After going through the blog again, I have altered "SQL 1" as

    select sysdate from dual

    and converted the date and time session variable value to DATE in the complex join as

    CAST ( VALUEOF("SV_Cutoff_Date") AS DATE ) 

    I shall do some testing and update this question later.

    Thank you very very much, again.

    Regards,

    Manoj.

  • Manoj Dixit
    Manoj Dixit Rank 6 - Analytics Lead

    No luck yet.

    With

    CAST ( VALUEOF("SV_Cutoff_Date") AS DATE )

    in the complex join, the SQL statement generated is

    cast(TO_DATE('2018-09-26' , 'YYYY-MM-DD') as DATE )

    which throws no error but the prompt's value is never considered which means that the default value of the session variable is always used.

    And with

    CAST ( VALUEOF(NQ_SESSION."SV_Cutoff_Date") AS DATE )

    in the complex join, the SQL statement generated is

    cast('2018-09-26' as DATE )

    which throws the ORA error although it considers the prompt's values (other than 26th of September, 2018).

    A work around using integer date values (20160926, for example) works fine with dynamic prompt but users can't use calendar and that format is bit weird.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    6 days ago ...

    "If this is against Oracle DB then default date data type typically has default date format of DD-MON-YY i.e. '21-SEP-18' - have you tried this?"

    the ORA error is because your literal is not right (no matter what the Admin tool shows - the query isn't run in the admin tool but on the database)

    https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams122.htm#REFRN10119

  • Manoj Dixit
    Manoj Dixit Rank 6 - Analytics Lead

    Yes, tried that just now. My apologies, sorry I kept you gentlemen weeping.

    Kind regards,

    Manoj.