9 Replies Latest reply on Sep 28, 2018 10:41 AM by Manoj Dixit

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

    Manoj Dixit

      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 c4
      FROM (FACT_Q T45210 INNER JOIN DIM_DATE T42529 ON T42529.DATE_ID = T45210.DATE_ID)
      LEFT OUTER JOIN DIM_TEAM T59551
      ON T59551.END_DATE      > '2018-09-17'
      AND T59551.START_DATE <= '2018-09-17'
      AND T45210.TEAM_ID     = T59551.TEAM_ID
      WHERE (T42529.DTE       = TO_DATE ('2018-09-17', 'YYYY-MM-DD'))
      GROUP BY T42529.DTE
      , T59551.TEAM_NAME
      , T59551.TEAM_OWNER
      ORDER 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.