Does anyone know how to pass a properly formatted date value to an Oracle query? I have tried innumerable ways and nothing seems to work. Variable binding or loop parameters just don't work.
Teradata doesn't have a problem, but Oracle does. Even when the syntax doesn't complain I get back zero results which likely has to do with how the query is being formed by TDP which is confusing Oracle.
I need to get the max date from one system to pass to another. This is but one of several ways I've tried. The other was just to put the subquery in the where clause, but while that runs it doesn't seem to ever return a result. That seems to suggest it's not digesting the query very well. If I put a static date value in it works fine so it's definitely specific to a cross-connection query.
The query below is immediately flagged at line 2 but all the error tells me is the following:
Cross Query Database Error: You have an error in your SQL syntax;
check the manual that corresponds to your Cross Query server version for the right syntax to use near
'TD_MaxDate AS (SELECT Max(t.create_date) FROM `_14mo5a264orq9m4ut3cs8dp5u5s3hemd' at line 2
WITH
TD_MaxDate AS (SELECT Max(t.create_date) FROM conn1.db1.audit_logs t),
ORC_Logs AS (
select
USER_ID
,USER_GUID
,SERVER_NAME
,EVENT_NAME
,CREATE_DATE
from conn2.db2.AUDIT_LOG am
where (EVENT_NAME = 'LOGIN')
group by
USER_ID
,USER_GUID
,SERVER_NAME
,EVENT_NAME
,CREATE_DATE
)
select * ORC_Logs where ORC_Logs.create_date > TD_MaxDate
This version runs without an error, but never returns a result.
select
USER_ID
,USER_GUID
,SERVER_NAME
,EVENT_NAME
,CREATE_DATE
from conn2.db2.AUDIT_LOG am
where (EVENT_NAME = 'LOGIN')
and create_date > (SELECT Max(t.create_date) FROM conn1.db1.audit_logs t)
group by
USER_ID
,USER_GUID
,SERVER_NAME
,EVENT_NAME
,CREATE_DATE