Skip to content

Instantly share code, notes, and snippets.

@dtelaroli

dtelaroli/entregue.sql

Created March 20, 2015 19:10
Show Gist options
  • Save dtelaroli/c771660f897a4403404e to your computer and use it in GitHub Desktop.
Save dtelaroli/c771660f897a4403404e to your computer and use it in GitHub Desktop.
Download ZIP
Diferença entre um sql que necessário e o entregue #estude #sql #soudev
Raw
entregue.sql
ALTER FUNCTION [dbo].[return_tabela_sap]
(
@DATA DATE,
@AERONAVE VARCHAR(6)
)
RETURNS @RETORNO TABLE (
ITEM INTEGER,
ROTA VARCHAR(6),
AIRCRAFT VARCHAR(6),
EQUIPAMENTO VARCHAR(50),
ATENDIMENTO VARCHAR(50),
ESCALA VARCHAR(50),
DESCRICAO_OPERACAO VARCHAR(50),
GRUPO_CODIGOS VARCHAR(50),
CODIGO_OPERACAO VARCHAR(50),
TRAP VARCHAR(50),
DATA_INICIAL VARCHAR(50),
HORA_INICIAL VARCHAR(50),
DATA_FINAL VARCHAR(50),
HORA_FINAL VARCHAR(50),
HORAS_VOADAS VARCHAR(50),
HORAS_GLOSADAS VARCHAR(50),
QTD_ITEM_AVULSO VARCHAR(50),
OBSERVACOES VARCHAR(50))
AS
BEGIN
DECLARE @ITEM INTEGER,
@ROUTE VARCHAR(6),
@AIRCRAFT VARCHAR(6),
@EQUIPAMENTO VARCHAR(50),
@ATENDIMENTO VARCHAR(50),
@ESCALA VARCHAR(50),
@DESCRICAO_OPERACAO VARCHAR(50),
@GRUPO_CODIGOS VARCHAR(50),
@CODIGO_OPERACAO VARCHAR(50),
@TRAP VARCHAR(50),
@DATA_INICIAL VARCHAR(50),
@HORA_INICIAL VARCHAR(50),
@DATA_FINAL VARCHAR(50),
@HORA_FINAL VARCHAR(50),
@HORAS_VOADAS VARCHAR(50),
@HORAS_GLOSADAS VARCHAR(50),
@QTD_ITEM_AVULSO VARCHAR(50),
@OBSERVACOES VARCHAR(50),
@ROTA VARCHAR(6)
DECLARE TAB CURSOR FOR
SELECT DISTINCT afs.route
FROM aims_flight_Schedule afs
WHERE afs.chr = 'foo'
AND afs.reg = @aeronave
AND SUBSTRING(afs.route,1,3) IN (SELECT name FROM aims_routes WHERE way_point_id = 4)
AND afs.fl_date = @data
AND (afs.disable_flight != 1 or afs.disable_flight is null)
OPEN TAB
FETCH NEXT FROM TAB INTO @ROUTE
;
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE SAP CURSOR FOR
SELECT TOP 1 ROW_NUMBER() OVER(PARTITION BY ais.Log_Page_No ORDER BY ais.atd ASC) AS item,
ais.route,
ais.reg,
ct.client_code_aircraft as equipamento,
ais.mission as atendimento,
(SELECT top 1 wp.prefix
FROM way_points wp
WHERE wp.aims_route = ais.dep) as escala,
'OA30 - ACIONAMENTO' as descricao_operacao,
'OPEAER' as grupo_codigos,
'OA30' as codigo_operacao,
'AE' as trap,
CONVERT(DATE,fl_date) as data_inicial,
CASE
WHEN ais.atd = '00:00'
THEN ais.atd
ELSE
CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.atd), '-0'+dbo.return_summer_time(ais.fl_date)))))
END hora_inicial,
CONVERT(DATE,fl_date) as data_final,
CASE
WHEN ais.toff = '00:00'
THEN ais.toff
ELSE CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.toff), '-0'+dbo.return_summer_time(ais.fl_date)))))
END hora_final,
'' as horas_voadas,
'' as horas_glosadas,
'' as qtd_item_avulso,
'' as observacoes
FROM aims_flight_Schedule ais
JOIN aircrafts air ON (air.prefix = ais.reg)
JOIN contract_aircrafts ca ON (ca.aircraft_id = air.id)
JOIN contracts ct ON (ct.id = ca.contract_id AND @data BETWEEN ct.date_start AND ct.date_end)
WHERE ais.chr = 'foo'
AND ais.reg = @aeronave
AND SUBSTRING(ais.route,1,3) IN (SELECT name FROM aims_routes WHERE way_point_id = 4)
AND ais.fl_date = @data
AND (ais.disable_flight != 1 or ais.disable_flight is null)
AND ais.route = @ROUTE
UNION
SELECT TOP 1 '2' AS item,
ais.route,
ais.reg,
ct.client_code_aircraft as equipamento,
ais.mission as atendimento,
(SELECT top 1 wp.prefix
FROM way_points wp
WHERE wp.aims_route = ais.dep) as escala,
'PA01 - DECOLAGEM' as descricao_operacao,
'OPSDUA' as grupo_codigos,
'PA01' as codigo_operacao,
'AE' as trap,
CONVERT(DATE,fl_date) as data_inicial,
CASE
WHEN ais.toff = '00:00'
THEN ais.toff
ELSE CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.toff), '-0'+dbo.return_summer_time(ais.fl_date)))))
END hora_inicial,
CONVERT(DATE,fl_date) as data_final,
'' as hora_final,
'' as horas_voadas,
'' as horas_glosadas,
'' as qtd_item_avulso,
'' as observacoes
FROM aims_flight_Schedule ais
JOIN aircrafts air ON (air.prefix = ais.reg)
JOIN contract_aircrafts ca ON (ca.aircraft_id = air.id)
JOIN contracts ct ON (ct.id = ca.contract_id AND @data BETWEEN ct.date_start AND ct.date_end)
WHERE ais.chr = 'foo'
AND ais.reg = @aeronave
AND ais.fl_date = @data
AND (ais.disable_flight != 1 or ais.disable_flight is null)
AND ais.route = @ROUTE
UNION
SELECT TOP 1 '3' AS item,
ais.route,
ais.reg,
ct.client_code_aircraft as equipamento,
ais.mission as atendimento,
(SELECT top 1 wp.prefix
FROM way_points wp
WHERE wp.aims_route = ais.arr) as escala,
CASE
WHEN t_type = 'SCHEDULED SERVICE'
THEN 'OA08 - EM VOO REGULAR PAX'
WHEN t_type = 'SCHEDULED CARGO'
THEN 'OA09 - EM VOO REGULAR CARGA'
WHEN t_type = 'FERRY FOO'
THEN 'OA14 - VÔO DE TRANSLADO'
END descricao_operacao,
'OPEAER' as grupo_codigos,
'OA08' as codigo_operacao,
'AE' as trap,
CONVERT(DATE,fl_date) as data_inicial,
CASE
WHEN ais.toff = '00:00'
THEN ais.toff
ELSE CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.toff), '-0'+dbo.return_summer_time(ais.fl_date)))))
END hora_inicial,
CONVERT(DATE,fl_date) as data_final,
CASE
WHEN ais.tdown = '00:00'
THEN ais.tdown
ELSE CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.tdown), '-0'+dbo.return_summer_time(ais.fl_date)))))
END as hora_final,
'' as horas_voadas,
'' as horas_glosadas,
'' as qtd_item_avulso,
'' as observacoes
FROM aims_flight_Schedule ais
JOIN aircrafts air ON (air.prefix = ais.reg)
JOIN contract_aircrafts ca ON (ca.aircraft_id = air.id)
JOIN contracts ct ON (ct.id = ca.contract_id AND @data BETWEEN ct.date_start AND ct.date_end)
WHERE ais.chr = 'foo'
AND ais.reg = @aeronave
AND SUBSTRING(ais.route,1,3) IN (SELECT name FROM aims_routes WHERE way_point_id = 4)
AND ais.fl_date = @data
AND (ais.disable_flight != 1 or ais.disable_flight is null)
AND ais.route = @ROUTE
UNION
SELECT '4' AS item,
ais.route, ais.reg,
ct.client_code_aircraft as equipamento,
ais.mission as atendimento,
(SELECT top 1 wp.prefix
FROM way_points wp
WHERE wp.aims_route = ais.arr) as escala,
CASE
WHEN t_type = 'SCHEDULED SERVICE'
THEN 'OA08 - EM VOO REGULAR PAX'
WHEN t_type = 'SCHEDULED CARGO'
THEN 'OA09 - EM VOO REGULAR CARGA'
WHEN t_type = 'FERRY FOO'
THEN 'OA14 - VÔO DE TRANSLADO'
END descricao_operacao,
'OPEAER' as grupo_codigos,
'OA08' as codigo_operacao,
'AE' as trap,
CONVERT(DATE,fl_date) as data_inicial,
CASE
WHEN ais.atd = '00:00'
THEN ais.atd
ELSE CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.atd), '-0'+dbo.return_summer_time(ais.fl_date)))))
END hora_inicial,
CONVERT(DATE,fl_date) as data_final,
CASE
WHEN ais.tdown = '00:00'
THEN ais.tdown
ELSE CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.tdown), '-0'+dbo.return_summer_time(ais.fl_date)))))
END as hora_final,
'' as horas_voadas,
'' as horas_glosadas,
'' as qtd_item_avulso,
'' as observacoes
FROM aims_flight_Schedule ais
JOIN aircrafts air ON (air.prefix = ais.reg)
JOIN contract_aircrafts ca ON (ca.aircraft_id = air.id)
JOIN contracts ct ON (ct.id = ca.contract_id AND @data BETWEEN ct.date_start AND ct.date_end)
WHERE ais.chr = 'foo'
AND ais.reg = @aeronave
AND SUBSTRING(ais.route,1,3) IN (SELECT name FROM aims_routes WHERE way_point_id = 4)
AND ais.fl_date = @data
AND (ais.disable_flight != 1 or ais.disable_flight is null)
AND ais.atd < (SELECT MAX (afs.atd)
FROM aims_flight_Schedule afs
WHERE afs.chr = 'foo'
AND afs.reg = @aeronave
AND SUBSTRING(afs.route,1,3) IN (SELECT name FROM aims_routes WHERE way_point_id = 4)
AND afs.fl_date = @data
AND (afs.disable_flight != 1 or afs.disable_flight is null)
AND afs.route = ais.route
GROUP BY afs.route)
AND ais.atd > (SELECT MIN (afs.atd)
FROM aims_flight_Schedule afs
WHERE afs.chr = 'foo'
AND afs.reg = @aeronave
AND SUBSTRING(afs.route,1,3) IN (SELECT name FROM aims_routes WHERE way_point_id = 4)
AND afs.fl_date = @data
AND (afs.disable_flight != 1 or afs.disable_flight is null)
AND afs.route = ais.route
GROUP BY afs.route)
AND ais.route = @ROUTE
UNION
SELECT TOP 1 SUM(5) OVER(PARTITION BY ais.Log_Page_No, ais.route ORDER BY ais.atd DESC) AS item,
ais.route,
ais.reg,
ct.client_code_aircraft as equipamento,
ais.mission as atendimento,
(SELECT top 1 wp.prefix
FROM way_points wp
WHERE wp.aims_route = ais.arr) as escala,
CASE
WHEN t_type = 'SCHEDULED SERVICE'
THEN 'OA08 - EM VOO REGULAR PAX'
WHEN t_type = 'SCHEDULED CARGO'
THEN 'OA09 - EM VOO REGULAR CARGA'
WHEN t_type = 'FERRY FOO'
THEN 'OA14 - VÔO DE TRANSLADO'
END descricao_operacao,
'OPEAER' as grupo_codigos,
'OA08' as codigo_operacao,
'AE' as trap,
CONVERT(DATE,fl_date) as data_inicial,
CASE
WHEN ais.atd = '00:00'
THEN ais.atd
ELSE CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.atd), '-0'+dbo.return_summer_time(ais.fl_date)))))
END hora_inicial,
CONVERT(DATE,fl_date) as data_final,
CASE
WHEN ais.tdown = '00:00'
THEN ais.tdown
ELSE CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.tdown), '-0'+dbo.return_summer_time(ais.fl_date)))))
END as hora_final,
'' as horas_voadas,
'' as horas_glosadas,
'' as qtd_item_avulso,
'' as observacoes
FROM aims_flight_Schedule ais
JOIN aircrafts air ON (air.prefix = ais.reg)
JOIN contract_aircrafts ca ON (ca.aircraft_id = air.id)
JOIN contracts ct ON (ct.id = ca.contract_id AND @data BETWEEN ct.date_start AND ct.date_end)
WHERE ais.chr = 'foo'
AND ais.reg = @aeronave
AND SUBSTRING(ais.route,1,3) IN (SELECT name FROM aims_routes WHERE way_point_id = 4)
AND ais.fl_date = @data
AND (ais.disable_flight != 1 or ais.disable_flight is null)
AND ais.route = @ROUTE
UNION
SELECT TOP 1 SUM(6) OVER(PARTITION BY ais.Log_Page_No, ais.route ORDER BY ais.atd DESC) AS item,
ais.route,
ais.reg,
ct.client_code_aircraft as equipamento,
ais.mission as atendimento,
(SELECT top 1 wp.prefix
FROM way_points wp
WHERE wp.aims_route = ais.arr) as escala,
'PA03 - POUSO' as descricao_operacao,
'OPSDUA' as grupo_codigos,
'PA03' as codigo_operacao,
'AE' as trap,
CONVERT(DATE,fl_date) as data_inicial,
CASE
WHEN ais.tdown = '00:00'
THEN ais.tdown
ELSE CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.tdown), '-0'+dbo.return_summer_time(ais.fl_date)))))
END hora_inicial,
CONVERT(DATE,fl_date) as data_final,
'' as hora_final,
'' as horas_voadas,
'' as horas_glosadas,
'' as qtd_item_avulso,
'' as observacoes
FROM aims_flight_Schedule ais
JOIN aircrafts air ON (air.prefix = ais.reg)
JOIN contract_aircrafts ca ON (ca.aircraft_id = air.id)
JOIN contracts ct ON (ct.id = ca.contract_id AND @data BETWEEN ct.date_start AND ct.date_end)
WHERE ais.chr = 'foo'
AND ais.reg = @aeronave
AND SUBSTRING(ais.route,1,3) IN (SELECT name FROM aims_routes WHERE way_point_id = 4)
AND ais.fl_date = @data
AND (ais.disable_flight != 1 or ais.disable_flight is null)
AND ais.route = @ROUTE
UNION
SELECT TOP 1 SUM(7) OVER(PARTITION BY ais.Log_Page_No, ais.route ORDER BY ais.atd DESC) AS item,
ais.route,
ais.reg,
ct.client_code_aircraft as equipamento,
ais.mission as atendimento,
(SELECT top 1 wp.prefix
FROM way_points wp
WHERE wp.aims_route = ais.arr) as escala,
'OA31 - CORTE' as descricao_operacao,
'OPEAER' as grupo_codigos,
'OA31' as codigo_operacao,
'AE' as trap,
CONVERT(DATE,fl_date) as data_inicial,
CASE
WHEN ais.tdown = '00:00'
THEN ais.tdown
ELSE CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.tdown), '-0'+dbo.return_summer_time(ais.fl_date)))))
END hora_inicial,
CONVERT(DATE,fl_date) as data_final,
CASE
WHEN ais.engine_off != '00:00'
THEN CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.engine_off), '-0'+dbo.return_summer_time(ais.fl_date)))))
ELSE ais.engine_off
END hora_final,
'' as horas_voadas,
'' as horas_glosadas,
'' as qtd_item_avulso,
'' as observacoes
FROM aims_flight_Schedule ais
JOIN aircrafts air ON (air.prefix = ais.reg)
JOIN contract_aircrafts ca ON (ca.aircraft_id = air.id)
JOIN contracts ct ON (ct.id = ca.contract_id AND @data BETWEEN ct.date_start AND ct.date_end)
WHERE ais.chr = 'foo'
AND ais.reg = @aeronave
AND SUBSTRING(ais.route,1,3) IN (SELECT name FROM aims_routes WHERE way_point_id = 4)
AND ais.fl_date = @data
AND (ais.disable_flight != 1 or ais.disable_flight is null)
AND ais.route = @ROUTE
UNION
SELECT TOP 1 SUM(8) OVER(PARTITION BY ais.Log_Page_No ORDER BY ais.atd DESC) AS item,
ais.route,
ais.reg,
ct.client_code_aircraft as equipamento,
ais.mission as atendimento,
(SELECT top 1 wp.prefix
FROM way_points wp
WHERE wp.aims_route = ais.arr) as escala,
'IA09 - DIF POUSO/DECOLAGEM' as descricao_operacao,
'OPEAER' as grupo_codigos,
'OA31' as codigo_operacao,
'AE' as trap,
CONVERT(DATE,fl_date) as data_inicial,
CASE
WHEN ais.tdown = '00:00'
THEN ais.tdown
ELSE CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.tdown), '-0'+dbo.return_summer_time(ais.fl_date)))))
END hora_inicial,
CONVERT(DATE,fl_date) as data_final,
CASE
WHEN ais.engine_off != '00:00'
THEN CONVERT(VARCHAR(5),CONVERT(TIME,(SWITCHOFFSET ((CONVERT(VARCHAR(10),CONVERT(DATE, getdate())) +' '+ ais.engine_off), '-0'+dbo.return_summer_time(ais.fl_date)))))
ELSE ais.engine_off
END hora_final,
'' as horas_voadas,
'' as horas_glosadas,
'' as qtd_item_avulso,
'' as observacoes
FROM aims_flight_Schedule ais
JOIN aircrafts air ON (air.prefix = ais.reg)
JOIN contract_aircrafts ca ON (ca.aircraft_id = air.id)
JOIN contracts ct ON (ct.id = ca.contract_id AND @data BETWEEN ct.date_start AND ct.date_end)
WHERE ais.chr = 'foo'
AND ais.reg = @aeronave
AND SUBSTRING(ais.route,1,3) IN (SELECT name FROM aims_routes WHERE way_point_id = 4)
AND ais.fl_date = @data
AND (ais.disable_flight != 1 or ais.disable_flight is null)
ORDER BY item, hora_inicial
OPEN SAP
FETCH NEXT FROM SAP INTO @ITEM, @ROTA, @AIRCRAFT, @EQUIPAMENTO, @ATENDIMENTO, @ESCALA, @DESCRICAO_OPERACAO, @GRUPO_CODIGOS, @CODIGO_OPERACAO, @TRAP, @DATA_INICIAL, @HORA_INICIAL, @DATA_FINAL, @HORA_FINAL, @HORAS_VOADAS, @HORAS_GLOSADAS, @QTD_ITEM_AVULSO, @OBSERVACOES
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @ITEM
--PRINT @ROTA
--print @AIRCRAFT
--print @EQUIPAMENTO
--print @ATENDIMENTO
--print @ESCALA
--print @DESCRICAO_OPERACAO
--print @GRUPO_CODIGOS
--print @CODIGO_OPERACAO
--print @TRAP
--print @DATA_INICIAL
--print @HORA_INICIAL
--print @DATA_FINAL
--print @HORA_FINAL
--print @HORAS_VOADAS
--print @HORAS_GLOSADAS
--print @QTD_ITEM_AVULSO
--print @OBSERVACOES
--print '******************************************************************'
INSERT INTO @RETORNO VALUES(@ITEM, @ROTA, @AIRCRAFT, @EQUIPAMENTO, @ATENDIMENTO, @ESCALA, @DESCRICAO_OPERACAO, @GRUPO_CODIGOS, @CODIGO_OPERACAO, @TRAP, @DATA_INICIAL, @HORA_INICIAL, @DATA_FINAL, @HORA_FINAL, @HORAS_VOADAS, @HORAS_GLOSADAS, @QTD_ITEM_AVULSO, @OBSERVACOES);
FETCH NEXT FROM SAP INTO @ITEM, @ROTA, @AIRCRAFT, @EQUIPAMENTO, @ATENDIMENTO, @ESCALA, @DESCRICAO_OPERACAO, @GRUPO_CODIGOS, @CODIGO_OPERACAO, @TRAP, @DATA_INICIAL, @HORA_INICIAL, @DATA_FINAL, @HORA_FINAL, @HORAS_VOADAS, @HORAS_GLOSADAS, @QTD_ITEM_AVULSO, @OBSERVACOES
END
CLOSE SAP
DEALLOCATE SAP
FETCH NEXT FROM TAB INTO @ROUTE
END
CLOSE TAB
DEALLOCATE TAB
RETURN
END
Raw
necessario.sql
ALTER FUNCTION [dbo].[return_tabela_sap]
(
@data DATE,
@base BIGINT
)
RETURNS @returnTable TABLE(
equipamento VARCHAR(20),
atendimento VARCHAR(20),
escala VARCHAR(20),
descricao_da_operacao VARCHAR(50),
data_inicial DATE,
hora_inicial VARCHAR(50),
data_final DATE,
hora_final VARCHAR(50)
)
AS
BEGIN
INSERT INTO @returnTable
SELECT equipamento,
atendimento,
escala,
descricao_da_operacao,
data_inicial,
hora_inicial,
data_final,
hora_final
FROM (
SELECT c.client_code_aircraft equipamento,
afs.mission atendimento,
(SELECT top 1 wp.prefix FROM way_points wp WHERE wp.aims_route = afs.dep) as escala,
id_operacao,
descricao_da_operacao,
data_voo data_inicial,
hora_inicial,
data_voo data_final,
hora_final,
(SELECT top 1 COUNT(*) FROM aims_flight_Schedule ais WHERE ais.mission = afs.mission GROUP BY ais.mission) pernas,
dbo.return_perna(afs.flt) perna
FROM aims_flight_Schedule afs
JOIN aircrafts a ON a.prefix = afs.reg
JOIN contract_aircrafts ca ON ca.aircraft_id = a.id
JOIN contracts c ON (c.id = ca.contract_id AND @data BETWEEN c.date_start AND c.date_end)
JOIN counting_methods cm ON cm.id = c.counting_method_id
CROSS APPLY(VALUES
(1, 'OA30 - ACIONAMENTO', afs.fl_date, afs.atd, afs.toff),
(2, 'PA01 - DECOLAGEM', afs.fl_date, afs.toff, ''),
(3, dbo.decode_counting_methods(afs.t_type), afs.fl_date, CASE (dbo.return_perna(afs.flt)) WHEN 1 THEN afs.toff ELSE afs.atd END, afs.tdown),
(4, 'PA03 - POUSO', afs.fl_date, afs.tdown, ''),
(5, 'OA31 - CORTE', afs.fl_date, afs.tdown, afs.engine_off),
(6, 'IA09 - DIF POUSO/DECOLAGEM', afs.fl_date, '', '')
) ITEM (id_operacao, descricao_da_operacao, data_voo, hora_inicial, hora_final)
WHERE afs.chr = 'foo'
AND SUBSTRING(afs.route,1,3) IN (SELECT name FROM aims_routes WHERE way_point_id = @base)
AND afs.fl_date = @data
AND (afs.disable_flight != 1 or afs.disable_flight is null)
) tab
WHERE NOT (id_operacao IN(1, 2) AND perna > 1)
AND NOT (id_operacao IN(4, 5, 6) AND (perna < pernas))
RETURN;
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment