SQL Server CASE表达式中引用的CTE返回空值,我的CTE可作为独立查询正常工作

xesrikrc 于 2023-01-12 发布在其他

关注(0)|答案(1)|浏览(120)

我有一个按预期工作的查询，我需要在此查询的基础上构建一个包含条件值的新列。新列的结果将是日期或字符串“na”。为此，我编写了一个CASE语句，然后在语句中引用CTE以提供条件逻辑。
主查询（不添加CASE语句）作为独立查询运行，CTE正常工作，并在作为独立查询运行时提供正确的日期。
这是我第一次使用INSERT语句。如果我搞砸了，请告诉我。
查询主表：

CREATE TABLE mainquery(
   Region_ID       INTEGER  NOT NULL PRIMARY KEY
  ,messageid       INTEGER  NOT NULL
  ,name            VARCHAR(50) NOT NULL
  ,DateReceived    DATETIME  NOT NULL
  ,Datemodified    DATETIME  NOT NULL
  ,Messagestatus   INTEGER  NOT NULL
  ,clientid        VARCHAR(255)
  ,ClientFirstName VARCHAR(255) NOT NULL
  ,ClientLastName  VARCHAR(255) NOT NULL
  ,clientdob       DATETIME  NOT NULL
  ,Supervisorid    INTEGER  NOT NULL
  ,visitid         VARCHAR(255) NOT NULL
  ,SuperName       VARCHAR(255) NOT NULL
  ,SuperID         VARCHAR(255) NOT NULL
  ,colldate        VARCHAR(255) NOT NULL
  ,colltime        VARCHAR(255) NOT NULL
  ,Ordername       VARCHAR(255) NOT NULL
  ,errorlogs       VARCHAR(8000) NOT NULL
  ,comments        VARCHAR(255)
  ,last_visit_date DATETIME
);
INSERT INTO mainquery(Region_ID,messageid,name,DateReceived,Datemodified,Messagestatus,clientid,ClientFirstName,ClientLastName,clientdob,Supervisorid,visitid,SuperName,SuperID,colldate,colltime,Ordername,errorlogs,comments,last_visit_date) VALUES (1,116113842,'R1_OG','2022-06-09 13:07:52.000','2022-06-09 13:07:52.000',4,'123456789','Fake','Name','1980-01-01  00:00:00.000',123,'741852963','Joe','J1234','2022-05-06','16:27:00','fake_order','Supervisor Match not found',NULL,NULL);
INSERT INTO mainquery(Region_ID,messageid,name,DateReceived,Datemodified,Messagestatus,clientid,ClientFirstName,ClientLastName,clientdob,Supervisorid,visitid,SuperName,SuperID,colldate,colltime,Ordername,errorlogs,comments,last_visit_date) VALUES (2,159753205,'SEL North','2022-03-12 04:07:85.000','2018-06-25 12:07:00.000',2,'963741258','Funny','Namely','1999-02-03 00:00:00.000',98524,'159654','David','DL652','2018-01-24','09:03:00','real_fake','Supervisor Match not found',NULL,NULL);
INSERT INTO mainquery(Region_ID,messageid,name,DateReceived,Datemodified,Messagestatus,clientid,ClientFirstName,ClientLastName,clientdob,Supervisorid,visitid,SuperName,SuperID,colldate,colltime,Ordername,errorlogs,comments,last_visit_date) VALUES (3,951789369,'Blue_South','2022-03-11 12:08:33.000','2022-03-11 12:08:33.001',2,NULL,'Who','Ami','2000-08-11 00:00:00.000',789456,'963123','Shirley','S852','2017-05-14','09:30:00','example_order','Client Match not found','here is a comment','na');
INSERT INTO mainquery(Region_ID,messageid,name,DateReceived,Datemodified,Messagestatus,clientid,ClientFirstName,ClientLastName,clientdob,Supervisorid,visitid,SuperName,SuperID,colldate,colltime,Ordername,errorlogs,comments,last_visit_date) VALUES (4,294615883,'Mtn-Dew','2017-09-06 16:20:00.000','2017-09-06 16:20:00.001',2,NULL,'Why','Tho','1970-11-20 00:00:00.000',9631475,'159654852','Bob','B420','2022-09-22','10:25:31','example_example','Client Match not found',NULL,'na');
INSERT INTO mainquery(Region_ID,messageid,name,DateReceived,Datemodified,Messagestatus,clientid,ClientFirstName,ClientLastName,clientdob,Supervisorid,visitid,SuperName,SuperID,colldate,colltime,Ordername,errorlogs,comments,last_visit_date) VALUES (5,789963258,'Home-Base','2022-07-11 15:22:40.000','2022-07-11 15:22:40.001',2,NULL,'Where','Aru','1987-01-06 00:00:00.000',805690123,'805460378','Carlos','C999','2022-07-11','07:30:45','order_order','Client Match not found',NULL,'na');

