时间戳

首页 > 解决方案 > SQL查询中为什么会引用未知列

sql - SQL查询中为什么会引用未知列

问题描述

我正在更新一些 SQL 查询以针对 MariaDB 而不是通过 SQL Anywhere 运行。我正在运行的一个查询出错了：

Error Code: 1054. Unknown column 'choice' in 'field list'

这是针对此查询：

   SELECT 
    (select firstname||' '||lastname||'  ('||service||')' from staff_members where id_number = customer_assignment_reviews.staff_member_id) as Rep,
    (select customer_firstname||' '|| customer_lastname from customers where id_number = customer_assignment_reviews.cs_id) as Cus,
    last_modified as "Response Date",replace(review_reason,'’','') as "Reason",
    (Select choice = CASE
     when accepted = 0 then 'No'
     when accepted = 1 then 'Yes'
     end) as "Accepted?"
    FROM customer_assignment_reviews
    where staff_member_id in (Select id_number from kar.staff_members where division_id = 6)
    and "Response Date" between today() - 7  and today() /* Date Range */
    and "Accepted?" = 'No'
    Order by 3 desc

此错误消息是否像听起来那样简单？只是说目标表上不存在“选择”列？

我只是想解释为什么这段代码（我继承的）会引用一个不存在的列。在运行时这里可以预料到一些事情吗？

标签： sqlmariadb

解决方案

您不需要在SELECT列表中使用子查询

SELECT
-- ...
(Select choice = CASE
     when accepted = 0 then 'No'
     when accepted = 1 then 'Yes'
     end) as "Accepted?"

=>
SELECT
     CASE
       when accepted = 0 then 'No'
       when accepted = 1 then 'Yes'
     end as "Accepted?"

Additionaly 语法SELECT alias = expression仅特定于 T-SQL：

SELECT alias = 1 
<=>
SELECT 1 AS alias

推荐阅读