0

In the teiid documentation the following definition is stated for the DayOfWeek function

Return day of week (Sunday=1, Saturday=7)

But when the teiid is pushed down to Oracle the dayOfWeek output differs depending on locale

US Locale

Sunday: 1

German Locale

Monday: 1

I investigated a bit and found the source query when translated to oracle and the DayOfWeek is translated to this to_number(TO_CHAR(g_0."DATE", 'D')). The to_char method in Oracle depends on the locale.

Is there an alternative to the DayOfWeek function or some sort of a workaround for this problem ?

Iman
  • 1

1 Answers1

0

From https://community.oracle.com/ideas/24090 there are a couple of proposals for working around that oracle does not support an ID format that would match Teiid's org.teiid.iso8601Week=true behavior. Other than making a local code fix based upon one of those suggestions, you would have to create a new source function to provide similar functionality

create foreign function ora_dow(param timestamp) returns integer options ("teiid_rel:native-query" 'trunc($1) - trunc($1,''IW'') + 1');

Also captured as https://issues.redhat.com/browse/TEIID-6016

Steven Hawkins
  • 538
  • 1
  • 4
  • 7