CTE临时表：

CREATE TABLE CTE(
   uid        INTEGER  NOT NULL PRIMARY KEY
  ,clientdob  DATETIME  NOT NULL
  ,clienttype INTEGER  NOT NULL
  ,date       DATETIME  NOT NULL
  ,visitid    VARCHAR(255) NOT NULL
  ,Region_ID  INTEGER  NOT NULL
  ,facilityid INTEGER  NOT NULL
  ,locationid INTEGER  NOT NULL
);
INSERT INTO CTE(uid,clientdob,clienttype,date,visitid,Region_ID,facilityid,locationid) VALUES (123456789,'1980-01-01  00:00:00.000',3,'2022-09-18 00:00:00.000','741852963',1,240,32);
INSERT INTO CTE(uid,clientdob,clienttype,date,visitid,Region_ID,facilityid,locationid) VALUES (963741258,'1999-02-03 00:00:00.000',3,'2022-05-11 00:00:00.000','159654',2,606,123);
INSERT INTO CTE(uid,clientdob,clienttype,date,visitid,Region_ID,facilityid,locationid) VALUES (852654320,'1994-05-11 00:00:00.000',3,'2019-03-18 00:00:00.000','123456',3,632,12);
INSERT INTO CTE(uid,clientdob,clienttype,date,visitid,Region_ID,facilityid,locationid) VALUES (85360123,'1997-08-16 00:00:00.000',3,'2021-02-19 00:00:00.000','7896451',4,856,147);
INSERT INTO CTE(uid,clientdob,clienttype,date,visitid,Region_ID,facilityid,locationid) VALUES (85311456,'1964-10-31 00:00:00.000',3,'2016-02-14 00:00:00.000','85263',5,852,15);

预期视图：
编辑screenshot of wide table以添加：我在“预期视图”的屏幕截图上犯了个错误。屏幕截图显示了一个表现在的样子的例子。理想的视图是在last_visit_date中有日期（在适当的行中），而不是空。
我的问题如下：该查询仍然可以使用新的CASE语句和CTE，只是在新列中没有提供正确的日期，而是返回一个空值。
我处理此查询时是否有错误的想法？我认为我的方法是正确的，但现在我怀疑自己。我尝试使用子查询而不是CTE，但收到错误消息，指出由于每行有多个结果，因此无法完成查询。
以下是我的主要质询和相关CTE：

WITH last_visit (uid, clientdob, clienttype, date, visitid, Region_ID, facilityid, locationid) AS
(
    SELECT DISTINCT 
        u.uid, u.clientdob, u.clienttype, 
        CONVERT(varchar, v.date) AS last_visit, 
        v.visitid, u.Region_ID, v.facilityid, v.locationid
    FROM 
        users u
    LEFT JOIN 
        visit v ON u.uid = v.clientid
                AND u.Region_ID = v.Region_ID
    WHERE 
        v.date = (SELECT MAX(v.date) FROM visit v WHERE v.clientid = u.uid)
        AND u.clienttype = 3
        AND u.uid <> 8663
        AND u.ulname NOT LIKE '%test%'
        AND u.ulname NOT LIKE '%unidentified%'
        AND u.delflvg = 0
        AND v.visittype = 1
        AND v.facilityid <> 0
        AND v.deleteflag = 0
)
SELECT 
    r.Region_ID, r.messageid, l.name, r.DateReceived, r.DateModified, 
    r.MessageStatus, r.clientid, r.ClientFirstName, r.ClientLastName, r.clientdob, 
    r.Supervisorid, r.visitid, r.SuperName, r.SuperID, r.colldate, r.colltime, 
    r.OrderName, r.errorlogs, a.comments,
    CASE 
        WHEN r.errorlogs LIKE 'Supervisor Match not found' 
            THEN lv.date 
            ELSE 'na' 
    END AS last_visit_date
