Easy SQL Select Case When 语句抛出不正确的语法错误，但我找不到它！

Question

感谢所有试图提供帮助的人！

我有 2 个 case select 语句，并且在该语句上收到错误：

Center,
Case Center
When 'Concord Call Center' Then 'CCC'
When 'Livermore Call Center' Then 'LCC'
When 'Morgan Hill Call Center' Then 'MHCC'
When 'Natomas Call Center' Then 'NCC'
When 'Virtual Call Center' Then 'VCC'
Else Center
End,

我收到的错误是： 没有为“ANDREWSTABLE”的第 14 列指定任何列。

请参阅下面完整声明中的位置：

Select segstart
    ,Interval
    ,dialed_num
    ,FiscalMonthYear
    ,SegStart_Date
    ,row_date
    ,Name
    ,Xferto
    ,TransferType
    ,Agent
    ,Sup
    ,Manager
    ,Center
    ,Xferfrom
    ,talktime
    ,ANDREWSTABLE.transferred
    ,ANDREWSTABLE.disposition
    ,split1
    ,hsplit.starttime
    ,CASE
    WHEN hsplit.callsoffered > 0 
    THEN (CAST(hsplit.acceptable as DECIMAL)/hsplit.callsoffered)*100
    ELSE '0'
    END AS 'Service Level'
    ,hsplit.callsoffered
    ,hsplit.acceptable
FROM
(
Select segstart,
CASE
--when hour is = 0 and min < 30 then 0
WHEN DATEPART(HOUR, segstart) = 0 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('0' as int)
--when hour is = 0 and min > 30 then 30
WHEN DATEPART(HOUR, segstart) = 0 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('30' as int)
--when hour is 1 and min < 30 then 100
WHEN DATEPART(HOUR, segstart) = 1 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('100' as int)
--when hour is 1 and min > 30 then 130
WHEN DATEPART(HOUR, segstart) = 1 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('130' as int)
--when hour is 2 and min < 30 then 200
WHEN DATEPART(HOUR, segstart) = 2 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('200' as int)
--when hour is 2 and min > 30 then 230
WHEN DATEPART(HOUR, segstart) = 2 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('230' as int)
--when hour is 3 and min < 30 then 300
WHEN DATEPART(HOUR, segstart) = 3 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('300' as int)
--when hour is 3 and min > 30 then 330
WHEN DATEPART(HOUR, segstart) = 3 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('330' as int)
--when hour is 4 and min < 30 then 400
WHEN DATEPART(HOUR, segstart) = 4 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('400' as int)
--when hour is 4 and min > 30 then 430
WHEN DATEPART(HOUR, segstart) = 4 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('430' as int)
--when hour is 5 and min < 30 then 500
WHEN DATEPART(HOUR, segstart) = 5 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('500' as int)
--when hour is 5 and min > 30 then 530
WHEN DATEPART(HOUR, segstart) = 5 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('530' as int)
--when hour is 6 and min < 30 then 600
WHEN DATEPART(HOUR, segstart) = 6 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('600' as int)
--when hour is 6 and min > 30 then 630
WHEN DATEPART(HOUR, segstart) = 6 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('630' as int)
--when hour is 7 and min < 30 then 700
WHEN DATEPART(HOUR, segstart) = 7 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('700' as int)
--when hour is 7 and min > 30 then 730
WHEN DATEPART(HOUR, segstart) = 7 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('730' as int)
--when hour is 8 and min < 30 then 800
WHEN DATEPART(HOUR, segstart) = 8 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('800' as int)
--when hour is 8 and min > 30 then 830
WHEN DATEPART(HOUR, segstart) = 8 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('830' as int)
--when hour is 9 and min < 30 then 900
WHEN DATEPART(HOUR, segstart) = 9 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('900' as int)
--when hour is 9 and min > 30 then 930
WHEN DATEPART(HOUR, segstart) = 9 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('930' as int)
--when hour is 10 and min < 30 then 1000
WHEN DATEPART(HOUR, segstart) = 10 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1000' as int)
--when hour is 10 and min > 30 then 1030
WHEN DATEPART(HOUR, segstart) = 10 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1030' as int)
--when hour is 11 and min < 30 then 1100
WHEN DATEPART(HOUR, segstart) = 11 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1100' as int)
--when hour is 11 and min > 30 then 1130
WHEN DATEPART(HOUR, segstart) = 11 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1130' as int)
--when hour is 12 and min < 30 then 1200
WHEN DATEPART(HOUR, segstart) = 12 AND DATEPART(MINUTE, segstart) < 30  THEN CAST('1200' as int)
--when hour is 12 and min < 30 then 1230
WHEN DATEPART(HOUR, segstart) = 12 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1230' as int)
--when hour is 13 and min < 30 then 1300
WHEN DATEPART(HOUR, segstart) = 13 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1300' as int)
--when hour is 13 and min > 30 then 1330
WHEN DATEPART(HOUR, segstart) = 13 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1330' as int)
--when hour is 14 and min < 30 then 1400
WHEN DATEPART(HOUR, segstart) = 14 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1400' as int)
--when hour is 14 and min > 30 then 1430
WHEN DATEPART(HOUR, segstart) = 14 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1430' as int)
--when hour is 15 and min < 30 then 1500
WHEN DATEPART(HOUR, segstart) = 15 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1500' as int)
--when hour is 15 and min > 30 then 1530
WHEN DATEPART(HOUR, segstart) = 15 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1530' as int)
--when hour is 16 and min < 30 then 1600
WHEN DATEPART(HOUR, segstart) = 16 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1600' as int)
--when hour is 16 and min > 30 then 1630
WHEN DATEPART(HOUR, segstart) = 16 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1630' as int)
--when hour is 17 and min < 30 then 1700
WHEN DATEPART(HOUR, segstart) = 17 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1700' as int)
--when hour is 17 and min > 30 then 1730
WHEN DATEPART(HOUR, segstart) = 17 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1730' as int)
--when hour is 18 and min < 30 then 1800
WHEN DATEPART(HOUR, segstart) = 18 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1800' as int)
--when hour is 18 and min > 30 then 1830
WHEN DATEPART(HOUR, segstart) = 18 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1830' as int)
--when hour is 19 and min < 30 then 1900
WHEN DATEPART(HOUR, segstart) = 19 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('1900' as int)
--when hour is 19 and min > 30 then 1930
WHEN DATEPART(HOUR, segstart) = 19 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('1930' as int)
--when hour is 20 and min < 30 then 2000
WHEN DATEPART(HOUR, segstart) = 20 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2000' as int)
--when hour is 20 and min > 30 then 2030
WHEN DATEPART(HOUR, segstart) = 20 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('2030' as int)
--when hour is 21 and min < 30 then 2100
WHEN DATEPART(HOUR, segstart) = 21 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2100' as int)
--when hour is 21 and min > 30 then 2130
WHEN DATEPART(HOUR, segstart) = 21 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('2130' as int)
--when hour is 22 and min < 30 then 2200
WHEN DATEPART(HOUR, segstart) = 22 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2200' as int)
--when hour is 22 and min > 30 then 2230
WHEN DATEPART(HOUR, segstart) = 22 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('2230' as int)
--when hour is 23 and min < 30 then 2300
WHEN DATEPART(HOUR, segstart) = 23 AND DATEPART(MINUTE, segstart) < 30 THEN CAST('2300' as int)
--when hour is 23 and min > 30 then 2330
WHEN DATEPART(HOUR, segstart) = 23 AND DATEPART(MINUTE, segstart) > 30 THEN CAST('2330' as int)
ELSE Null End as Interval,
    FiscalMonthYear,
    SegStart_Date,
    dialed_num,
    callid,
    Name,
    t.Queue AS 'Xferto',
    TransferType,
    RepLName+', '+RepFName AS Agent,
    SupLName+', '+SupFName AS Sup,
    MgrLName+', '+MgrFName AS Manager,
    Center
Case Center
When 'Concord Call Center' Then 'CCC'
When 'Livermore Call Center' Then 'LCC'
When 'Morgan Hill Call Center' Then 'MHCC'
When 'Natomas Call Center' Then 'NCC'
When 'Virtual Call Center' Then 'VCC'
Else Center
End,
    q.Queue AS 'Xferfrom',
    e.anslogin,
    e.origlogin,
    t.Extension,
    transferred,
    disposition,
    talktime,
    split1
From CMS_ECH.dbo.CaliforniaECH e

INNER JOIN Cal_RemReporting.dbo.TransferVDNs t on e.dialed_num = t.Extension
INNER JOIN InfoQuest.dbo.IQ_Employee_Profiles_v3_AvayaId q on e.origlogin = q.AvayaID
INNER JOIN Cal_RemReporting.dbo.udFiscalMonthTable f on e.SegStart_Date = f.Tdate

Where SegStart_Date between getdate()-90 and getdate()-1
    And SegStart_Date between RepToSup_StartDate and RepToSup_EndDate
    And SegStart_Date between SupToMgr_StartDate and SupToMgr_EndDate
    And SegStart_Date between Avaya_StartDate and Avaya_EndDate
    And SegStart_Date between RepQueue_StartDate and RepQueue_EndDate
    AND e.disposition like '4' 
    OR e.transferred like '1'
) AS ANDREWSTABLE

