如何将这个复杂的查询转换为活动记录CODEIGNITER PHP?

I have these queries in my ORACLE SQL:

SELECT dep.airport   AS DEPAIRPORT, 
   dep.scheduled AS SCHEDULED, 
   dep.ontime    AS ONTIME, 
   arr.arrontime AS ARRONTIME FROM   (SELECT AIRPORT,sum(FIRSTFLIGHT) AS FIRSTFLIGHT, SUM(OTPFIRSTFLIGHT) as OTPFIRSTFLIGHT, SUM(ZMDFIRSTFLIGHT) as ZMDFIRSTFLIGHT,
        COUNT(SCHEDULED) AS SCHEDULED,COUNT(ONTIME) AS ONTIME, COUNT(SCHEDARR) AS SCHEDARR FROM (SELECT DEPAIRPORT as AIRPORT,
    MAX(case when A.STATUS = 'Scheduled' and 
    A.ACTUAL_BLOCKOFF is not null then 1 else NULL END) as SCHEDULED,
    MAX(case when ((A.ACTUAL_BLOCKOFF+ interval '7' hour) - (A.SCHEDULED_DEPDT+ interval '7' hour))*24*60 <= '+000000015 00:00:00.000000000' and 
    A.ACTUAL_BLOCKOFF is not null then 1 else NULL END) as ONTIME,
    MAX(case when  A.STATUS = 'Scheduled' and A.ACTUAL_BLOCKON is not null then 1 else NULL END) as SCHEDARR
FROM TABLEA A left join TABLEB B ON A.FLIGHTLEGREF = B.FLIGHTLEGREF
where A.SERVICETYPE IN ('J','G') and to_char(SCHEDULED_DEPDT + interval '7' hour, 'yyyy-mm-dd') between '2018-07-15' and '2018-07-21' 
and A.LATEST_ARRIVALAIRPORT != A.SCHED_DEPARTUREAIRPORT and (SUFFIX != 'R' or SUFFIX IS NULL)
group by DEPAIRPORT, AIRCRAFTREG,SCHEDULED_DEPDT,ACTUAL_BLOCKOFF,ACTUAL_BLOCKON,AIRCRAFTTYPE,SCHEDULED_ARRDT)GROUP BY AIRPORT) dep
   left join (SELECT sched_arrivalairport AS airport, 
            count(CASE 
                    WHEN( ( a.actual_blockon + interval '7' hour ) - ( 
                                a.scheduled_arrdt + interval '7' hour ) ) * 
                        24 * 
                        60 
                        <= 
                              '+000000015 00:00:00.000000000' 
                        AND a.actual_blockon IS NOT NULL THEN 1 
                  END)     AS arrontime 
     FROM   TABLEA A 
     where A.SERVICETYPE IN ('J','G') and to_char(SCHEDULED_DEPDT + interval '7' hour, 'yyyy-mm-dd') between '2018-07-15' and '2018-07-21' 
     and A.LATEST_ARRIVALAIRPORT != A.SCHED_DEPARTUREAIRPORT and (SUFFIX != 'R' or SUFFIX IS NULL)
     GROUP  BY sched_arrivalairport)arr  
          ON ( dep.airport = arr.airport )order by dep.airport `

and those codes works perfectly when I compiled it in oracle sql developer. but when I converted to active record in code igniter the code didnt works. here is my converted code in php:

$CI->db->select("DEP.AIRPORT   AS DEPAIRPORT, 
   DEP.scheduled AS SCHEDULED, 
   DEP.ontime    AS ONTIME, 
   ARR.arrontime AS ARRONTIME");
$CI->db->from("(");
$subq3 = $CI->db->get_compiled_select();
//$CI->db->reset_query();
$CI->db->select("DEPAIRPORT,sum(FIRSTFLIGHT) AS FIRSTFLIGHT, SUM(OTPFIRSTFLIGHT) as OTPFIRSTFLIGHT, SUM(ZMDFIRSTFLIGHT) as ZMDFIRSTFLIGHT,"
        . "SUM(SCHEDULED) AS SCHEDULED,SUM(ONTIME) AS ONTIME");
 $CI->db->from("(SELECT DEPAIRPORT as DEPAIRPORT,
    MAX(case when A.STATUS = 'Scheduled' and 
    A.ACTUAL_BLOCKOFF is not null then 1 else NULL END) as SCHEDULED,
    MAX(case when ((A.ACTUAL_BLOCKOFF+ interval '7' hour) - (A.SCHEDULED_DEPDT+ interval '7' hour))*24*60 <= '+000000015 00:00:00.000000000' and 
    A.ACTUAL_BLOCKOFF is not null then 1 else NULL END) as ONTIME FROM TABLEA A");$CI->db->join('TABLEB B', 'A.FLIGHTLEGREF = B.FLIGHTLEGREF','left');
$CI->db->where("A.LATEST_ARRIVALAIRPORT != A.SCHED_DEPARTUREAIRPORT");    $CI->db->where(divertStatus());
if ($stnService == "dom"){
$CI->db->where("A.SCHED_DEPARTUREAIRPORT IN (".$domstn.")");
}
if ($stnService == "int"){
$CI->db->where("A.SCHED_DEPARTUREAIRPORT IN (".$intstn.")");
}
if ($aptMgmt == "ap1"){
$CI->db->where_in("A.SCHED_DEPARTUREAIRPORT",$AP1);
}
if ($aptMgmt == "ap2"){
$CI->db->where_in("A.SCHED_DEPARTUREAIRPORT",$AP2);
}
if(!is_null($date1) && !is_null($date2))
$CI->db->where("to_char(SCHEDULED_DEPDT + interval '7' hour, 'yyyy-mm-dd') between '".$date1."' and '".$date2."'");
else $CI->db->where($timeLimit);
$CI->db->where("A.SERVICETYPE IN ('J','G')");
$CI->db->group_by("DEPAIRPORT, AIRCRAFTREG,SCHEDULED_DEPDT,ACTUAL_BLOCKOFF,ACTUAL_BLOCKON,AIRCRAFTTYPE,SCHEDULED_ARRDT) GROUP BY DEPAIRPORT) DEP ");
$subq1 = $CI->db->get_compiled_select();
//$CI->db->reset_query();
$CI->db->select("SCHED_ARRIVALAIRPORT AS AIRPORT, 
            count(CASE 
                    WHEN( ( A.ACTUAL_BLOCKON + interval '7' hour ) - ( 
                                A.SCHEDULED_ARRDT + interval '7' hour ) ) * 
                        24 * 
                        60 
                        <= 
                              '+000000015 00:00:00.000000000' 
                        AND A.ACTUAL_BLOCKON IS NOT NULL THEN 1 
                  END)     AS arrontime ");
$CI->db->from("TABLEA A ");
if(!is_null($date1) && !is_null($date2))
$CI->db->where("to_char(SCHEDULED_DEPDT + interval '7' hour, 'yyyy-mm-dd') between '".$date1."' and '".$date2."'");
else $CI->db->where($timeLimit);
$CI->db->where("A.SERVICETYPE IN ('J','G')");
$CI->db->where("A.LATEST_ARRIVALAIRPORT != A.SCHED_DEPARTUREAIRPORT");    $CI->db->where(divertStatus());
$CI->db->group_by("SCHED_ARRIVALAIRPORT)arr ON DEP.AIRPORT = ARR.AIRPORT");
$CI->db->order_by("DEP.DEPAIRPORT ASC");
$subq2 = $CI->db->get_compiled_select();
//$CI->db->reset_query();
$CI->db->query("$subq3 $subq1 LEFT JOIN ($subq2");

when I tried to compile the converted code it always produces result like this

Error Number: 904

ORA-00904: "DEP"."AIRPORT": invalid identifier

Did I miss something in my converted code ? Any suggestion would be appreciated. Thankss

查看全部
drsb77336
drsb77336
2018/07/25 02:42
  • oracle
  • codeigniter
  • activerecord
  • php
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

1个回复