FROM
    electronicresults r
JOIN 
    recelectronicresults a ON r.messageid = a.messageid 
                           AND r.Region_ID = a.Region_ID
LEFT OUTER JOIN 
    users u ON r.clientid = u.uid
LEFT OUTER JOIN 
    visit v ON r.clientid = v.clientid AND r.visitid = v.visitid
LEFT OUTER JOIN 
    last_visit lv ON r.visitid = lv.visitid 
                  AND r.clientid = lv.uid
JOIN 
    lblist l ON r.lbid = l.id 
             AND r.Region_ID = l.Region_ID
WHERE 
    r.MessageStatus IN (0, 2, 4)
    AND a.actiontaken = 0
    AND l.deleteflag = 0
GROUP BY 
    r.Region_ID, r.messageid, l.name, r.DateReceived, r.DateModified, 
    r.MessageStatus, r.clientid, r.ClientFirstName, r.ClientLastName, 
    r.clientdob, r.Supervisorid, r.visitid, r.SuperName, r.SuperID, 
    r.colldate, r.colltime, r.OrderName, r.errorlogs, a.comments, lv.date

我感谢您花时间阅读本文，任何支持、提示、技巧都将不胜感激

sql-server

来源：https://stackoverflow.com/questions/73859234/referenced-cte-in-case-expression-is-returning-null-values-my-cte-works-correct

1条答案

按热度按时间

oknwwptz1#

当您提供了一个脚本来创建表和示例数据时，我满怀希望，但不幸的是，您提供的脚本无法正常工作-您无法对DATETIME列中的值'na'执行INSERT，正如所指出的那样。此外，DATETIME示例值之一无效："2022 - 03 - 12 04：07：85. 000"（没有85秒的时间）。不管怎样，我纠正了问题，继续前进。
您的示例查询中存在一个问题-CASE语句在一种情况下返回DATETIME，而在另一种情况下返回VARCHAR。这是不合法的-所有情况都必须返回相同的数据类型。我没有返回"na"，而是选择了NULL。
注解掉所有未包含的表后，我就可以得到一个正在运行的查询：

