Toad World® Forums

Toad Data Point Variables to Oracle

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

The first one failed as I don't think you can use CTE's with cross-query as the built in version of mySQL does not support the WITH. As for the second one you may have to ensure the t.create_date is in the correct data type. Those are the only ideas I can think of off-hand.