Julian Date Converted - issue with the where clause

building semantic layer
1

Hi all,

I have converted an Oracle JDE Julian Date using the syntax below:

TO_DATE(TO_CHAR(JULIAN_DATE + 1900000),‘YYYYDDD’)

This works fine in the SELECT clause

When I try and use this as a filter in Web Intelligence, I get the error message

Error: ORA-01848: day of year must be between 1 and 365 (366 for leap year)

Can anyone help?

jemstar :ireland: (BOB member since 2006-03-30)

2

Hi James,

When you said that it works fine in the SELECT clause - how did you verify it?

It may happen that if you tested an SQL statement in a query tool that the tool retrieved only first N (usually 100 or 1000 or so) records and everything might be OK there. But if the whole result of the SQL was retrieved, you could see an issue there as well even though the syntax you mentioned was used only in the Select clause, not in Where.

Try analyzing all distinct values of

TO_CHAR(JULIAN_DATE + 1900000)

, mainly the last 3 characters, whether there is anything different than numbers between 001 and 365.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Hi Marek,

I ran the query successfully within Webi without the filter…
There were no strange results in the output…all looked like legitimate dates…

It is only when I add the filter that I run into issues…

(I owe you an email…definitely Friday)

jemstar :ireland: (BOB member since 2006-03-30)

4

Hi,

Can you maybe post the SQL that WebI generates and mainly its WHERE clause?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

5

Hi Marek,

SQL in bold below causes an issue. It is worth noting that the field CRPDTA.F0911.GLDGJ below can contain 0 , but the additional filter CRPDTA.F0911.GLPOST ) <> ‘M’ removes these records from being considered

SELECT
to_date(to_char(( CRPDTA.F0911.GLDGJ )+1900000),‘YYYYDDD’)
FROM
CRPDTA.F0911
WHERE
( ( CRPDTA.F0911.GLPOST ) <> ‘M’ )
AND
to_date(to_char(( CRPDTA.F0911.GLDGJ )+1900000),‘YYYYDDD’) = ‘31-01-2015 00:00:00’
AND (( CRPDTA.F0911.GLPOST ) <> ‘M’)

jemstar :ireland: (BOB member since 2006-03-30)

6

Hi James,

I did not use to have good experience with relying on implicit conversions (string to date, etc.). Can you maybe try modifying the WHERE clause and to convert the hardcoded string to a date?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

7

I have played around with this by building some universe based prompts…

I will try this route again on Friday…

jemstar :ireland: (BOB member since 2006-03-30)

8

Hi Marek,

the julian date contained zeroes…but even though I was filtering for this scenario, I had to code for zeroes using a case statement

case
when @Select(GL Dates\Julian GL Date) <> 0 
then
to_date(to_char(@Select(GL Dates\Julian GL Date)+1900000),'YYYYDDD')
else to_date(to_char('01/01/9999'),'DD/MM/YYYY')
end

It works now…thank you for your input

jemstar :ireland: (BOB member since 2006-03-30)