0 Replies Latest reply on Feb 27, 2019 1:00 AM by krishna12345

    oracle sql code ( subquery)- invalid identifier

    krishna12345

      Hi Gurus,

       

      I am just struck in the oracle query and would need bit help here

      Below is the scenario:

       

      In the select statement I have DURATION Column as

       

      ---before---

      0 as duration,

       

       

      -----I need------

       

      if DURATION IS NULL THEN MIS.DURATION/60"

       

       

      TRIED trial and errors but it says MIS.duration is invalid identifier" Is there any way I can get MIS column value to the select statement?

       

         set define off

       

       

      select * from (

       

       

      SELECT distinct

       

       

      T1.CSH_ID as ID,

       

       

      to_char(t1.history_date, 'DDMONYYYY:HH24:MI:SS') as ACTION_DATE,

       

       

                0 as DURATION,

       

       

      -------FOR THE ABOVE COLUMN I WANT----------

       

       

      ***" IF DURATION IS NULL THEN MIS.DURATION/60 as ACTION_DATE,"***

       

       

                     t1.WAC_ID,

       

       

                t2.CASE_ACTIVE

       

       

                 FROM

      (select t1.* from CPS_TACT.CPS_CASE_HISTORY T1

       

       

      left join (select * from CPS_TACT.cps_user  where cps_system_user = 'Y')

       

       

      u on u.usr_id = t1.usr_id

       

       

      where lendnet_send ='Y' or (notes like 'Diarised%' and u.cps_system_user = 'Y') ) t1

       

       

        left JOIN

       

       

      (SELECT t1.* ,t3.DESCRIPTION AS CPS_TASK_TO

       

       

      FROM CPS_TACT.CPS_CASE_AUDIT t1 LEFT JOIN CPS_TACT.CPS_PROCESS_TASK T2

       

       

      ON (T1.CURRENT_TASK = T2.PTS_ID)

       

       

      LEFT JOIN CPS_TACT.CPS_TASK T3

       

       

      ON (T2.TAS_ID = T3.TAS_ID)

       

       

      where t1.case_locked_date is null

       

       

          )t2

       

       

      ON (t1.CSE_ID = t2.CSE_ID) AND (t1.history_DATE between t2.DML_DATE -1/(24*3600) and t2.DML_DATE +1/(24*3600) )

       

       

        LEFT JOIN CPS_TACT.CPS_CASE_AUDIT f8 ON (t1.CSE_ID = f8.CSE_ID)

       

       

        LEFT JOIN CPS_TACT.CPS_ACCOUNT t3 ON (t3.CA_ID = f8.CA_ID)

       

       

        LEFT JOIN CPS_TACT.CPS_USER T4 ON (T1.USR_ID = T4.USR_ID)

       

       

      LEFT JOIN CPS_TACT.CPS_WORK_ACTION T5 ON (T1.WAC_ID = T5.WAC_ID)

       

       

      LEFT JOIN CPS_TACT.CPS_PROCESS_TASK T6 ON (T1.PTS_ID = T6.PTS_ID)

      LEFT JOIN CPS_TACT.CPS_TASK T7 ON (T6.TAS_ID = T7.TAS_ID)

       

       

      LEFT JOIN CPS_TACT.CPS_PROCESS T8 ON (T8.PRC_ID = T6.PRC_ID)

       

       

      LEFT JOIN CPS_TACT.CPS_WORK_GROUP T9 ON (T1.BRL_ID = T9.BRL_ID)

       

       

                

            WHERE

       

       

      t1.history_DATE >=sysdate-1

       

       

       

       

        AND T1.CSE_ID != 0

       

       

      AND t8.CPS_NAME = 'LendNet Mortgage Deals'

       

       

      --order by history_date

       

       

      ) t10

       

       

      LEFT JOIN (select * from CPS_tact.MIS_USER_PROCESS_TASK_ACTIONS MIS

       

       

      /* where action_DATE >= '01Jul2015:0:0:0'DT */

       

       

      ) MIS

       

       

            ON TO_TIMESTAMP(MIS.ACTION_DATE,'DD-MON-YYYY HH24:MI:SS') between to_TIMESTAMP(T10.action_date,'DDMONYYYY:HH24:MI:SS') -1/(24*3600) and TO_TIMESTAMP(T10.action_date,'DDMONYYYY:HH24:MI:SS') + 1/(24*3600) 

       

       

           -- ON MIS.ACTION_DATE = T10.action_date

       

       

            AND T10.CSE_ID = MIS.CSE_ID AND MIS.PTS_ID = T10.PTS_ID

       

       

       

       

      Thanks