Oracle Communities

Oracle Analytics and AI

Child Item

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

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

Find more posts tagged with

Accepted answers

All comments

Joel Acha

Take a look at this article - https://gerardnico.com/dat/obiee/obis/server_variable_date

Robert Angel

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!!!)

ON T59551.END_DATE > '2018-09-17'
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

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

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

Hi Manoj,

datetime includes time, so it is expecting; -

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

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

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

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

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

Kind regards,

Manoj.