SELECT DISTINCT L.FN_NUMBER VOO,
(L.AC_OWNER)||
(L.FN_NUMBER) NUMERO,
L.LEG_STATE SITUACAO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( L.DAY_OF_ORIGIN - 3/24, 'DD/MM/YYYY' ) ELSE TO_CHAR( L.DAY_OF_ORIGIN , 'DD/MM/YYYY' ) END) DATA,
L.DEP_AP_SCHED ORIGEM_PLANEJADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR(L.DEP_SCHED_DT- 3/24, 'DD/MM/YYYY' ) ELSE TO_CHAR(L.DEP_SCHED_DT, 'DD/MM/YYYY' ) END) DECOLAGEM_DATA_PLANEJADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR(L.DEP_SCHED_DT- 3/24, 'HH24:MI' ) ELSE TO_CHAR(L.DEP_SCHED_DT,'HH24:MI') END) DECOLAGEM_HORA_PLANEJADO,
L.ARR_AP_SCHED DESTINO_PLANEJADO,
TO_CHAR(L.ARR_SCHED_DT ,'DD/MM/YYYY') POUSO_DATA_PLANEJADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR(L.ARR_SCHED_DT- 3/24, 'HH24:MI' ) ELSE TO_CHAR(L.ARR_SCHED_DT ,'HH24:MI')END) POUSO_HORA_PLANEJADO,
L.DEP_AP_ACTUAL ORIGEM_EXECUTADO,
L.ARR_AP_ACTUAL DESTINO_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.OFFBLOCK_DT - 3/24, 'HH24:MI:SS' ) ELSE TO_CHAR( T.OFFBLOCK_DT ,'HH24:MI:SS' ) END) PARTIDA_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.AIRBORNE_DT - 3/24, 'DD/MM/YYYY' ) ELSE TO_CHAR( T.AIRBORNE_DT , 'DD/MM/YYYY' ) END) DECOLAGEM_DATA_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.AIRBORNE_DT - 3/24, 'HH24:MI:SS' ) ELSE TO_CHAR( T.AIRBORNE_DT , 'HH24:MI:SS' ) END) DECOLAGEM_HORA_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.LANDING_DT - 3/24, 'DD/MM/YYYY' ) ELSE TO_CHAR( T.LANDING_DT , 'DD/MM/YYYY' ) END) POUSO_DATA_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.LANDING_DT - 3/24, 'HH24:MI:SS' ) ELSE TO_CHAR( T.LANDING_DT , 'HH24:MI:SS' ) END) POUSO_HORA_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.ONBLOCK_DT - 3/24, 'HH24:MI:SS' ) ELSE TO_CHAR( T.ONBLOCK_DT , 'HH24:MI:SS' ) END) CORTE_EXECUTADO,
L.LEG_NO SUBPAR,
(floor((to_date(TO_CHAR(T.LANDING_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.AIRBORNE_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*24)||':'||
round(round((to_date(TO_CHAR(T.LANDING_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.AIRBORNE_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*1440)-
floor((to_date(TO_CHAR(T.LANDING_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.AIRBORNE_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*24)*60)) FLIGHT_TIME,
(floor((to_date(TO_CHAR(T.ONBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.OFFBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*24)||':'||
round(round((to_date(TO_CHAR(T.ONBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.OFFBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*1440)- floor((to_date(TO_CHAR(T.ONBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.OFFBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*24)*60)) OPERATION_TIME,
(L.DEP_SCHED_DT - L.DEP_DT )*24*60 ATRASO_DECOLAGEM,
(CASE WHEN ((L.DEP_SCHED_DT - L.DEP_DT )*24*60) > 0 THEN 'ATRASADO' ELSE 'ADIANTADO' END) STATUS_DEC,
(L.ARR_SCHED_DT - L.ARR_DT )*24*60 ATRASO_POUSO,
(CASE WHEN ((L.ARR_SCHED_DT - L.ARR_DT )*24*60) > 0 THEN 'ATRASADO' ELSE 'ADIANTADO' END) STATUS_POU,
V.SEATS_C CFG,
L.AC_REGISTRATION,
SUBSTR(L.AC_REGISTRATION,LENGTH(L.AC_REGISTRATION)-3,3) PREF,
(SELECT CNL1.CODE FROM CNL1 WHERE CNL1.LEG_NO = L.LEG_NO AND (ROWNUM = 1) ) NLC1,
(SELECT CNL1.DTIME FROM CNL1 WHERE CNL1.LEG_NO = L.LEG_NO AND (ROWNUM = 1)) NLT1,
(SELECT CNL2.CODE FROM CNL2 WHERE CNL2.LEG_NO = L.LEG_NO AND (ROWNUM =1)) NLC2,
(SELECT CNL2.DTIME FROM CNL2 WHERE CNL2.LEG_NO = L.LEG_NO AND (ROWNUM = 1)) NLT2,
(SELECT CNL2.DESCRIPTION FROM CNL2 WHERE CNL2.LEG_NO = L.LEG_NO AND (ROWNUM = 1)) DESCRIPTION,
L.AC_SUBTYPE
FROM LEG L
LEFT JOIN AC_VERSION V ON V.AC_VERSION = L.AC_VERSION AND V.AC_SUBTYPE = L.AC_SUBTYPE
LEFT JOIN LEG_TIMES T ON L.LEG_NO = T.LEG_NO
WHERE
L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 7450 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 7499 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 7600 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 7699 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 8500 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 8999 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9060 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9069 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9090 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9099 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9100 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9129 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9252 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9259 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9274 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9287 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9296 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9299 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 1200 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 1399 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 1500 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 2999 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9030 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9059 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9070 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9089 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9200 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9239 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9240 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9251 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9260 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9273 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9288 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9299 END OR
L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9296 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9295 END OR
L.LEG_TYPE = CASE :PARAM4 WHEN '2' THEN 'C' END OR
L.LEG_TYPE = CASE WHEN :PARAM4 IS NULL THEN L.LEG_TYPE END AND
to_date(L.DAY_OF_ORIGIN,'DD/MM/YYYY') >= to_date(:PARAM1,'DD/MM/YYYY') AND to_date(L.DAY_OF_ORIGIN,'DD/MM/YYYY') <= to_date(:PARAM2,'DD/MM/YYYY')
Where Não filtra depois de um OR
-
- Rank: Estagiário Pleno
- Mensagens: 6
- Registrado em: Sex, 17 Abr 2009 8:05 am
- Localização: São José dos Campos - SP
Rafael Willwohl Salgado
BEM, ABAIXO SE O PARAMETRO 4 VAI NULL E CAI NA ÚTIMA OPÇÃO A COSULTA FAZ O FILTRO NO FINAL PELA DATA E OS PARÂMETROS 1 E 2 MAS SE ELE CAI EM UM DOS CASE O FILTRO DE DATA NÃO É FEITO E ELE RETORNA TODOS OS REGISTROS DO BANCO NO INTERVÁ-LO DETERMINADO. ALGUÉM TEM ALGUMA DICA PARA ISSO FUNCIONAR?????
SE ALGUÉM SOUBER DE UMA FORMA DE SIMPLIFICAR QUALQUER PARTE, POR FAVOR POSTEM!!!!
-
- Rank: Estagiário Pleno
- Mensagens: 6
- Registrado em: Sex, 17 Abr 2009 8:05 am
- Localização: São José dos Campos - SP
Rafael Willwohl Salgado
SELECT DISTINCT L.FN_NUMBER VOO,
(L.AC_OWNER)||
(L.FN_NUMBER) NUMERO,
L.LEG_STATE SITUACAO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( L.DAY_OF_ORIGIN - 3/24, 'DD/MM/YYYY' ) ELSE TO_CHAR( L.DAY_OF_ORIGIN , 'DD/MM/YYYY' ) END) DATA,
L.DEP_AP_SCHED ORIGEM_PLANEJADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR(L.DEP_SCHED_DT- 3/24, 'DD/MM/YYYY' ) ELSE TO_CHAR(L.DEP_SCHED_DT, 'DD/MM/YYYY' ) END) DECOLAGEM_DATA_PLANEJADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR(L.DEP_SCHED_DT- 3/24, 'HH24:MI' ) ELSE TO_CHAR(L.DEP_SCHED_DT,'HH24:MI') END) DECOLAGEM_HORA_PLANEJADO,
L.ARR_AP_SCHED DESTINO_PLANEJADO,
TO_CHAR(L.ARR_SCHED_DT ,'DD/MM/YYYY') POUSO_DATA_PLANEJADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR(L.ARR_SCHED_DT- 3/24, 'HH24:MI' ) ELSE TO_CHAR(L.ARR_SCHED_DT ,'HH24:MI')END) POUSO_HORA_PLANEJADO,
L.DEP_AP_ACTUAL ORIGEM_EXECUTADO,
L.ARR_AP_ACTUAL DESTINO_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.OFFBLOCK_DT - 3/24, 'HH24:MI:SS' ) ELSE TO_CHAR( T.OFFBLOCK_DT ,'HH24:MI:SS' ) END) PARTIDA_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.AIRBORNE_DT - 3/24, 'DD/MM/YYYY' ) ELSE TO_CHAR( T.AIRBORNE_DT , 'DD/MM/YYYY' ) END) DECOLAGEM_DATA_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.AIRBORNE_DT - 3/24, 'HH24:MI:SS' ) ELSE TO_CHAR( T.AIRBORNE_DT , 'HH24:MI:SS' ) END) DECOLAGEM_HORA_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.LANDING_DT - 3/24, 'DD/MM/YYYY' ) ELSE TO_CHAR( T.LANDING_DT , 'DD/MM/YYYY' ) END) POUSO_DATA_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.LANDING_DT - 3/24, 'HH24:MI:SS' ) ELSE TO_CHAR( T.LANDING_DT , 'HH24:MI:SS' ) END) POUSO_HORA_EXECUTADO,
(CASE :PARAM3 WHEN '03' THEN TO_CHAR( T.ONBLOCK_DT - 3/24, 'HH24:MI:SS' ) ELSE TO_CHAR( T.ONBLOCK_DT , 'HH24:MI:SS' ) END) CORTE_EXECUTADO,
L.LEG_NO SUBPAR,
(floor((to_date(TO_CHAR(T.LANDING_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.AIRBORNE_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*24)||':'||
round(round((to_date(TO_CHAR(T.LANDING_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.AIRBORNE_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*1440)-
floor((to_date(TO_CHAR(T.LANDING_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.AIRBORNE_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*24)*60)) FLIGHT_TIME,
(floor((to_date(TO_CHAR(T.ONBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.OFFBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*24)||':'||
round(round((to_date(TO_CHAR(T.ONBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.OFFBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*1440)- floor((to_date(TO_CHAR(T.ONBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS') - to_date(TO_CHAR(T.OFFBLOCK_DT,'DD/MM/RRRR HH24:MI:SS'),'DD/MM/RRRR HH24:MI:SS'))*24)*60)) OPERATION_TIME,
(L.DEP_SCHED_DT - L.DEP_DT )*24*60 ATRASO_DECOLAGEM,
(CASE WHEN ((L.DEP_SCHED_DT - L.DEP_DT )*24*60) > 0 THEN 'ATRASADO' ELSE 'ADIANTADO' END) STATUS_DEC,
(L.ARR_SCHED_DT - L.ARR_DT )*24*60 ATRASO_POUSO,
(CASE WHEN ((L.ARR_SCHED_DT - L.ARR_DT )*24*60) > 0 THEN 'ATRASADO' ELSE 'ADIANTADO' END) STATUS_POU,
V.SEATS_C CFG,
L.AC_REGISTRATION,
SUBSTR(L.AC_REGISTRATION,LENGTH(L.AC_REGISTRATION)-3,3) PREF,
(SELECT CNL1.CODE FROM CNL1 WHERE CNL1.LEG_NO = L.LEG_NO AND (ROWNUM = 1) ) NLC1,
(SELECT CNL1.DTIME FROM CNL1 WHERE CNL1.LEG_NO = L.LEG_NO AND (ROWNUM = 1)) NLT1,
(SELECT CNL2.CODE FROM CNL2 WHERE CNL2.LEG_NO = L.LEG_NO AND (ROWNUM =1)) NLC2,
(SELECT CNL2.DTIME FROM CNL2 WHERE CNL2.LEG_NO = L.LEG_NO AND (ROWNUM = 1)) NLT2,
(SELECT CNL2.DESCRIPTION FROM CNL2 WHERE CNL2.LEG_NO = L.LEG_NO AND (ROWNUM = 1)) DESCRIPTION,
L.AC_SUBTYPE
FROM LEG L
LEFT JOIN AC_VERSION V ON V.AC_VERSION = L.AC_VERSION AND V.AC_SUBTYPE = L.AC_SUBTYPE
LEFT JOIN LEG_TIMES T ON L.LEG_NO = T.LEG_NO
WHERE
((L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 7450 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 7499 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 7600 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 7699 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 8500 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 8999 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9060 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9069 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9090 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9099 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9100 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9129 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9252 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9259 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9274 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9287 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '0' THEN 9296 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '0' THEN 9299 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 1200 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 1399 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 1500 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 2999 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9030 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9059 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9070 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9089 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9200 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9239 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9240 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9251 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9260 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9273 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9288 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9299 END) OR
(L.FN_NUMBER >= CASE :PARAM4 WHEN '1' THEN 9296 END AND L.FN_NUMBER <= CASE :PARAM4 WHEN '1' THEN 9295 END) OR
(L.LEG_TYPE = CASE :PARAM4 WHEN '2' THEN 'C' END) OR
(L.LEG_TYPE = CASE WHEN :PARAM4 IS NULL THEN L.LEG_TYPE END)) AND
to_date(L.DAY_OF_ORIGIN,'DD/MM/YYYY') >= to_date(:PARAM1,'DD/MM/YYYY') AND to_date(L.DAY_OF_ORIGIN,'DD/MM/YYYY') <= to_date(:PARAM2,'DD/MM/YYYY')
-
- Informação
-
Quem está online
Usuários navegando neste fórum: Nenhum usuário registrado e 18 visitantes