Morning People,
早上的人，
I'm having a little issue with COALESCE causing a stored procedure which is writing data to a table to run excessively. (without the COALESCE it takes 4 minutes and with it it takes 1 hour 30 minutes ish).
我对COALESCE有一点问题，导致存储过程将数据写入表中过度运行。 （没有COALESCE需要4分钟，需要1小时30分钟才能完成）。
I need this staging table's f_ columns to be clear of NULLs as these will be used for calculus later on in the warehouse which is then being displayed to Cognos reporting studio.
我需要这个临时表的f_列清除NULL，因为这些将在稍后的仓库中用于演算，然后显示给Cognos Reporting Studio。
There are 161 columns that require this and I have also tried to do a "CASE WHEN X IS NULL THEN 0 ELSE X" which apparently has a lower impact but still takes an Hour and a half.
有161列需要这个，我也试图做一个“情况，当X是空的，然后0 ELSE X”，这显然影响较小但仍然需要一个半小时。
Also have added "DEFAULT 0" to the tables build with no joys.
还为没有欢乐的表构建添加了“DEFAULT 0”。
Stored Procedure follows (Apologies in advance for the sheer size of it)
存储过程如下（事先告知它的大小）
TRUNCATE TABLE
ST_PIT_BOOKINGCOSTS
INSERT INTO
ST_PIT_BOOKINGCOSTS
SELECT DISTINCT
ST_PIT_BOOKINGHEADERS.i_SK_Bkg,
ST_PIT_BOOKINGHEADERS.i_SK_Version,
0 AS f_BkgTransportPax,
0 AS f_BkgTransportQuantity,
0 AS f_BkgTransportAdults,
0 AS f_BkgTransportChildren,
0 AS f_BkgTransportInfants,
0 AS f_BkgTransportRevenue,
0 AS f_BkgTransportCost,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Pax,0) AS f_BkgFltPax,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Quantity,0) AS f_BkgFltQuantity,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Adults,0) AS f_BkgFltAdults,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Children,0) AS f_BkgFltChildren,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Infants,0) AS f_BkgFltInfants,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Revenue,0) AS f_BkgFltRevenue,
COALESCE(ST_V_FC_FLIGHTCOSTS_AIR.f_Cost,0) AS f_BkgFltCost,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Pax,0) AS f_BkgFerryPax,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Quantity,0) AS f_BkgFerryQuantity,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Adults,0) AS f_BkgFerryAdults,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Children,0) AS f_BkgFerryChildren,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Infants,0) AS f_BkgFerryInfants,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Revenue,0) AS f_BkgFerryRevenue,
COALESCE(ST_V_FC_FLIGHTCOSTS_FER.f_Cost,0) AS f_BkgFerryCost,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Pax,0) AS f_BkgTrainPax,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Quantity,0) AS f_BkgTrainQuantity,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Adults,0) AS f_BkgTrainAdults,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Children,0) AS f_BkgTrainChildren,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Infants,0) AS f_BkgTrainInfants,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Revenue,0) AS f_BkgTrainRevenue,
COALESCE(ST_V_FC_FLIGHTCOSTS_TRN.f_Cost,0) AS f_BkgTrainCost,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Pax,0) AS f_BkgAccomPax,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Quantity,0) AS f_BkgAccomQuantity,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Adults,0) AS f_BkgAccomAdults,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Children,0) AS f_BkgAccomChildren,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Infants,0) AS f_BkgAccomInfants,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Revenue,0) AS f_BkgAccomRevenue,
COALESCE(ST_V_AC_ACCOMCOSTS.f_Cost,0) AS f_BkgAccomCost,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Pax,0) AS f_BkgExtraACTIPax,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Quantity,0) AS f_BkgExtraACTIQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Adults,0) AS f_BkgExtraACTIAdults,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Children,0) AS f_BkgExtraACTIChildren,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Infants,0) AS f_BkgExtraACTIInfants,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Revenue,0) AS f_BkgExtraACTIRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_ACTI.f_Cost,0) AS f_BkgExtraACTICost,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Pax,0) AS f_BkgExtraCARHPax,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Quantity,0) AS f_BkgExtraCARHQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Adults,0) AS f_BkgExtraCARHAdults,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Children,0) AS f_BkgExtraCARHChildren,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Infants,0) AS f_BkgExtraCARHInfants,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Revenue,0) AS f_BkgExtraCARHRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_CARH.f_Cost,0) AS f_BkgExtraCARHCost,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Pax,0) AS f_BkgExtraCARIPax,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Quantity,0) AS f_BkgExtraCARIQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Adults,0) AS f_BkgExtraCARIAdults,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Children,0) AS f_BkgExtraCARIChildren,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Infants,0) AS f_BkgExtraCARIInfants,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Revenue,0) AS f_BkgExtraCARIRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_CARI.f_Cost,0) AS f_BkgExtraCARICost,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Pax,0) AS f_BkgExtraCHLDPax,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Quantity,0) AS f_BkgExtraCHLDQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Adults,0) AS f_BkgExtraCHLDAdults,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Children,0) AS f_BkgExtraCHLDChildren,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Infants,0) AS f_BkgExtraCHLDInfants,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Revenue,0) AS f_BkgExtraCHLDRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_CHLD.f_Cost,0) AS f_BkgExtraCHLDCost,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Pax,0) AS f_BkgExtraCLUBPax,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Quantity,0) AS f_BkgExtraCLUBQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Adults,0) AS f_BkgExtraCLUBAdults,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Children,0) AS f_BkgExtraCLUBChildren,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Infants,0) AS f_BkgExtraCLUBInfants,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Revenue,0) AS f_BkgExtraCLUBRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_CLUB.f_Cost,0) AS f_BkgExtraCLUBCost,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Pax,0) AS f_BkgExtraCRECPax,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Quantity,0) AS f_BkgExtraCRECQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Adults,0) AS f_BkgExtraCRECAdults,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Children,0) AS f_BkgExtraCRECChildren,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Infants,0) AS f_BkgExtraCRECInfants,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Revenue,0) AS f_BkgExtraCRECRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_CREC.f_Cost,0) AS f_BkgExtraCRECCost,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Pax,0) AS f_BkgExtraEQUIPax,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Quantity,0) AS f_BkgExtraEQUIQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Adults,0) AS f_BkgExtraEQUIAdults,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Children,0) AS f_BkgExtraEQUIChildren,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Infants,0) AS f_BkgExtraEQUIInfants,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Revenue,0) AS f_BkgExtraEQUIRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_EQUI.f_Cost,0) AS f_BkgExtraEQUICost,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Pax,0) AS f_BkgExtraEXCUPax,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Quantity,0) AS f_BkgExtraEXCUQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Adults,0) AS f_BkgExtraEXCUAdults,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Children,0) AS f_BkgExtraEXCUChildren,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Infants,0) AS f_BkgExtraEXCUInfants,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Revenue,0) AS f_BkgExtraEXCURevenue,
COALESCE(ST_V_EC_EXTRACOSTS_EXCU.f_Cost,0) AS f_BkgExtraEXCUCost,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Pax,0) AS f_BkgExtraFLTRPax,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Quantity,0) AS f_BkgExtraFLTRQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Adults,0) AS f_BkgExtraFLTRAdults,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Children,0) AS f_BkgExtraFLTRChildren,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Infants,0) AS f_BkgExtraFLTRInfants,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Revenue,0) AS f_BkgExtraFLTRRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_FLTR.f_Cost,0) AS f_BkgExtraFLTRCost,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Pax,0) AS f_BkgExtraINSRPax,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Quantity,0) AS f_BkgExtraINSRQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Adults,0) AS f_BkgExtraINSRAdults,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Children,0) AS f_BkgExtraINSRChildren,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Infants,0) AS f_BkgExtraINSRInfants,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Revenue,0) AS f_BkgExtraINSRRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_INSR.f_Cost,0) AS f_BkgExtraINSRCost,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Pax,0) AS f_BkgExtraOTHRPax,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Quantity,0) AS f_BkgExtraOTHRQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Adults,0) AS f_BkgExtraOTHRAdults,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Children,0) AS f_BkgExtraOTHRChildren,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Infants,0) AS f_BkgExtraOTHRInfants,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Revenue,0) AS f_BkgExtraOTHRRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_OTHR.f_Cost,0) AS f_BkgExtraOTHRCost,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Pax,0) AS f_BkgExtraSKIPPax,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Quantity,0) AS f_BkgExtraSKIPQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Adults,0) AS f_BkgExtraSKIPAdults,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Children,0) AS f_BkgExtraSKIPChildren,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Infants,0) AS f_BkgExtraSKIPInfants,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Revenue,0) AS f_BkgExtraSKIPRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_SKIP.f_Cost,0) AS f_BkgExtraSKIPCost,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Pax,0) AS f_BkgExtraSKITPax,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Quantity,0) AS f_BkgExtraSKITQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Adults,0) AS f_BkgExtraSKITAdults,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Children,0) AS f_BkgExtraSKITChildren,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Infants,0) AS f_BkgExtraSKITInfants,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Revenue,0) AS f_BkgExtraSKITRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_SKIT.f_Cost,0) AS f_BkgExtraSKITCost,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Pax,0) AS f_BkgExtraSPIOPax,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Quantity,0) AS f_BkgExtraSPIOQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Adults,0) AS f_BkgExtraSPIOAdults,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Children,0) AS f_BkgExtraSPIOChildren,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Infants,0) AS f_BkgExtraSPIOInfants,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Revenue,0) AS f_BkgExtraSPIORevenue,
COALESCE(ST_V_EC_EXTRACOSTS_SPIO.f_Cost,0) AS f_BkgExtraSPIOCost,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Pax,0) AS f_BkgExtraSPORPax,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Quantity,0) AS f_BkgExtraSPORQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Adults,0) AS f_BkgExtraSPORAdults,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Children,0) AS f_BkgExtraSPORChildren,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Infants,0) AS f_BkgExtraSPORInfants,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Revenue,0) AS f_BkgExtraSPORRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_SPOR.f_Cost,0) AS f_BkgExtraSPORCost,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Pax,0) AS f_BkgExtraSTNDPax,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Quantity,0) AS f_BkgExtraSTNDQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Adults,0) AS f_BkgExtraSTNDAdults,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Children,0) AS f_BkgExtraSTNDChildren,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Infants,0) AS f_BkgExtraSTNDInfants,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Revenue,0) AS f_BkgExtraSTNDRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_STND.f_Cost,0) AS f_BkgExtraSTNDCost,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Pax,0) AS f_BkgExtraTRAFPax,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Quantity,0) AS f_BkgExtraTRAFQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Adults,0) AS f_BkgExtraTRAFAdults,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Children,0) AS f_BkgExtraTRAFChildren,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Infants,0) AS f_BkgExtraTRAFInfants,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Revenue,0) AS f_BkgExtraTRAFRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_TRAF.f_Cost,0) AS f_BkgExtraTRAFCost,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Pax,0) AS f_BkgExtraXXXXPax,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Quantity,0) AS f_BkgExtraXXXXQuantity,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Adults,0) AS f_BkgExtraXXXXAdults,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Children,0) AS f_BkgExtraXXXXChildren,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Infants,0) AS f_BkgExtraXXXXInfants,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Revenue,0) AS f_BkgExtraXXXXRevenue,
COALESCE(ST_V_EC_EXTRACOSTS_XXXX.f_Cost,0) AS f_BkgExtraXXXXCost,
0 AS f_DiffTransportPax,
0 AS f_DiffTransportQuantity,
0 AS f_DiffTransportAdults,
0 AS f_DiffTransportChildren,
0 AS f_DiffTransportInfants,
0 AS f_DiffTransportRevenue,
0 AS f_DiffTransportCost,
0 AS f_DiffFltPax,
0 AS f_DiffFltQuantity,
0 AS f_DiffFltAdults,
0 AS f_DiffFltChildren,
0 AS f_DiffFltInfants,
0 AS f_DiffFltRevenue,
0 AS f_DiffFltCost,
0 AS f_DiffFerryPax,
0 AS f_DiffFerryQuantity,
0 AS f_DiffFerryAdults,
0 AS f_DiffFerryChildren,
0 AS f_DiffFerryInfants,
0 AS f_DiffFerryRevenue,
0 AS f_DiffFerryCost,
0 AS f_DiffTrainPax,
0 AS f_DiffTrainQuantity,
0 AS f_DiffTrainAdults,
0 AS f_DiffTrainChildren,
0 AS f_DiffTrainInfants,
0 AS f_DiffTrainRevenue,
0 AS f_DiffTrainCost,
0 AS f_DiffAccomPax,
0 AS f_DiffAccomQuantity,
0 AS f_DiffAccomAdults,
0 AS f_DiffAccomChildren,
0 AS f_DiffAccomInfants,
0 AS f_DiffAccomRevenue,
0 AS f_DiffAccomCost,
0 AS f_DiffExtraACTIPax,
0 AS f_DiffExtraACTIQuantity,
0 AS f_DiffExtraACTIAdults,
0 AS f_DiffExtraACTIChildren,
0 AS f_DiffExtraACTIInfants,
0 AS f_DiffExtraACTIRevenue,
0 AS f_DiffExtraACTICost,
0 AS f_DiffExtraCARHPax,
0 AS f_DiffExtraCARHQuantity,
0 AS f_DiffExtraCARHAdults,
0 AS f_DiffExtraCARHChildren,
0 AS f_DiffExtraCARHInfants,
0 AS f_DiffExtraCARHRevenue,
0 AS f_DiffExtraCARHCost,
0 AS f_DiffExtraCARIPax,
0 AS f_DiffExtraCARIQuantity,
0 AS f_DiffExtraCARIAdults,
0 AS f_DiffExtraCARIChildren,
0 AS f_DiffExtraCARIInfants,
0 AS f_DiffExtraCARIRevenue,
0 AS f_DiffExtraCARICost,
0 AS f_DiffExtraCHLDPax,
0 AS f_DiffExtraCHLDQuantity,
0 AS f_DiffExtraCHLDAdults,
0 AS f_DiffExtraCHLDChildren,
0 AS f_DiffExtraCHLDInfants,
0 AS f_DiffExtraCHLDRevenue,
0 AS f_DiffExtraCHLDCost,
0 AS f_DiffExtraCLUBPax,
0 AS f_DiffExtraCLUBQuantity,
0 AS f_DiffExtraCLUBAdults,
0 AS f_DiffExtraCLUBChildren,
0 AS f_DiffExtraCLUBInfants,
0 AS f_DiffExtraCLUBRevenue,
0 AS f_DiffExtraCLUBCost,
0 AS f_DiffExtraCRECPax,
0 AS f_DiffExtraCRECQuantity,
0 AS f_DiffExtraCRECAdults,
0 AS f_DiffExtraCRECChildren,
0 AS f_DiffExtraCRECInfants,
0 AS f_DiffExtraCRECRevenue,
0 AS f_DiffExtraCRECCost,
0 AS f_DiffExtraEQUIPax,
0 AS f_DiffExtraEQUIQuantity,
0 AS f_DiffExtraEQUIAdults,
0 AS f_DiffExtraEQUIChildren,
0 AS f_DiffExtraEQUIInfants,
0 AS f_DiffExtraEQUIRevenue,
0 AS f_DiffExtraEQUICost,
0 AS f_DiffExtraEXCUPax,
0 AS f_DiffExtraEXCUQuantity,
0 AS f_DiffExtraEXCUAdults,
0 AS f_DiffExtraEXCUChildren,
0 AS f_DiffExtraEXCUInfants,
0 AS f_DiffExtraEXCURevenue,
0 AS f_DiffExtraEXCUCost,
0 AS f_DiffExtraFLTRPax,
0 AS f_DiffExtraFLTRQuantity,
0 AS f_DiffExtraFLTRAdults,
0 AS f_DiffExtraFLTRChildren,
0 AS f_DiffExtraFLTRInfants,
0 AS f_DiffExtraFLTRRevenue,
0 AS f_DiffExtraFLTRCost,
0 AS f_DiffExtraINSRPax,
0 AS f_DiffExtraINSRQuantity,
0 AS f_DiffExtraINSRAdults,
0 AS f_DiffExtraINSRChildren,
0 AS f_DiffExtraINSRInfants,
0 AS f_DiffExtraINSRRevenue,
0 AS f_DiffExtraINSRCost,
0 AS f_DiffExtraOTHRPax,
0 AS f_DiffExtraOTHRQuantity,
0 AS f_DiffExtraOTHRAdults,
0 AS f_DiffExtraOTHRChildren,
0 AS f_DiffExtraOTHRInfants,
0 AS f_DiffExtraOTHRRevenue,
0 AS f_DiffExtraOTHRCost,
0 AS f_DiffExtraSKIPPax,
0 AS f_DiffExtraSKIPQuantity,
0 AS f_DiffExtraSKIPAdults,
0 AS f_DiffExtraSKIPChildren,
0 AS f_DiffExtraSKIPInfants,
0 AS f_DiffExtraSKIPRevenue,
0 AS f_DiffExtraSKIPCost,
0 AS f_DiffExtraSKITPax,
0 AS f_DiffExtraSKITQuantity,
0 AS f_DiffExtraSKITAdults,
0 AS f_DiffExtraSKITChildren,
0 AS f_DiffExtraSKITInfants,
0 AS f_DiffExtraSKITRevenue,
0 AS f_DiffExtraSKITCost,
0 AS f_DiffExtraSPIOPax,
0 AS f_DiffExtraSPIOQuantity,
0 AS f_DiffExtraSPIOAdults,
0 AS f_DiffExtraSPIOChildren,
0 AS f_DiffExtraSPIOInfants,
0 AS f_DiffExtraSPIORevenue,
0 AS f_DiffExtraSPIOCost,
0 AS f_DiffExtraSPORPax,
0 AS f_DiffExtraSPORQuantity,
0 AS f_DiffExtraSPORAdults,
0 AS f_DiffExtraSPORChildren,
0 AS f_DiffExtraSPORInfants,
0 AS f_DiffExtraSPORRevenue,
0 AS f_DiffExtraSPORCost,
0 AS f_DiffExtraSTNDPax,
0 AS f_DiffExtraSTNDQuantity,
0 AS f_DiffExtraSTNDAdults,
0 AS f_DiffExtraSTNDChildren,
0 AS f_DiffExtraSTNDInfants,
0 AS f_DiffExtraSTNDRevenue,
0 AS f_DiffExtraSTNDCost,
0 AS f_DiffExtraTRAFPax,
0 AS f_DiffExtraTRAFQuantity,
0 AS f_DiffExtraTRAFAdults,
0 AS f_DiffExtraTRAFChildren,
0 AS f_DiffExtraTRAFInfants,
0 AS f_DiffExtraTRAFRevenue,
0 AS f_DiffExtraTRAFCost,
0 AS f_DiffExtraXXXXPax,
0 AS f_DiffExtraXXXXQuantity,
0 AS f_DiffExtraXXXXAdults,
0 AS f_DiffExtraXXXXChildren,
0 AS f_DiffExtraXXXXInfants,
0 AS f_DiffExtraXXXXRevenue,
0 AS f_DiffExtraXXXXCost
FROM
AMI_Stage.dbo.ST_PIT_BOOKINGHEADERS ST_PIT_BOOKINGHEADERS
LEFT JOIN
AMI_GALAXY.dbo.CONF_DT_BOOKING CONF_DT_BOOKING
ON
CONF_DT_BOOKING.i_SK_Bkg = ST_PIT_BOOKINGHEADERS.I_SK_BKG
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_XXXX
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_XXXX.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_XXXX.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_XXXX.i_BK_ExtraType = 'XXXX'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_TRAF
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_TRAF.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_TRAF.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_TRAF.i_BK_ExtraType = 'TRAF'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_STND
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_STND.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_STND.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_STND.i_BK_ExtraType = 'STND'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SPOR
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SPOR.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SPOR.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_SPOR.i_BK_ExtraType = 'SPOR'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SPIO
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SPIO.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SPIO.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_SPIO.i_BK_ExtraType = 'SPIO'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SKIT
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SKIT.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SKIT.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_SKIT.i_BK_ExtraType = 'SKIT'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_SKIP
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_SKIP.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_SKIP.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_SKIP.i_BK_ExtraType = 'SKIP'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_OTHR
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_OTHR.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_OTHR.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_OTHR.i_BK_ExtraType = 'OTHR'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_INSR
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_INSR.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_INSR.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_INSR.i_BK_ExtraType = 'INSR'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_FLTR
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_FLTR.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_FLTR.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_FLTR.i_BK_ExtraType = 'FLTR'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_EXCU
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_EXCU.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_EXCU.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_EXCU.i_BK_ExtraType = 'EXCU'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_EQUI
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_EQUI.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_EQUI.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_EQUI.i_BK_ExtraType = 'EQUI'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CREC
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CREC.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CREC.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_CREC.i_BK_ExtraType = 'CREC'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CLUB
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CLUB.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CLUB.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_CLUB.i_BK_ExtraType = 'CLUB'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CHLD
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CHLD.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CHLD.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_CHLD.i_BK_ExtraType = 'CHLD'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CARI
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CARI.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CARI.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_CARI.i_BK_ExtraType = 'CARI'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_CARH
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_CARH.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_CARH.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_CARH.i_BK_ExtraType = 'CARH'
LEFT JOIN
AMI_Stage.dbo.ST_V_EC_EXTRACOSTS ST_V_EC_EXTRACOSTS_ACTI
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_EC_EXTRACOSTS_ACTI.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_EC_EXTRACOSTS_ACTI.i_BK_Version
AND
ST_V_EC_EXTRACOSTS_ACTI.i_BK_ExtraType = 'ACTI'
LEFT JOIN
AMI_Stage.dbo.ST_V_AC_ACCOMCOSTS ST_V_AC_ACCOMCOSTS
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_AC_ACCOMCOSTS.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_AC_ACCOMCOSTS.i_BK_Version
LEFT JOIN
AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS.i_BK_Version
LEFT JOIN
AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS_AIR
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS_AIR.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS_AIR.i_BK_Version
AND
ST_V_FC_FLIGHTCOSTS_AIR.i_BK_TransportType = 'AIR'
LEFT JOIN
AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS_FER
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS_FER.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS_FER.i_BK_Version
AND
ST_V_FC_FLIGHTCOSTS_FER.i_BK_TransportType = 'FER'
LEFT JOIN
AMI_Stage.dbo.ST_V_FC_FLIGHTCOSTS ST_V_FC_FLIGHTCOSTS_TRN
ON
CONF_DT_BOOKING.i_BK_BkgConcat = ST_V_FC_FLIGHTCOSTS_TRN.i_BK_Bkg
AND
ST_PIT_BOOKINGHEADERS.I_SK_VERSION = ST_V_FC_FLIGHTCOSTS_TRN.i_BK_Version
AND
ST_V_FC_FLIGHTCOSTS_TRN.i_BK_TransportType = 'TRN'
1 个解决方案
#1
3
First of all, you can try ISNULL, which has a better performance (but not as much as you need), however it is quicker.
首先，您可以尝试ISNULL，它具有更好的性能（但不是您需要的更多），但它更快。
Here is a reference for an older StackOverlow thread: Which is quicker COALESCE OR ISNULL?
这是一个旧的StackOverlow线程的参考：哪个更快COALESCE或ISNULL？
UPDATED:
更新：
It meant a huge difference in performance:
这意味着性能的巨大差异：
"Marcus Culver: Across 730,000 rows 154 columns being ISNULL'd took 00:02:34 as opposed to coalesce at 01:32:43"
“马库斯·卡尔弗（Marcus Culver）：截止到730,000行154列是ISNULL的，我们采取的是00:02:34而不是在01:32:43合并
#1
3
First of all, you can try ISNULL, which has a better performance (but not as much as you need), however it is quicker.
首先，您可以尝试ISNULL，它具有更好的性能（但不是您需要的更多），但它更快。
Here is a reference for an older StackOverlow thread: Which is quicker COALESCE OR ISNULL?
这是一个旧的StackOverlow线程的参考：哪个更快COALESCE或ISNULL？
UPDATED:
更新：
It meant a huge difference in performance:
这意味着性能的巨大差异：
"Marcus Culver: Across 730,000 rows 154 columns being ISNULL'd took 00:02:34 as opposed to coalesce at 01:32:43"
“马库斯·卡尔弗（Marcus Culver）：截止到730,000行154列是ISNULL的，我们采取的是00:02:34而不是在01:32:43合并