Feb 18, 2013 8:56 AM

I have a Source Qualifier that uses a Query Override. In the Query override i have the query below that has a subqeury in it.

When I try to validate the query I get 'Invalid Query' , however the query works fine when run from a query tool like toad an even the mapping works fine when I test it, but when validating I still get 'Invalid Query'.

Can I prevent this validating error? I do not want future developers to be confused by it.

SELECT

ENTITYID AS ENTITYID

, ENTITYTYPE AS ENTITYTYPE

, ENTITYNAME AS ENTITYNAME

, STATEDEFINITIONNAME AS STATEDEFINITIONNAME

, PROCESSNAME AS PROCESSNAME

, STEPID AS STEPID

, STEPNAME AS STEPNAME

, PROCESSCOMPLETED AS PROCESSCOMPLETED

, PARTICIPANTTYPE AS PARTICIPANTTYPE

, SYSTEMPARTICIPANTTYPE AS SYSTEMPARTICIPANTTYPE

, ASSIGNEDTORESOURCENAME AS ASSIGNEDTORESOURCENAME

, ASSIGNEDTOROLENAME AS ASSIGNEDTOROLENAME

, ASSIGNEDTOPARTICIPANTNAME AS ASSIGNEDTOPARTICIPANTNAME

, ASSIGNEDTOPARTICIPANTID AS ASSIGNEDTOPARTICIPANTID

, PARTICIPANTCODE AS PARTICIPANTCODE

, STEPSTATUSCODE AS STEPSTATUSCODE

, STEPSTATUS AS STEPSTATUS

, STEPCOMPLETED AS STEPCOMPLETED

, STEPACTIVE AS STEPACTIVE

, DATEASSIGNED AS DATEASSIGNED

, DATECOMPLETED AS DATECOMPLETED

, DATESTARTED AS DATESTARTED

, ACTIONEDBYID AS ACTIONEDBYID

, ACTIONEDBYNAME AS ACTIONEDBYNAME

, REQUIREDTIMETOCOMPLETE AS REQUIREDTIMETOCOMPLETE

, DUEDATEGROSS AS DUEDATEGROSS

, DUEDATE AS DUEDATE

, TIMETOCOMPLETE AS TIMETOCOMPLETE

, DATE_LOAD AS DATE_LOAD

, PLANNEDFINISH AS PLANNEDFINISH

, PLANNEDSTART AS PLANNEDSTART

, TASKID AS TASKID

FROM

( SELECT

rfa.referenceid AS ENTITYID

, '(R)FA' AS ENTITYTYPE

, NULL AS ENTITYNAME

, NULL AS STATEDEFINITIONNAME

, '(R)FA' AS PROCESSNAME

, rfa.historyid AS STEPID

, substr(trace,instr(trace,'||',-1)+2,100) AS STEPNAME

, NULL AS PROCESSCOMPLETED

, NULL AS PARTICIPANTTYPE

, NULL AS SYSTEMPARTICIPANTTYPE

, NULL AS ASSIGNEDTORESOURCENAME

, NULL AS ASSIGNEDTOROLENAME

, NULL AS ASSIGNEDTOPARTICIPANTNAME

, NULL AS ASSIGNEDTOPARTICIPANTID

, NULL AS PARTICIPANTCODE

, NULL AS STEPSTATUSCODE

, NULL AS STEPSTATUS

, null AS STEPCOMPLETED

, null AS STEPACTIVE

, to_date('01-01-1900','dd-mm-yyyy') AS DATEASSIGNED

, lag(createdon) over (partition by referenceid order by createdon desc) AS DATECOMPLETED