Inner Join CMS_ECH.dbo.hsplit hsplit on hsplit.starttime = ANDREWSTABLE.Interval and hsplit.row_date = ANDREWSTABLE.SegStart_Date and ANDREWSTABLE.split1 = hsplit.split

我哪里出错了？它说我的语法不正确，我一直盯着看，用谷歌搜索，用谷歌搜索，但我想不出如何纠正这个问题！这一定是简单的事情。

再次感谢您的所有帮助！

Answer 1

您只需要列名的别名。由于它是派生表，因此需要它。

 Case Center
    When 'Concord Call Center' Then 'CCC'
    When 'Livermore Call Center' Then 'LCC'
    When 'Morgan Hill Call Center' Then 'MHCC'
    When 'Natomas Call Center' Then 'NCC'
    When 'Virtual Call Center' Then 'VCC'
    Else Center
 End as Center

Answer 2

这部分的格式不正确：

    ,Center
Case
When 'Concord Call Center' Then 'CCC'
When 'Livermore Call Center' Then 'LCC'
When 'Morgan Hill Call Center' Then 'MHCC'
When 'Natomas Call Center' Then 'NCC'
When 'Virtual Call Center' Then 'VCC'
Else Center
End
    ,q.Queue AS 'Xferfrom'

您要么需要在 Case 之前添加逗号，要么需要使用类似 Mycenter = 的内容来代替第一个 Center。