CREATE TABLE mainquery(
   Region_ID       INTEGER  NOT NULL PRIMARY KEY
  ,messageid       INTEGER  NOT NULL
  ,name            VARCHAR(50) NOT NULL
  ,DateReceived    DATETIME  NOT NULL
  ,Datemodified    DATETIME  NOT NULL
  ,Messagestatus   INTEGER  NOT NULL
  ,clientid        VARCHAR(255)
  ,ClientFirstName VARCHAR(255) NOT NULL
  ,ClientLastName  VARCHAR(255) NOT NULL
  ,clientdob       DATETIME  NOT NULL
  ,Supervisorid    INTEGER  NOT NULL
  ,visitid         VARCHAR(255) NOT NULL
  ,SuperName       VARCHAR(255) NOT NULL
  ,SuperID         VARCHAR(255) NOT NULL
  ,colldate        VARCHAR(255) NOT NULL
  ,colltime        VARCHAR(255) NOT NULL
  ,Ordername       VARCHAR(255) NOT NULL
  ,errorlogs       VARCHAR(8000) NOT NULL
  ,comments        VARCHAR(255)
  ,last_visit_date DATETIME
);
INSERT INTO mainquery(Region_ID,messageid,name,DateReceived,Datemodified,Messagestatus,clientid,ClientFirstName,ClientLastName,clientdob,Supervisorid,visitid,SuperName,SuperID,colldate,colltime,Ordername,errorlogs,comments,last_visit_date) VALUES (1,116113842,'R1_OG','2022-06-09 13:07:52.000','2022-06-09 13:07:52.000',4,'123456789','Fake','Name','1980-01-01  00:00:00.000',123,'741852963','Joe','J1234','2022-05-06','16:27:00','fake_order','Supervisor Match not found',NULL,NULL);
INSERT INTO mainquery(Region_ID,messageid,name,DateReceived,Datemodified,Messagestatus,clientid,ClientFirstName,ClientLastName,clientdob,Supervisorid,visitid,SuperName,SuperID,colldate,colltime,Ordername,errorlogs,comments,last_visit_date) VALUES (2,159753205,'SEL North','2022-03-12 04:07:55.000','2018-06-25 12:07:00.000',2,'963741258','Funny','Namely','1999-02-03 00:00:00.000',98524,'159654','David','DL652','2018-01-24','09:03:00','real_fake','Supervisor Match not found',NULL,NULL);
INSERT INTO mainquery(Region_ID,messageid,name,DateReceived,Datemodified,Messagestatus,clientid,ClientFirstName,ClientLastName,clientdob,Supervisorid,visitid,SuperName,SuperID,colldate,colltime,Ordername,errorlogs,comments,last_visit_date) VALUES (3,951789369,'Blue_South','2022-03-11 12:08:33.000','2022-03-11 12:08:33.001',2,NULL,'Who','Ami','2000-08-11 00:00:00.000',789456,'963123','Shirley','S852','2017-05-14','09:30:00','example_order','Client Match not found','here is a comment',NULL);
INSERT INTO mainquery(Region_ID,messageid,name,DateReceived,Datemodified,Messagestatus,clientid,ClientFirstName,ClientLastName,clientdob,Supervisorid,visitid,SuperName,SuperID,colldate,colltime,Ordername,errorlogs,comments,last_visit_date) VALUES (4,294615883,'Mtn-Dew','2017-09-06 16:20:00.000','2017-09-06 16:20:00.001',2,NULL,'Why','Tho','1970-11-20 00:00:00.000',9631475,'159654852','Bob','B420','2022-09-22','10:25:31','example_example','Client Match not found',NULL,NULL);
INSERT INTO mainquery(Region_ID,messageid,name,DateReceived,Datemodified,Messagestatus,clientid,ClientFirstName,ClientLastName,clientdob,Supervisorid,visitid,SuperName,SuperID,colldate,colltime,Ordername,errorlogs,comments,last_visit_date) VALUES (5,789963258,'Home-Base','2022-07-11 15:22:40.000','2022-07-11 15:22:40.001',2,NULL,'Where','Aru','1987-01-06 00:00:00.000',805690123,'805460378','Carlos','C999','2022-07-11','07:30:45','order_order','Client Match not found',NULL,NULL);

CREATE TABLE CTE(
   uid        INTEGER  NOT NULL PRIMARY KEY
  ,clientdob  DATETIME  NOT NULL
  ,clienttype INTEGER  NOT NULL
  ,date       DATETIME  NOT NULL
  ,visitid    VARCHAR(255) NOT NULL
  ,Region_ID  INTEGER  NOT NULL
  ,facilityid INTEGER  NOT NULL
  ,locationid INTEGER  NOT NULL
);
INSERT INTO CTE(uid,clientdob,clienttype,date,visitid,Region_ID,facilityid,locationid) VALUES (123456789,'1980-01-01  00:00:00.000',3,'2022-09-18 00:00:00.000','741852963',1,240,32);
INSERT INTO CTE(uid,clientdob,clienttype,date,visitid,Region_ID,facilityid,locationid) VALUES (963741258,'1999-02-03 00:00:00.000',3,'2022-05-11 00:00:00.000','159654',2,606,123);
INSERT INTO CTE(uid,clientdob,clienttype,date,visitid,Region_ID,facilityid,locationid) VALUES (852654320,'1994-05-11 00:00:00.000',3,'2019-03-18 00:00:00.000','123456',3,632,12);
INSERT INTO CTE(uid,clientdob,clienttype,date,visitid,Region_ID,facilityid,locationid) VALUES (85360123,'1997-08-16 00:00:00.000',3,'2021-02-19 00:00:00.000','7896451',4,856,147);
INSERT INTO CTE(uid,clientdob,clienttype,date,visitid,Region_ID,facilityid,locationid) VALUES (85311456,'1964-10-31 00:00:00.000',3,'2016-02-14 00:00:00.000','85263',5,852,15);

