当在sql查询中没有引入EXISTS错误的子查询时,只能在选择列表中指定一个表达式 [英] Only one expression can be specified in the select list when the subquery is not introduced with EXISTS error in sql query
本文介绍了当在sql查询中没有引入EXISTS错误的子查询时,只能在选择列表中指定一个表达式的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
这是我的查询,我将数据插入临时表。
Hi,
here is my query where i am inserting data to temp table.
insert into #tmpImportTruckMin(
Month,
Count,
ID,
ContainerSize,
TruckTime,
ContainerOrder
)
values
(
(
SELECT (case when @monthnoImportTime = 1 then 'Jan'
when @monthnoImportTime = 2 then 'Feb'
when @monthnoImportTime = 3 then 'Mar'
when @monthnoImportTime= 4 then 'Apr'
when @monthnoImportTime = 5 then 'May'
when @monthnoImportTime= 6 then 'Jun'
when @monthnoImportTime = 7 then 'Jul'
when @monthnoImportTime= 8 then 'Aug'
when @monthnoImportTime = 9 then 'Sep'
when @monthnoImportTime = 10 then 'Oct'
when @monthnoImportTime = 11 then 'Nov'
when @monthnoImportTime = 12 then 'Dec' end
),
ISNULL(COUNT(GatePassId),0),
@monthnoImportTime,
'20FT',
AVG(
CASE
WHEN Status = 'In Yard' thenISNULL(DATEDIFF(MINUTE,TimeIn,getdate()),0)
when status = 'Left The Yard' then ISNULL(DATEDIFF(MINUTE,TimeIn,TimeOut),0)
else '0' end
),
1
from tbl_CMS_GatePass
where MONTH(AddedTime) = @monthnoImportTime
and YEAR(AddedTime) = @Year
and DepotID =@DepotID
and BranchID = @branchID
AND ((ShippingLine IS NULL OR ShippingLine=0) AND (@ShippingLine IS NULL OR @ShippingLine=0) OR ShippingLine= CASE WHEN ((@ShippingLine=0) OR (@ShippingLine IS NULL)) THEN ShippingLine ELSE @ShippingLine END)
and ((Customer IS NULL OR Customer=0) AND (@Client IS NULL OR @Client=0) OR Customer= CASE WHEN ((@Client=0) OR (@Client IS NULL)) THEN Customer ELSE @Client END) and ContainerSize = 2))
推荐答案
您正在使用for INSERT INTO [TABLE](COL1,COL2 ... COLn)VALUES(VAL1,VAL2 ... VALn),但不提供值列表,而是提供SELECt语句。那个SELECT语句返回多行,这就是错误的原因...
你有两个选择:
1.优化SELECT语句返回单行
2.使用INSERT INTO [TABLE](COL1,COL2 ... COLn)SELECT VAL1,VAL2 ... VALn FROM [OTHER TABLE]
You are using the for of INSERT INTO [TABLE] (COL1, COL2... COLn) VALUES(VAL1, VAL2... VALn), but instead of list of values you supply a SELECt statement. That SELECT statement returns more than one rows and that's the cause of the error...
You have two option:
1. Refine the SELECT statement to return single row
2. use INSERT INTO [TABLE] (COL1, COL2... COLn) SELECT VAL1, VAL2... VALn FROM [OTHER TABLE]
这篇关于当在sql查询中没有引入EXISTS错误的子查询时,只能在选择列表中指定一个表达式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文