我的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 `
当我在oracle-sqldeveloper中编译这些代码时，这些代码非常有效。但当我在代码点火器中转换为活动记录时，代码不起作用。这是我用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");
当我试图编译转换后的代码时，它总是产生这样的结果
错误编号：904
ORA-00904："DEP"。"AIRPORT"：无效标识符
转换后的代码中有遗漏吗？任何建议都将不胜感激。感谢
您可以尝试使用Codeigniterquery((函数。试试这个：
$your_query = "paste here your ORACLE SQL";
$result = $CI->db->query($your_query)->result_array();
此函数返回结果数组。此处的文档https://www.codeigniter.com/userguide3...