SQLize | PHPize | SQLtest

WITH TMP_AIRCRAFT_MAP AS ( SELECT IFIMS_ST_AIRCRAFT_MAP.ACTYPE_KEY, IFIMS_ST_AIRCRAFT_MAP.SOURCE_SYSTEM, IFIMS_ST_AIRCRAFT_MAP.ACTYPE_CODE, TO_VARCHAR(IFIMS_ST_AIRCRAFT_MAP.VAFR, 'YYYYMMDD') AS VAFR, TO_VARCHAR(IFIMS_ST_AIRCRAFT_MAP.VATO, 'YYYYMMDD') AS VATO FROM AOT_EDW_DEV.IFIMS_ST_AIRCRAFT_MAP -- UNION ALL -- SELECT 55555 AS ACTYPE_KEY, -- 'FIMS' AS SOURCE_SYSTEM, -- 'A306_333' AS ACTYPE_CODE, -- '19000101' AS VAFR, -- '99991231' AS VATO ), TMP_INPUT_FIMS AS ( SELECT 'FIMS' AS SOURCE_SYSTEM, CASE WHEN ( LENGTH(INPUT_DATA.ACT5) = 0 AND ASCII(INPUT_DATA.ACT5) = 32 ) OR (LENGTH(INPUT_DATA.ACT5) = 0) THEN NULL ELSE TRIM(INPUT_DATA.ACT5) END AS ACT5, CASE WHEN ( LENGTH(INPUT_DATA.ACT3) = 0 AND ASCII(INPUT_DATA.ACT3) = 32 ) OR (LENGTH(INPUT_DATA.ACT3) = 0) THEN NULL ELSE TRIM(INPUT_DATA.ACT3) END AS ACT3, NULL AS FLAG, ACFN AS ACFN FROM ( SELECT 'FIMS' AS SOURCE_SYSTEM, COALESCE(ACT5, NULL) AS ACT5, COALESCE(ACT3, NULL) AS ACT3, CASE WHEN ( LENGTH(COALESCE(FIMS_ACTTAB_HIS.ACT5, '')) = 0 AND LENGTH(COALESCE(FIMS_ACTTAB_HIS.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(FIMS_ACTTAB_HIS.ACT3, '')) <> 0 THEN COALESCE(TRIM(FIMS_ACTTAB_HIS.ACT5) || '_', '') || COALESCE(TRIM(FIMS_ACTTAB_HIS.ACT3), '') ELSE TRIM(FIMS_ACTTAB_HIS.ACT5) || COALESCE( '_' || REPLACE(TRIM(FIMS_ACTTAB_HIS.ACT3), '', NULL), '' ) END AS ACTYPE_CODE FROM AOT_EDW_DEV.FIMS_ACTTAB_HIS LEFT JOIN TMP_AIRCRAFT_MAP ON CASE WHEN ( LENGTH(COALESCE(FIMS_ACTTAB_HIS.ACT5, '')) = 0 AND LENGTH(COALESCE(FIMS_ACTTAB_HIS.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(FIMS_ACTTAB_HIS.ACT3, '')) <> 0 THEN COALESCE(TRIM(FIMS_ACTTAB_HIS.ACT5) || '_', '') || COALESCE(TRIM(FIMS_ACTTAB_HIS.ACT3), '') ELSE TRIM(FIMS_ACTTAB_HIS.ACT5) || COALESCE( '_' || REPLACE(TRIM(FIMS_ACTTAB_HIS.ACT3), '', NULL), '' ) END = COALESCE(TMP_AIRCRAFT_MAP.ACTYPE_CODE, 'NA') AND TMP_AIRCRAFT_MAP.SOURCE_SYSTEM = 'FIMS' WHERE TMP_AIRCRAFT_MAP.ACTYPE_KEY IS NULL AND END_DATE = '9999-12-31' AND DELETED_FLAG = 'N' AND ( ACT5 IS NOT NULL OR ACT3 IS NOT NULL ) UNION ALL SELECT 'FIMS' AS SOURCE_SYSTEM, COALESCE(FIMS_AFTTAB.ACT5, NULL) AS ACT5, COALESCE(FIMS_AFTTAB.ACT3, NULL) AS ACT3, CASE WHEN ( LENGTH(COALESCE(FIMS_AFTTAB.ACT5, '')) = 0 AND LENGTH(COALESCE(FIMS_AFTTAB.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(FIMS_AFTTAB.ACT3, '')) <> 0 THEN CONCAT( CONCAT( TRIM( TRAILING '_' FROM TRIM(FIMS_AFTTAB.ACT5) ), '_' ), TRIM(FIMS_AFTTAB.ACT3) ) ELSE CONCAT( TRIM(FIMS_AFTTAB.ACT5), COALESCE( CONCAT('_', REPLACE(TRIM(FIMS_AFTTAB.ACT3), '', NULL)), '' ) ) END AS ACTYPE_CODE FROM AOT_EDW_DEV.FIMS_AFTTAB LEFT JOIN TMP_AIRCRAFT_MAP ON CASE WHEN ( LENGTH(COALESCE(FIMS_AFTTAB.ACT5, '')) = 0 AND LENGTH(COALESCE(FIMS_AFTTAB.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(FIMS_AFTTAB.ACT3, '')) <> 0 THEN CONCAT( CONCAT( TRIM( TRAILING '_' FROM TRIM(FIMS_AFTTAB.ACT5) ), '_' ), TRIM(FIMS_AFTTAB.ACT3) ) ELSE CONCAT( TRIM(FIMS_AFTTAB.ACT5), COALESCE( CONCAT('_', REPLACE(TRIM(FIMS_AFTTAB.ACT3), '', NULL)), '' ) ) END = COALESCE(TMP_AIRCRAFT_MAP.ACTYPE_CODE, 'NA') AND TMP_AIRCRAFT_MAP.SOURCE_SYSTEM = 'FIMS' WHERE TMP_AIRCRAFT_MAP.ACTYPE_KEY IS NULL AND ( FIMS_AFTTAB.ACT3 IS NOT NULL OR FIMS_AFTTAB.ACT5 IS NOT NULL ) AND CASE WHEN ADID = 'A' THEN LEFT(STOA, 8) ELSE LEFT(STOD, 8) END >= '20060928' GROUP BY COALESCE(FIMS_AFTTAB.ACT3, NULL), COALESCE(FIMS_AFTTAB.ACT5, NULL), CASE WHEN ( LENGTH(COALESCE(FIMS_AFTTAB.ACT5, '')) = 0 AND LENGTH(COALESCE(FIMS_AFTTAB.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(FIMS_AFTTAB.ACT3, '')) <> 0 THEN CONCAT( CONCAT( TRIM( TRAILING '_' FROM TRIM(FIMS_AFTTAB.ACT5) ), '_' ), TRIM(FIMS_AFTTAB.ACT3) ) ELSE CONCAT( TRIM(FIMS_AFTTAB.ACT5), COALESCE( CONCAT('_', REPLACE(TRIM(FIMS_AFTTAB.ACT3), '', NULL)), '' ) ) END ) INPUT_DATA LEFT JOIN AOT_EDW_DEV.FIMS_ACTTAB_HIS ON INPUT_DATA.ACTYPE_CODE = CASE WHEN ( LENGTH(IFNULL(FIMS_ACTTAB_HIS.ACT5, '')) = 0 AND LENGTH(IFNULL(FIMS_ACTTAB_HIS.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(IFNULL(FIMS_ACTTAB_HIS.ACT3, '')) <> 0 THEN IFNULL(TRIM(FIMS_ACTTAB_HIS.ACT5) || '_', '') || IFNULL(TRIM(FIMS_ACTTAB_HIS.ACT3), '') ELSE TRIM(FIMS_ACTTAB_HIS.ACT5) || IFNULL( '_' || REPLACE(TRIM(FIMS_ACTTAB_HIS.ACT3), '', NULL), '' ) END WHERE FIMS_ACTTAB_HIS.END_DATE = '9999-12-31' AND FIMS_ACTTAB_HIS.DELETED_FLAG = 'N' GROUP BY INPUT_DATA.SOURCE_SYSTEM, CASE WHEN ( LENGTH(INPUT_DATA.ACT5) = 0 AND ASCII(INPUT_DATA.ACT5) = 32 ) OR LENGTH(INPUT_DATA.ACT5) = 0 THEN NULL ELSE TRIM(INPUT_DATA.ACT5) END, NULL, ACFN ), TMP_INPUT_FICS AS ( SELECT SOURCE_SYSTEM, CASE WHEN ( LENGTH(INPUT_DATA.ACT5) = 0 AND ASCII(INPUT_DATA.ACT5) = 32 ) OR LENGTH(INPUT_DATA.ACT5) = 0 THEN NULL ELSE TRIM(INPUT_DATA.ACT5) END AS ACT5, INPUT_DATA.ACT3, NULL AS FLAG, ANAM AS ACFN FROM ( SELECT 'FICS' AS SOURCE_SYSTEM, AC10 AS ACT5, NULL AS ACT3, AC10 AS ACTYPE_CODE FROM AOT_EDW_DEV.FICS_QS_AIRCRAFT_HIS LEFT JOIN TMP_AIRCRAFT_MAP ON COALESCE(FICS_QS_AIRCRAFT_HIS.AC10, 'NA') = COALESCE(TMP_AIRCRAFT_MAP.ACTYPE_CODE, 'NA') AND TMP_AIRCRAFT_MAP.SOURCE_SYSTEM = 'FICS' WHERE TMP_AIRCRAFT_MAP.ACTYPE_KEY IS NULL AND END_DATE = '9999-12-31' AND DELETED_FLAG = 'N' AND AC10 IS NOT NULL GROUP BY AC10 UNION ALL SELECT 'FICS' AS SOURCE_SYSTEM, FICS_AFTBKK.AC10 AS ACT5, NULL AS ACT3, FICS_AFTBKK.AC10 AS ACTYPE_CODE FROM AOT_EDW_DEV.FICS_AFTBKK LEFT JOIN TMP_AIRCRAFT_MAP ON COALESCE(FICS_AFTBKK.AC10, 'NA') = COALESCE(TMP_AIRCRAFT_MAP.ACTYPE_CODE, 'NA') AND TMP_AIRCRAFT_MAP.SOURCE_SYSTEM = 'FICS' WHERE TMP_AIRCRAFT_MAP.ACTYPE_KEY IS NULL AND FICS_AFTBKK.AC10 IS NOT NULL AND CASE WHEN ADFL = 'A' THEN LEFT(STOA, 8) ELSE LEFT(STOD, 8) END GROUP BY FICS_AFTBKK.AC10 UNION ALL SELECT SYSTEM_CODE, AIRCRAFT_10_LETTER AS ACT5, NULL AS ACT3, AIRCRAFT_10_LETTER AS ACTYPE_CODE FROM AOT_EDW_DEV.TF_QSSDBBKKON_DAY LEFT JOIN TMP_AIRCRAFT_MAP ON COALESCE(TF_QSSDBBKKON_DAY.AIRCRAFT_10_LETTER, 'NA') = COALESCE(TMP_AIRCRAFT_MAP.ACTYPE_CODE, 'NA') AND TMP_AIRCRAFT_MAP.SOURCE_SYSTEM = 'FICS' WHERE TMP_AIRCRAFT_MAP.ACTYPE_KEY IS NULL AND AIRCRAFT_10_LETTER IS NOT NULL AND TF_QSSDBBKKON_DAY.SYSTEM_CODE = 'FICS' GROUP BY TF_QSSDBBKKON_DAY.SYSTEM_CODE, AIRCRAFT_10_LETTER ) INPUT_DATA LEFT JOIN AOT_EDW_DEV.FICS_QS_AIRCRAFT_HIS ON COALESCE(INPUT_DATA.ACTYPE_CODE, 'NA') = COALESCE(FICS_QS_AIRCRAFT_HIS.AC10, 'NA') AND INPUT_DATA.SOURCE_SYSTEM = 'FICS' AND END_DATE = '9999-12-31' AND DELETED_FLAG = 'N' WHERE 1 = 1 GROUP BY SOURCE_SYSTEM, CASE WHEN ( LENGTH(INPUT_DATA.ACT5) = 0 AND ASCII(INPUT_DATA.ACT5) = 32 ) OR LENGTH(INPUT_DATA.ACT5) = 0 THEN NULL ELSE TRIM(INPUT_DATA.ACT5) END, INPUT_DATA.ACT3, ANAM ), TMP_INPUT_IFIMS AS ( SELECT SOURCE_SYSTEM, CASE WHEN ( LENGTH(INPUT_DATA.ACT5) = 0 AND ASCII(INPUT_DATA.ACT5) = 32 ) OR LENGTH(INPUT_DATA.ACT5) = 0 THEN NULL ELSE LTRIM(RTRIM(INPUT_DATA.ACT5)) END AS ACT5, CASE WHEN ( LENGTH(INPUT_DATA.ACT3) = 0 AND ASCII(INPUT_DATA.ACT3) = 32 ) OR LENGTH(INPUT_DATA.ACT3) = 0 THEN NULL ELSE LTRIM(RTRIM(INPUT_DATA.ACT3)) END AS ACT3, NULL AS FLAG, ACFN AS ACFN FROM ( SELECT 'IFIMS' AS SOURCE_SYSTEM, COALESCE(ACT5, NULL) AS ACT5, COALESCE(ACT3, NULL) AS ACT3, CASE WHEN ( LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT5, '')) = 0 AND LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT3, '')) <> 0 THEN COALESCE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT5)) || '_', '') || COALESCE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT3)), '') ELSE LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT5)) || COALESCE( '_' || REPLACE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT3)), '', NULL), '' ) END AS ACTYPE_CODE FROM AOT_EDW_DEV.IFIMS_ACTTAB_HIS LEFT JOIN TMP_AIRCRAFT_MAP ON CASE WHEN ( LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT5, '')) = 0 AND LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT3, '')) <> 0 THEN COALESCE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT5)) || '_', '') || COALESCE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT3)), '') ELSE LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT5)) || COALESCE( '_' || REPLACE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT3)), '', NULL), '' ) END = COALESCE(TMP_AIRCRAFT_MAP.ACTYPE_CODE, 'NA') AND TMP_AIRCRAFT_MAP.SOURCE_SYSTEM = 'IFIMS' WHERE TMP_AIRCRAFT_MAP.ACTYPE_KEY IS NULL AND END_DATE = '9999-12-31' AND DELETED_FLAG = 'N' AND ( ACT5 IS NOT NULL OR ACT3 IS NOT NULL ) GROUP BY COALESCE(ACT5, NULL), COALESCE(ACT3, NULL), CASE WHEN ( LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT5, '')) = 0 AND LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT3, '')) <> 0 THEN COALESCE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT5)) || '_', '') || COALESCE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT3)), '') ELSE LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT5)) || COALESCE( '_' || REPLACE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT3)), '', NULL), '' ) END UNION ALL SELECT 'IFIMS' AS SOURCE_SYSTEM, COALESCE(IFIMS_AFTTAB.ACT5, NULL) AS ACT5, COALESCE(IFIMS_AFTTAB.ACT3, NULL) AS ACT3, CASE WHEN ( LENGTH(COALESCE(IFIMS_AFTTAB.ACT5, '')) = 0 AND LENGTH(COALESCE(IFIMS_AFTTAB.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(IFIMS_AFTTAB.ACT3, '')) <> 0 THEN COALESCE(LTRIM(RTRIM(IFIMS_AFTTAB.ACT5)) || '_', '') || COALESCE(LTRIM(RTRIM(IFIMS_AFTTAB.ACT3)), '') ELSE LTRIM(RTRIM(IFIMS_AFTTAB.ACT5)) || COALESCE( '_' || REPLACE(LTRIM(RTRIM(IFIMS_AFTTAB.ACT3)), '', NULL), '' ) END AS ACTYPE_CODE FROM AOT_EDW_DEV.IFIMS_T_AFTTAB LEFT JOIN TMP_AIRCRAFT_MAP ON CASE WHEN ( LENGTH(COALESCE(IFIMS_AFTTAB.ACT5, '')) = 0 AND LENGTH(COALESCE(IFIMS_AFTTAB.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(IFIMS_AFTTAB.ACT3, '')) <> 0 THEN COALESCE(LTRIM(RTRIM(IFIMS_AFTTAB.ACT5)) || '_', '') || COALESCE(LTRIM(RTRIM(IFIMS_AFTTAB.ACT3)), '') ELSE LTRIM(RTRIM(IFIMS_AFTTAB.ACT5)) || COALESCE( '_' || REPLACE(LTRIM(RTRIM(IFIMS_AFTTAB.ACT3)), '', NULL), '' ) END = COALESCE(TMP_AIRCRAFT_MAP.ACTYPE_CODE, 'NA') AND TMP_AIRCRAFT_MAP.SOURCE_SYSTEM = 'IFIMS' WHERE 1 = 1 AND TMP_AIRCRAFT_MAP.ACTYPE_KEY IS NULL AND ( IFIMS_AFTTAB.ACT3 IS NOT NULL OR IFIMS_AFTTAB.ACT5 IS NOT NULL ) AND CASE WHEN ADID = 'A' THEN LEFT(STOA, 8) ELSE LEFT(STOD, 8) END >= ( SELECT V_START_DATE FROM V_START_DATE ) GROUP BY COALESCE(IFIMS_AFTTAB.ACT3, NULL), COALESCE(IFIMS_AFTTAB.ACT5, NULL), CASE WHEN ( LENGTH(COALESCE(IFIMS_AFTTAB.ACT5, '')) = 0 AND LENGTH(COALESCE(IFIMS_AFTTAB.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(IFIMS_AFTTAB.ACT3, '')) <> 0 THEN COALESCE(LTRIM(RTRIM(IFIMS_AFTTAB.ACT5)) || '_', '') || COALESCE(LTRIM(RTRIM(IFIMS_AFTTAB.ACT3)), '') ELSE LTRIM(RTRIM(IFIMS_AFTTAB.ACT5)) || COALESCE( '_' || REPLACE(LTRIM(RTRIM(IFIMS_AFTTAB.ACT3)), '', NULL), '' ) END UNION -- #NAME? (This part was commented out in your original query, you may need to specify a valid query here.) SELECT 'IFIMS' AS SOURCE_SYSTEM, COALESCE(TF_SSITAB_DAY.AIRCRAFT_10_LETTER, NULL) AS ACT5, COALESCE(TF_SSITAB_DAY.AIRCRAFT_3_LETTER, NULL) AS ACT3, CASE WHEN ( LENGTH(COALESCE(TF_SSITAB_DAY.AIRCRAFT_10_LETTER, '')) = 0 AND LENGTH(COALESCE(TF_SSITAB_DAY.AIRCRAFT_3_LETTER, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(TF_SSITAB_DAY.AIRCRAFT_3_LETTER, '')) <> 0 THEN COALESCE( LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_10_LETTER)) || '_', '' ) || COALESCE( LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_3_LETTER)), '' ) ELSE LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_10_LETTER)) || COALESCE( '_' || REPLACE( LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_3_LETTER)), '', NULL ), '' ) END AS ACTYPE_CODE FROM AOT_EDW_DEV.TF_SSITAB_DAY INNER JOIN AOT_EDW_DEV.IFIMS_SSITAB_HIS ON TF_SSITAB_DAY.AOT_AIRPORT_CODE = IFIMS_SSITAB_HIS.AOT_AIRPORT_CODE AND TF_SSITAB_DAY.URNO_SCHED_FLIGHT = IFIMS_SSITAB_HIS.URNO AND TF_SSITAB_DAY.SYSTEM_CODE = IFIMS_SSITAB_HIS.SYSTEM_CODE AND IFIMS_SSITAB_HIS.START_DATE IN ( SELECT V_END_DATE FROM V_END_DATE ) LEFT JOIN TMP_AIRCRAFT_MAP ON CASE WHEN ( LENGTH(COALESCE(TF_SSITAB_DAY.AIRCRAFT_10_LETTER, '')) = 0 AND LENGTH(COALESCE(TF_SSITAB_DAY.AIRCRAFT_3_LETTER, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(TF_SSITAB_DAY.AIRCRAFT_3_LETTER, '')) <> 0 THEN COALESCE( LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_10_LETTER)) || '_', '' ) || COALESCE( LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_3_LETTER)), '' ) ELSE LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_10_LETTER)) || COALESCE( '_' || REPLACE( LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_3_LETTER)), '', NULL ), '' ) END = COALESCE(TMP_AIRCRAFT_MAP.ACTYPE_CODE, 'NA') AND TMP_AIRCRAFT_MAP.SOURCE_SYSTEM = 'IFIMS' WHERE TMP_AIRCRAFT_MAP.ACTYPE_KEY IS NULL AND RIGHT(TF_SSITAB_DAY.SEASON_CODE, 2) >= 19 AND ( TF_SSITAB_DAY.AIRCRAFT_10_LETTER IS NOT NULL OR TF_SSITAB_DAY.AIRCRAFT_3_LETTER IS NOT NULL ) GROUP BY COALESCE(TF_SSITAB_DAY.AIRCRAFT_10_LETTER, NULL), COALESCE(TF_SSITAB_DAY.AIRCRAFT_3_LETTER, NULL), CASE WHEN ( LENGTH(COALESCE(TF_SSITAB_DAY.AIRCRAFT_10_LETTER, '')) = 0 AND LENGTH(COALESCE(TF_SSITAB_DAY.AIRCRAFT_3_LETTER, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(TF_SSITAB_DAY.AIRCRAFT_3_LETTER, '')) <> 0 THEN COALESCE( LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_10_LETTER)) || '_', '' ) || COALESCE( LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_3_LETTER)), '' ) ELSE LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_10_LETTER)) || COALESCE( '_' || REPLACE( LTRIM(RTRIM(TF_SSITAB_DAY.AIRCRAFT_3_LETTER)), '', NULL ), '' ) END ) INPUT_DATA LEFT JOIN AOT_EDW_DEV.IFIMS_ACTTAB_HIS ON INPUT_DATA.ACTYPE_CODE = CASE WHEN ( LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT5, '')) = 0 AND LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(IFIMS_ACTTAB_HIS.ACT3, '')) <> 0 THEN COALESCE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT5)) || '_', '') || COALESCE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT3)), '') ELSE LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT5)) || COALESCE( '_' || REPLACE(LTRIM(RTRIM(IFIMS_ACTTAB_HIS.ACT3)), '', NULL), '' ) END AND END_DATE = '9999-12-31' AND DELETED_FLAG = 'N' GROUP BY SOURCE_SYSTEM, CASE WHEN ( LENGTH(INPUT_DATA.ACT5) = 0 AND ASCII(INPUT_DATA.ACT5) = 32 ) OR LENGTH(INPUT_DATA.ACT5) = 0 THEN NULL ELSE LTRIM(RTRIM(INPUT_DATA.ACT5)) END, CASE WHEN ( LENGTH(INPUT_DATA.ACT3) = 0 AND ASCII(INPUT_DATA.ACT3) = 32 ) OR LENGTH(INPUT_DATA.ACT3) = 0 THEN NULL ELSE LTRIM(RTRIM(INPUT_DATA.ACT3)) END, ACFN ), -- Define TMP_INPUT_AIRCRAFT TMP_INPUT_AIRCRAFT AS ( SELECT TMP_INPUT_FIMS.SOURCE_SYSTEM, CASE WHEN ( LENGTH(COALESCE(TMP_INPUT_FIMS.ACT5, '')) = 0 AND LENGTH(COALESCE(TMP_INPUT_FIMS.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(TMP_INPUT_FIMS.ACT3, '')) <> 0 THEN COALESCE(TMP_INPUT_FIMS.ACT5 || '_', '') || COALESCE(TMP_INPUT_FIMS.ACT3, '') ELSE TMP_INPUT_FIMS.ACT5 || COALESCE( '_' || REPLACE(TMP_INPUT_FIMS.ACT3, '', NULL), '' ) END AS ACTYPE_CODE, TMP_INPUT_FIMS.ACT5, TMP_INPUT_FIMS.ACT3, FLAG, ACFN FROM TMP_INPUT_FIMS WHERE 1 = 1 GROUP BY TMP_INPUT_FIMS.SOURCE_SYSTEM, CASE WHEN ( LENGTH(COALESCE(TMP_INPUT_FIMS.ACT5, '')) = 0 AND LENGTH(COALESCE(TMP_INPUT_FIMS.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(TMP_INPUT_FIMS.ACT3, '')) <> 0 THEN COALESCE(TMP_INPUT_FIMS.ACT5 || '_', '') || COALESCE(TMP_INPUT_FIMS.ACT3, '') ELSE TMP_INPUT_FIMS.ACT5 || COALESCE( '_' || REPLACE(TMP_INPUT_FIMS.ACT3, '', NULL), '' ) END, TMP_INPUT_FIMS.ACT5, TMP_INPUT_FIMS.ACT3, FLAG, ACFN UNION ALL SELECT TMP_INPUT_FICS.SOURCE_SYSTEM, COALESCE(TMP_INPUT_FICS.ACT5, 'NA') AS ACTYPE_CODE, TMP_INPUT_FICS.ACT5, TMP_INPUT_FICS.ACT3, FLAG, ACFN FROM TMP_INPUT_FICS WHERE 1 = 1 GROUP BY TMP_INPUT_FICS.SOURCE_SYSTEM, COALESCE(TMP_INPUT_FICS.ACT5, 'NA'), TMP_INPUT_FICS.ACT5, TMP_INPUT_FICS.ACT3, FLAG, ACFN UNION ALL SELECT TMP_INPUT_IFIMS.SOURCE_SYSTEM, CASE WHEN ( LENGTH(COALESCE(TMP_INPUT_IFIMS.ACT5, '')) = 0 AND LENGTH(COALESCE(TMP_INPUT_IFIMS.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(TMP_INPUT_IFIMS.ACT3, '')) <> 0 THEN COALESCE(TMP_INPUT_IFIMS.ACT5 || '_', '') || COALESCE(TMP_INPUT_IFIMS.ACT3, '') ELSE TMP_INPUT_IFIMS.ACT5 || COALESCE( '_' || REPLACE(TMP_INPUT_IFIMS.ACT3, '', NULL), '' ) END AS ACTYPE_CODE, TMP_INPUT_IFIMS.ACT5, TMP_INPUT_IFIMS.ACT3, FLAG, ACFN FROM TMP_INPUT_IFIMS WHERE 1 = 1 GROUP BY TMP_INPUT_IFIMS.SOURCE_SYSTEM, CASE WHEN ( LENGTH(COALESCE(TMP_INPUT_IFIMS.ACT5, '')) = 0 AND LENGTH(COALESCE(TMP_INPUT_IFIMS.ACT3, '')) = 0 ) THEN 'NA' WHEN LENGTH(COALESCE(TMP_INPUT_IFIMS.ACT3, '')) <> 0 THEN COALESCE(TMP_INPUT_IFIMS.ACT5 || '_', '') || COALESCE(TMP_INPUT_IFIMS.ACT3, '') ELSE TMP_INPUT_IFIMS.ACT5 || COALESCE( '_' || REPLACE(TMP_INPUT_IFIMS.ACT3, '', NULL), '' ) END, TMP_INPUT_IFIMS.ACT5, TMP_INPUT_IFIMS.ACT3, FLAG, ACFN ), -- Define TMP_GEN_KEY TMP_GEN_KEY AS ( SELECT MAX(ACTYPE_KEY) AS MAX_KEY, CASE WHEN MIN(TMP_ACTYPE_KEY) >= 1 OR MIN(TMP_ACTYPE_KEY) IS NULL THEN 0 ELSE MIN(TMP_ACTYPE_KEY) END AS MIN_KEY FROM AOT_EDW_DEV.CM_D_AIRCRAFT_NA ), -- Define TMP_NEW_ACTYPE_NEG TMP_NEW_ACTYPE_NEG AS ( SELECT CAST( ( SELECT MIN_KEY FROM TMP_GEN_KEY ) - ROW_NUMBER() OVER ( ORDER BY TMP_INPUT_AIRCRAFT.ACTYPE_CODE, TMP_INPUT_AIRCRAFT.SOURCE_SYSTEM ) AS INT ) AS ACTYPE_KEY, TMP_INPUT_AIRCRAFT.SOURCE_SYSTEM, TMP_INPUT_AIRCRAFT.ACTYPE_CODE FROM TMP_INPUT_AIRCRAFT LEFT JOIN AOT_DW.dbo.CM_D_AIRCRAFT_NA ON TMP_INPUT_AIRCRAFT.ACTYPE_CODE = CM_D_AIRCRAFT_NA.ACTYPE_CODE AND TMP_INPUT_AIRCRAFT.SOURCE_SYSTEM = CM_D_AIRCRAFT_NA.SOURCE_SYSTEM WHERE CM_D_AIRCRAFT_NA.ACTYPE_CODE IS NULL AND TMP_INPUT_AIRCRAFT.ACTYPE_CODE <> 'NA' GROUP BY TMP_INPUT_AIRCRAFT.SOURCE_SYSTEM, TMP_INPUT_AIRCRAFT.ACTYPE_CODE ), -- Define TMP_NEW_ACTYPE TMP_NEW_ACTYPE AS ( -- When Not Found ACT5, ACT3 on table CM_D_AIRCRAFT_NA (New AC Code) SELECT TMP_NEW_ACTYPE_NEG.ACTYPE_KEY AS ACTYPE_KEY, TMP_INPUT_AIRCRAFT.SOURCE_SYSTEM, TMP_INPUT_AIRCRAFT.ACTYPE_CODE, TMP_INPUT_AIRCRAFT.ACT5, TMP_INPUT_AIRCRAFT.ACT3, TMP_INPUT_AIRCRAFT.ACFN, ( SELECT V_END_DATE FROM V_END_DATE ) AS VAFR, '99991231' AS VATO, 'NEWNEG' AS NEG_FLAG FROM TMP_INPUT_AIRCRAFT INNER JOIN TMP_NEW_ACTYPE_NEG ON TMP_INPUT_AIRCRAFT.ACTYPE_CODE = TMP_NEW_ACTYPE_NEG.ACTYPE_CODE AND TMP_INPUT_AIRCRAFT.SOURCE_SYSTEM = TMP_NEW_ACTYPE_NEG.SOURCE_SYSTEM GROUP BY TMP_NEW_ACTYPE_NEG.ACTYPE_KEY, TMP_INPUT_AIRCRAFT.SOURCE_SYSTEM, TMP_INPUT_AIRCRAFT.ACTYPE_CODE, TMP_INPUT_AIRCRAFT.ACT5, TMP_INPUT_AIRCRAFT.ACT3, TMP_INPUT_AIRCRAFT.ACFN UNION ALL -- When Found ACT5, ACT3 on table CM_D_AIRCRAFT_NA SELECT CM_D_AIRCRAFT_NA.TMP_ACTYPE_KEY AS ACTYPE_KEY, CM_D_AIRCRAFT_NA.SOURCE_SYSTEM, CM_D_AIRCRAFT_NA.ACTYPE_CODE, NULL AS ACT5, NULL AS ACT3, NULL AS ACFN, TO_VARCHAR(CM_D_AIRCRAFT_NA.CREATE_DATE, 'YYYYMMDD') AS VAFR, '99991231' AS VATO, 'NA_NEG' AS NEG_FLAG FROM AOT_DW.dbo.CM_D_AIRCRAFT_NA LEFT JOIN TMP_AIRCRAFT_MAP ON CM_D_AIRCRAFT_NA.SOURCE_SYSTEM = TMP_AIRCRAFT_MAP.SOURCE_SYSTEM AND CM_D_AIRCRAFT_NA.ACTYPE_CODE = TMP_AIRCRAFT_MAP.ACTYPE_CODE WHERE FLAG IS NULL AND COALESCE( TMP_AIRCRAFT_MAP.ACTYPE_KEY, CM_D_AIRCRAFT_NA.TMP_ACTYPE_KEY ) < 0 GROUP BY CM_D_AIRCRAFT_NA.TMP_ACTYPE_KEY, CM_D_AIRCRAFT_NA.SOURCE_SYSTEM, CM_D_AIRCRAFT_NA.ACTYPE_CODE, TO_VARCHAR(CM_D_AIRCRAFT_NA.CREATE_DATE, 'YYYYMMDD') ) -- Final query SELECT ACTYPE_KEY, SOURCE_SYSTEM, ACTYPE_CODE, CASE WHEN ( ACT5 IS NULL OR LENGTH(ACT5) = 0 ) THEN 'N/A' ELSE ACT5 END AS ACT5, CASE WHEN ( ACT3 IS NULL OR LENGTH(ACT3) = 0 ) THEN 'N/A' ELSE ACT3 END AS ACT3, ACFN AS ANAM, VAFR, VATO, TO_DATE(VAFR, 'YYYYMMDD') AS VAFR_DATE, TO_DATE(VATO, 'YYYYMMDD') AS VATO_DATE, NEG_FLAG FROM TMP_NEW_ACTYPE ORDER BY ACTYPE_KEY DESC;Could not execute 'WITH TMP_AIRCRAFT_MAP AS ( SELECT IFIMS_ST_AIRCRAFT_MAP.ACTYPE_KEY, ...' SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "GROUP": line 147 col 1 (at pos 6447) Could not execute ') INPUT_DATA LEFT JOIN AOT_EDW_DEV.FICS_QS_AIRCRAFT_HIS ON COALESCE(INPUT_DATA.ACTYPE_CODE, ' NA ') = ...' in 2 ms 966 µ s.SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near ")": line 1 col 1 (at pos 1)