3706 error involving CASE WHEN and LEFT?

Database

3706 error involving CASE WHEN and LEFT?

The query below is returning a 3706 error: "expected something between the 'WHEN' keyword and the 'LEFT' keyword".

The only place those terms occur together is in the "UNITS" part of the query. If I take the SQL inside the parenthesis in front of UNITS, it runs. If I put it in the parenthesis, it returns the syntax error. Can someone spot what I'm doing wrong? I'm using Teradata 12.0.0.0 ODBC drivers.

SELECT YRMO, SYS_HOURS, SYS_HOURS_AMT, SYS_MILES, SYS_MILES_AMT, SYS_EXPENSE, FGN_HOURS, FGN_HOURS_AMT, FGN_MILES, FGN_MILES_AMT, FGN_EXPENSE, 
TTX_HOURS, TTX_HOURS_AMT, TTX_MILES, TTX_MILES_AMT, TTX_EXPENSE, PRV_MILES, PRV_MILES_AMT, PRV_EXPENSE, SYS_UNITS, FGN_UNITS, TTX_UNITS, PRV_UNITS, LSE_UNITS
FROM
(SELECT
ATTRS.CURR_ACTV_EVT_YRMO AS YRMO
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='S' THEN AMT.PIED_ACUM_HRS ELSE 0 END) AS SYS_HOURS
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='S' THEN AMT.PER_DIEM_AMT ELSE 0.00 END) AS SYS_HOURS_AMT
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='S' THEN AMT.PIED_ACUM_MLG ELSE 0 END) AS SYS_MILES
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='S' THEN AMT.MILEAGE_AMT ELSE 0.00 END) AS SYS_MILES_AMT
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='S' THEN AMT.PER_DIEM_AMT+AMT.MILEAGE_AMT+AMT.RACK_AMT ELSE 0.00 END) AS SYS_EXPENSE
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='F' THEN AMT.PIED_ACUM_HRS ELSE 0 END) AS FGN_HOURS
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='F' THEN AMT.PER_DIEM_AMT ELSE 0.00 END) AS FGN_HOURS_AMT
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='F' THEN AMT.PIED_ACUM_MLG ELSE 0 END) AS FGN_MILES
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='F' THEN AMT.MILEAGE_AMT ELSE 0.00 END) AS FGN_MILES_AMT
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='F' THEN AMT.PER_DIEM_AMT+AMT.MILEAGE_AMT+AMT.RACK_AMT ELSE 0.00 END) AS FGN_EXPENSE
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='T' THEN AMT.PIED_ACUM_HRS ELSE 0 END) AS TTX_HOURS
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='T' THEN AMT.PER_DIEM_AMT ELSE 0.00 END) AS TTX_HOURS_AMT
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='T' THEN AMT.PIED_ACUM_MLG ELSE 0 END) AS TTX_MILES
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='T' THEN AMT.MILEAGE_AMT ELSE 0.00 END) AS TTX_MILES_AMT
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='T' THEN AMT.PER_DIEM_AMT+AMT.MILEAGE_AMT+AMT.RACK_AMT ELSE 0.00 END) AS TTX_EXPENSE
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='P' THEN AMT.PIED_ACUM_MLG ELSE 0 END) AS PRV_MILES
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='P' THEN AMT.MILEAGE_AMT ELSE 0.00 END) AS PRV_MILES_AMT
,SUM(CASE WHEN ATTRS.CURR_FGN_SYS_CD='P' THEN AMT.MILEAGE_AMT+AMT.RACK_AMT ELSE 0.00 END) AS PRV_EXPENSE
FROM
CWA.VCAR_HIRE_PIED_CALC_AMT AMT
LEFT JOIN CWA.VCAR_HIRE_PIED_CALC_ATTRS ATTRS
ON AMT.ACTV_EVT_DT=ATTRS.ACTV_EVT_DT
AND AMT.RPTG_CYCL_ID=ATTRS.RPTG_CYCL_ID
WHERE
AMT.ACTV_EVT_DT>='2012-03-01'
GROUP BY YRMO
UNION ALL
SELECT YRMO, SYS_HOURS, SYS_HOURS_AMT, SYS_MILES, SYS_MILES_AMT, SYS_EXPENSE, FGN_HOURS, FGN_HOURS_AMT, FGN_MILES, FGN_MILES_AMT, FGN_EXPENSE,
TTX_HOURS, TTX_HOURS_AMT, TTX_MILES, TTX_MILES_AMT, TTX_EXPENSE, PRV_MILES, PRV_MILES_AMT, PRV_EXPENSE
FROM
(SELECT
CH_SETL_YRMO AS YRMO
,SUM(PER_DIEM_UOT) AS SYS_HOURS
,SUM(PER_DIEM_AMT) AS SYS_HOURS_AMT
,SUM(CH_MILES) AS SYS_MILES
,SUM(MILG_AMT) AS SYS_MILES_AMT
,SUM(PER_DIEM_AMT + MILG_AMT + RACK_AMT) AS SYS_EXPENSE
FROM
CWA.VCAR_HIRE_RCPT_DTL
WHERE
CH_SETL_YRMO BETWEEN '201112' AND '201202'
AND AAR_ACCT_CD='010'
AND CAS_OWNER='BNSF'
GROUP BY YRMO) SETTLED_RECEIVABLE,
(SELECT
CH_SETL_YRMO AS YRMO2
,SUM(CASE WHEN FGN_SYS_CD=' ' AND EQP_CNTL_ABBR < >'TTX' THEN PER_DIEM_UOT ELSE 0 END) AS FGN_HOURS
,SUM(CASE WHEN FGN_SYS_CD=' ' AND EQP_CNTL_ABBR < >'TTX' THEN PER_DIEM_AMT ELSE 0.00 END) AS FGN_HOURS_AMT
,SUM(CASE WHEN FGN_SYS_CD=' ' AND EQP_CNTL_ABBR < >'TTX' THEN CH_MILES ELSE 0 END) AS FGN_MILES
,SUM(CASE WHEN FGN_SYS_CD=' ' AND EQP_CNTL_ABBR < >'TTX' THEN MILG_AMT ELSE 0.00 END) AS FGN_MILES_AMT
,SUM(CASE WHEN FGN_SYS_CD=' ' AND EQP_CNTL_ABBR < >'TTX' THEN CH_TOT_AMT ELSE 0.00 END) AS FGN_EXPENSE
,SUM(CASE WHEN EQP_CNTL_ABBR ='TTX' THEN PER_DIEM_UOT ELSE 0 END) AS TTX_HOURS
,SUM(CASE WHEN EQP_CNTL_ABBR ='TTX' THEN PER_DIEM_AMT ELSE 0.00 END) AS TTX_HOURS_AMT
,SUM(CASE WHEN EQP_CNTL_ABBR ='TTX' THEN CH_MILES ELSE 0 END) AS TTX_MILES
,SUM(CASE WHEN EQP_CNTL_ABBR ='TTX' THEN MILG_AMT ELSE 0.00 END) AS TTX_MILES_AMT
,SUM(CASE WHEN EQP_CNTL_ABBR ='TTX' THEN CH_TOT_AMT ELSE 0.00 END) AS TTX_EXPENSE
,SUM(CASE WHEN FGN_SYS_CD='P' THEN CH_MILES ELSE 0 END) AS PRV_MILES
,SUM(CASE WHEN FGN_SYS_CD='P' THEN MILG_AMT ELSE 0.00 END) AS PRV_MILES_AMT
,SUM(CASE WHEN FGN_SYS_CD='P' THEN CH_TOT_AMT ELSE 0.00 END) AS PRV_EXPENSE
FROM
CWA.VCAR_HIRE_SETTLED
WHERE
CH_SETL_YRMO BETWEEN '201112' AND '201202'
AND AAR_ACCT_CD='010'
AND CAS_OWNER='BNSF'
GROUP BY YRMO2) SETTLED_PAYABLE
WHERE SETTLED_RECEIVABLE.YRMO=SETTLED_PAYABLE.YRMO2
) EXPENSE,
(SELECT YRMO2, SUM(SYS_UNITS) AS SYS_UNITS, SUM(FGN_UNITS) AS FGN_UNITS, SUM(TTX_UNITS) AS TTX_UNITS, SUM(PRV_UNITS) AS PRV_UNITS, SUM(LSE_UNITS) AS LSE_UNITS
FROM (SELECT
UNIT.IMS_ACTV_DATE(FORMAT 'YYYYMM')(CHAR (6)) AS YRMO2
,CASE
WHEN L.CONTR_NBR IS NOT NULL THEN 'L'
WHEN LEFT(UNIT.PLUS_CAR_KIND,1) IN ('K','V') THEN
CASE
WHEN CASE WHEN E.REC_LSE_SCAC=' ' THEN E.REC_OWN_SCAC ELSE E.REC_LSE_SCAC END='BNSF' THEN 'S'
WHEN CASE WHEN E.REC_LSE_SCAC=' ' THEN E.REC_OWN_SCAC ELSE E.REC_LSE_SCAC END='TTX' THEN 'T'
WHEN TRIM(E.EQP_INIT1) IN ('BN', 'BNSF', 'ATSF') THEN 'S'
ELSE 'F'
END
WHEN WB.EQP_OWN_ABBR = 'TTX' THEN 'T'
WHEN UNIT.FGN_SYS_CD <> ' ' THEN UNIT.FGN_SYS_CD
WHEN UNIT.EQP_INIT IN ('BN','BNSF','ATSF') THEN 'S'
ELSE 'F'
END AS OWNER_CD
,SUM(CASE WHEN OWNER_CD = 'S' THEN UNIT.REV_CAR_CNT ELSE 0 END) AS SYS_UNITS
,SUM(CASE WHEN OWNER_CD = 'F' THEN UNIT.REV_CAR_CNT ELSE 0 END) AS FGN_UNITS
,SUM(CASE WHEN OWNER_CD = 'T' THEN UNIT.REV_CAR_CNT ELSE 0 END) AS TTX_UNITS
,SUM(CASE WHEN OWNER_CD = 'P' THEN UNIT.REV_CAR_CNT ELSE 0 END) AS PRV_UNITS
,SUM(CASE WHEN OWNER_CD = 'L' THEN UNIT.REV_CAR_CNT ELSE 0 END) AS LSE_UNITS
FROM
(SELECT IMS_ACTV_DATE, FGN_SYS_CD, REV_CAR_CNT, WB_ID,BUS_GRP_CD,PLUS_CAR_KIND,EQP_INIT,EQP_NUMB FROM CWVIEWS.VREV_DRR_SEG UNIT WHERE UNIT.IMS_ACTV_DATE > '2011-12-01' ) UNIT
LEFT JOIN CWX_RSS0.OW_VWBM WB ON WB.WB_ID = UNIT.WB_ID
LEFT JOIN CWVIEWS.VEQ_TE_TRNSP_EQPACTV_INDEX E ON E.TRNSP_EQP_ID=CAST(CASE WHEN LEFT(UNIT.PLUS_CAR_KIND,1) IN ('K','V') THEN WB.CAR_INIT ELSE UNIT.EQP_INIT END AS CHAR(4)) || SUBSTR('0000000000',1,10-CHARS(TRIM(CASE WHEN LEFT(UNIT.PLUS_CAR_KIND,1) IN ('K','V') THEN WB.CAR_NUMB ELSE UNIT.EQP_NUMB END))) || TRIM(CASE WHEN LEFT(UNIT.PLUS_CAR_KIND,1) IN ('K','V') THEN WB.CAR_NUMB ELSE UNIT.EQP_NUMB END) AND (LEFT(E.EDO_FLEET_CD,1) IN ('I') OR E.EQP_GRP = 'IFLT')
LEFT JOIN CWX_LC0.VLC_RIDER_EQP L ON L.EQP_INIT=CASE WHEN LEFT(UNIT.PLUS_CAR_KIND,1) IN ('K','V') THEN WB.CAR_INIT ELSE UNIT.EQP_INIT END AND L.EQP_NUMB=CASE WHEN LEFT(UNIT.PLUS_CAR_KIND,1) IN ('K','V') THEN WB.CAR_NUMB ELSE UNIT.EQP_NUMB END AND UNIT.IMS_ACTV_DATE >= L.ACT_DT AND (UNIT.IMS_ACTV_DATE <= L.INACT_DT OR L.INACT_DT IS NULL)
GROUP BY YRMO2, OWNER_CD) AS UNITSUM
GROUP BY YRMO2) UNITS
WHERE EXPENSE.YRMO=UNITS.YRMO2

1 REPLY

Re: 3706 error involving CASE WHEN and LEFT?

I found my mistake. There is no LEFT string manipulation function, I needed to use SUBSTR.