NS ODBC Data Retrieval Error
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I'm receiving a data retrieval error from the NS ODBC. Query works fine when the following isn't included within the where clause:
and t.TRANDATE between
--period beg
case when to_char(current_date, 'mm')='01' then add_months(trunc(current_date, 'yyyy'),-12)
else trunc(current_date, 'yyyy')
end
--period end
and to_date(trunc(current_date,'mm')-(1/84600), 'yyyy-mm-dd HH24:MI:SS')
Here's the error message:
Does anyone know how to resolve this error? Or where I can find the ticket to see the cause of the issue?
- Labels:
- Database Connection
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@mmvcans I am not expert at Oracle SQL variant which seems to be where the Trunc is most common but as I understand it Trunc truncates to the format specified including truncating the time component, returning a Date type not DateTime.
the period begin looks to result in a 10 character date and the period end looks to force a datetime result, the to-date function is on the outer bounds of the functions in that set.
I have no idea what the value/format of T.Trandate so the issue may be the discrepancy on types/values being compared.
Let me know if this is the problem.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@fharper t.trandate format is date time but even when using to_date to convert the conditionally returned value to datetime format, the same error occurs.
I can provide the entire query if that helps.