SELECT 
    r.Region_ID, r.messageid, r.DateReceived, r.DateModified, 
    r.MessageStatus, r.clientid, r.ClientFirstName, r.ClientLastName, r.clientdob, 
    r.Supervisorid, r.visitid, r.SuperName, r.SuperID, r.colldate, r.colltime, 
    r.OrderName, r.errorlogs, 
    CASE 
        WHEN r.errorlogs LIKE 'Supervisor Match not found' 
            THEN lv.date 
            ELSE NULL 
    END AS last_visit_date
FROM
    mainquery r
LEFT OUTER JOIN 
    CTE lv ON r.visitid = lv.visitid 
                  AND r.clientid = lv.uid
WHERE 
    r.MessageStatus IN (0, 2, 4)
GROUP BY 
    r.Region_ID, r.messageid, r.DateReceived, r.DateModified, 
    r.MessageStatus, r.clientid, r.ClientFirstName, r.ClientLastName, 
    r.clientdob, r.Supervisorid, r.visitid, r.SuperName, r.SuperID, 
    r.colldate, r.colltime, r.OrderName, r.errorlogs, lv.date

下面是输出：
| 区域_ID|消息标识符|接收日期|修改日期|消息状态|客户|客户名字|客户姓氏|客户数据库|监督员|可见性|超级用户名|超级ID|拼贴画|拼贴时间|订单名称|错误日志|末次访视日期|
| - ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|- ------|
| 1个|小行星116113842|2022年6月9日13时7分52秒000|2022年6月9日13时7分52秒000|四个|小行星123456789|假的|姓名|1980年1月1日00时00分|一百二十三|小行星741852963|乔|小行星1234|2022年5月6日|16点27分|假订单|未找到主管匹配|2022年9月18日00时00分00秒|
| 第二章|小行星159753|2022年3月12日04：07：55.000|2018年6月25日12时07分00秒|第二章|小行星963741258|好笑|即|一九九九年二月三日零时|小行星98524|小行星159654|大卫|DL652|2018年1月24日|09时03分|真假|未找到主管匹配|2022年5月11日00时00分00秒|
| 三个|小行星951789369|2022年3月11日12时08分33秒|2022年3月11日12时08分33秒|第二章|零|谁|亚美|二○ ○ ○年八月十一日零时|小行星789456|小行星963123|雪莉|S852|2017年5月14日|九时三十分|示例_订单|未找到客户端匹配项|零|
| 四个|小行星2946|2017年9月6日16时20分00秒|2017年9月6日16时20分00秒|第二章|零|为什么|寿|1970年11月20日00时00分00秒|小行星9631475|小行星15965|鲍勃|B420|二〇二二年九月二十二日|十点二十五分三十一秒|范例_范例|未找到客户端匹配项|零|
| 五个|小行星78996|2022年7月11日15时22分四万|2022年7月11日15时22分四万|第二章|零|地点|阿鲁|1987年1月6日00时00分00秒|小行星805690123|八○五四六○三七八|卡洛斯|C999|2022年7月11日|07时30分45秒|订单|未找到客户端匹配项|零|
同样，您必须添加回我必须删除的JOIN（因为表没有包括在内）以及SELECT列表、WHERE子句和'GROUP BY'子句中的一些项。

赞(0）回复(0）举报 2023-01-12

我来回答

SQL Server CASE表达式中引用的CTE返回空值,我的CTE可作为独立查询正常工作

1条答案

相关问题

热门标签

最新问答