I have the below query:
我有以下查询：
select
t.location_id as Station_ID
,' ' as Trans_register
,timp.hose_num as Trans_Pump
,t.invoice as Trans_Receipt
,t.pos_date as Trans_Date_Time
,' ' as Trans_Batch
,' ' as Trans_Batch_Sequence
,CASE
WHEN im.org_id = 125 and l.chain_id = 10 THEN "EC"
WHEN im.org_id = 125 and l.chain_id <> 10 THEN "ER"
WHEN im.org_id = 165 and l.chain_id = 10 and t.stmt_id <> -4 THEN "DC"
WHEN im.org_id = 165 and l.chain_id = 10 and t.stmt_id = -4 THEN "IC"
WHEN im.org_id = 165 and l.chain_id <> 10 and t.stmt_id <> -4 THEN "DR"
WHEN im.org_id = 165 and l.chain_id <> 10 and t.stmt_id = -4 THEN "IR"
ELSE "EC" END as Trans_Origin
,t.trans_date as Trans_Process_Date_Time
,CASE
WHEN l.chain_Id <> 10 then t.pref_total
ELSE 0 END as Trans_Amount
,CASE
WHEN l.chain_id <> 10 THEN tltg.amount
ELSE 0 END as Trans_GST_Amount
,CASE
WHEN l.chain_id <> 10 THEN tltp.amount
ELSE 0 END as Trans_PST_Amount
,' ' as Loyalty_Program
,' ' as Loyalty_Account_Number
,' ' as Loyalty_Fee
,CASE
WHEN l.chain_Id <> 10 then t.pref_total
ELSE 0 END as Trans_Loyalty_Amount
,tl.line_id + 1 as Product_Sequence
,CASE
WHEN tl.cat in ("DEF","DEFD") then 4194304
--DEV need to add that if it's not DEF, DEFD and fuel type is 0 using the tl.cat but that wasn't working for me...need smarties to do that!
ELSE tl.fuel_type end as Product_Code
,tl.qty as Product_Qty --will always be Litres since Canada
,CASE
WHEN l.chain_id <> 10 then tl.amt else 0 end as Product_Amount
,' ' as Product_Loyalty_Amount
,tltf.amount as Product_FET_Amount
,tltf.tax_rate as Product_FET_Unit_Amount
,CASE
WHEN l.chain_id <> 10 THEN tltg.amount
ELSE 0 END as Product_GST_Amount
,CASE
WHEN l.chain_id <> 10 THEN tltg.tax_rate
ELSE 0 END as Product_GST_Unit_Amount
,tltpf.amount as Product_PFT_Amount
,tltpf.tax_rate as Product_PFT_Unit_Amount
,CASE
WHEN l.chain_id <> 10 THEN tltp.amount
ELSE 0 END as Product_PST_Amount
,CASE
WHEN l.chain_id <> 10 THEN tltp.tax_rate
ELSE 0 END as Product_PST_Unit_Amount
,tltv.amount as Product_MFT_Amount
,tltv.tax_rate as Product_MFT_Unit_Amount
,tl.line_id + 1 as Tender_Sequence
,t.Carrier_Id as Tender_Account
,' ' as Tender_Unit_Card_Number
,CASE
WHEN im.org_id = 165 then "19" ELSE "18" END as Tender_Type
,CASE
WHEN l.chain_Id <> 10 then t.pref_total
ELSE 0 END as Tedner_Amount
,' ' as Tender_Fee_Amt
,ca.expiredate as Tender_Exp_date
,t.auth_code as Authorization_Code
,CASE WHEN t.extra_3 in (3,7) then "N" else "Y" END as Authorization_Method
,' ' as Fi_Termid
,' ' as Fi_Sequence
,tiu.info as Unit_Number
,tio.info as Odometer_Number
,tid.info as Driver_Number
,tip.info as Trip_Number
,' ' as Reference_Number
,tit.info as Trailer_Number
,' ' as Trailer_Hub
,tihb.info as Tractor_Hub
,' ' as Check_Digit
,' ' as FI_Response_Code
,left(t.card_num,6) as FI_ISO_Code
from
transaction as t
left outer join
trans_imperial as timp
on t.trans_id = timp.trans_id
left outer join location as l
on t.location_id = l.location_id
left outer join contract as c
on t.contract_id = c.contract_id
left outer join issuer_misc as im
on c.issuer_id = im.issuer_id
left outer join trans_line_tax as tltg
on t.trans_id = tltg.trans_id
and tltg.tax_cd IN ("GST","HST","FNT") and tltg.gross_net_flag = "N" and tltg.exempt_flag = "N"
left outer join trans_line_tax as tltp
on t.trans_id = tltp.trans_id
and tltp.tax_cd IN ("PST","QST") and tltp.gross_net_flag = "N" and tltp.exempt_flag = "N"
left outer join trans_line as tl
on t.trans_id = tl.trans_id
left outer join trans_line_tax as tltf
on t.trans_id = tltf.trans_id
and tltf.tax_cd = "FET" and tltf.gross_net_flag = "N" and tltf.exempt_flag = "N"
left outer join trans_line_tax as tltpf
on t.trans_id = tltpf.trans_id
and tltpf.tax_cd = "PFT" and tltpf.gross_net_flag = "N" and tltpf.exempt_flag = "N"
left outer join trans_line_tax as tltv
on t.trans_id = tltv.trans_id
and tltv.tax_cd = "VTT" and tltv.gross_net_flag = "N" and tltv.exempt_flag = "N"
left outer join cards as ca
on t.card_num = ca.card_num
left outer join trans_info as tiu
on t.trans_id = tiu.trans_id
and tiu.type = "UNIT"
left outer join trans_info as tio
on t.trans_id = tio.trans_id
and tio.type = "ODRD"
left outer join trans_info as tid
on t.trans_id = tid.trans_id
and tid.type = "DRID"
left outer join trans_info as tip
on t.trans_id = tip.trans_id
and tip.type = "TRIP"
left outer join trans_info as tit
on t.trans_id = tit.trans_id
and tit.type = "TRLR"
left outer join trans_info as tihb
on t.trans_id = tihb.trans_id
and tihb.type = "HBRD"
where
t.extra_5 = 20161204
and l.src_country = "CAN"
select
t.l
