Skip navigation
Contact Us
688 Views 2 Replies Latest reply: Feb 18, 2013 8:59 PM by Aditya Prakash RSS
Martin van Newbie 1 posts since
Feb 18, 2013
Currently Being Moderated

Feb 18, 2013 8:56 AM

Subquery in SQ Query Override causes 'Invalid Query' on validate

Hi All,

 

 

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

     ,   createdon                                                              AS   DATESTARTED              

     ,   NULL                                                                   AS   ACTIONEDBYID              

     ,   NULL                                                                   AS   ACTIONEDBYNAME            

     ,   NULL                                                                   AS   REQUIREDTIMETOCOMPLETE    

     ,   NULL                                                                   AS   DUEDATEGROSS              

     ,   NULL                                                                   AS   DUEDATE                   

     ,   NULL                                                                   AS   TIMETOCOMPLETE            

     ,   NULL                                                                   AS   DATE_LOAD

     ,   null                                                                   AS PLANNEDFINISH

     ,   null                                                                   AS PLANNEDSTART 

     ,   null                                                                   AS  TASKID

FROM

    ODS_CHP.ODS_CHP_DS_CUSTHIST_EXT rfa

)  

WHERE stepname IN ('RFA - Rapid Feasibility Assesment'

                  ,'FA - Feasibility Assesment'

                  ,'FA - Approved')

 

 

Regards,

 

Martin

More Like This

  • Retrieving data ...

Bookmarked By (0)