I have this query and it works fine when run the query by itself:
SELECT
c.factDate,
c.agentOsLoginId,
c.person,
c.systemName,
c.siteId,
c.callId,
c.callType,
c.agentId,
c.GMTStartTime,
c.GMTStopTime,
c.duration,
c.evalInitiatedTime,
TO_DATE (to_char (from_tz(cast(c.GMTStartTime as timestamp), 'GMT') at time zone 'US/Eastern', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') EasternStartTime,
TO_DATE (to_char (from_tz(cast(c.GMTStopTime as timestamp), 'GMT') at time zone 'US/Eastern', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') EasternStopTime,
TRUNC(TO_DATE (to_char (from_tz(cast(c.GMTStartTime as timestamp), 'GMT') at time zone 'US/Eastern', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS')) EasternfactDate
from m_NPInteraction c
where c.factDate >= trunc( add_months ( sysdate, -3 ), 'MM' )
But when I add this query to my data conversion code, it is throwing ORA-00907.
Here's what it looks like when I put it in the conversion code:
$class=com.merced.dice.translate.conduits.jdbc.MetaBeanTypeSource
$scope=global
configuration=(creator:/com/merced/dice/Configuration)
metaBeanType=(creator:/com/merced/apps/perform/recordtypes/NPInteractionEvalChunk)
queryBeanFormat="\
SELECT --\n\
c.factDate, --\n\
c.agentOsLoginId, --\n\
c.person, --\n\
c.systemName, --\n\
c.siteId, --\n\
c.callId, --\n\
c.callType, --\n\
c.agentId, --\n\
c.GMTStartTime, --\n\
c.GMTStopTime, --\n\
c.duration, --\n\
c.evalInitiatedTime, --\n\
TO_DATE (to_char (from_tz(cast(c.GMTStartTime as timestamp), 'GMT') at time zone 'US/Eastern', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') EasternStartTime, --\n\
TO_DATE (to_char (from_tz(cast(c.GMTStopTime as timestamp), 'GMT') at time zone 'US/Eastern', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS') EasternStopTime, --\n\
TRUNC(TO_DATE (to_char (from_tz(cast(c.GMTStartTime as timestamp), 'GMT') at time zone 'US/Eastern', 'yyyy-mm-dd HH24:MI:SS'), 'yyyy-mm-dd HH24:MI:SS')) EasternfactDate --\n\
from m_NPInteraction c --\n\
where c.factDate >= trunc( add_months ( sysdate, -3 ), 'MM' )
"
When it complied it throws ORA-00907. What am I missing here? Is there a quirk of Oracle that I don't know about? Or do I have some syntax error that also throws ORA-00907? Maybe somewhere in the WHERE clause? I'm not that experienced in Oracle so any insight would be greatly appreciated.