子查询返回的值超过一个。在Server 2005中

Question

当我在我的应用程序中查询数据时，我得到以下错误

当子查询后面是=，!=，<，<=，>，>=或者子查询用作表达式时，返回的子查询超过一个<=是不允许的。 声明已被终止。

下面是我的存储过程。有人能帮我吗？

ALTER proc [dbo].[SP_RC_DS_2]

@CustomerID varchar(10),
@ExfacDateFrom Datetime,
@ExfacDateTo DateTime

SELECT
         SOD_SOLineNbr as Line_No
        ,IMA_LeadTimeCode as Manu_Pur
        ,IMA_Classification as Category
        ,case when IMA_ItemName is null then SOI_MiscLineDescription else IMA_ItemName end as ItemName
        --,SOI_MiscLineDescription as MiscLine
        ,IAD_AliasName as AliasName
        --,SOD_UserDef1 as PrivateRemark
        ,PrivateRemark =
            (Case CUS_CustomerID
            When 'C1175' Then   isnull(convert(nvarchar(200),SOM_SpecialInst),'')  + ' / ' + isnull(SOD_UserDef1,'')
            When 'C1209' Then   isnull(convert(nvarchar(200),SOM_SpecialInst),'')  + ' / ' + isnull(SOD_UserDef1,'')
            Else SOD_UserDef1
            End)
        ,SOD_UserDef2 as RemarkLine--
        ,CUS_CustomerID as CustomerID
        ,CSA_Name as CustomerName
        ,SOM_Destination as Destination
        ,SOM_ContainerSize as ContainerSize
        ,CSA_RecordID as CSA_RecordID
        ,SOM_SalesOrderID as SalesOrderID
        ,SOM_CustomerPOID as CustomerPO
        ,SOD_RequiredDate as ExFacDate
        ,SOM_DefaultDockDate as ETD
        ,SOM_ETA as ETA
        ,SOD_PromiseDate as PromiseDate
        ,SOD_RequiredQty as Qty
        ,SOI_SalesConvUnitMeasure as UnitMeasure
        ,SOD_UnitPrice as UnitPrice
        ,isnull(IMA_Weight,0) as NetWeight
        ,isnull(IMA_GrossWeight,0)as GrossWeight
        ,(Case When IMA_ProdFam is null then 'Others' else IMA_ProdFam end) as ProdFamilyItem
        ,(Case When IMA_ProdFam is null then 'Others' else (Select PFM_PGI From PFM Where PFM_ProdFam = IMA_ProdFam) end) as ProdFamInvGroup
        ,UnitVol =
                (Case When(Select Count(*) From RC_Special_UnitVol Where SU_CSA_RecordID =CSA_RecordID and SU_IMA_ItemID =IMA_ItemID) =1
                      Then (Select SU_CubicVolume From RC_Special_UnitVol Where SU_CSA_RecordID =CSA_RecordID and SU_IMA_ItemID =IMA_ItemID) 
                      Else isnull(IMA_CubicVolume,0) End)
        ,IMA_CubicVolUnitMeasure as VolUnitMeasure
        ,isnull((SOD_RequiredQty * 
                (Case When(Select distinct Count(*) From RC_Special_UnitVol Where SU_CSA_RecordID =CSA_RecordID and SU_IMA_ItemID =IMA_ItemID) =1
                      Then (Select SU_CubicVolume From RC_Special_UnitVol Where SU_CSA_RecordID =CSA_RecordID and SU_IMA_ItemID =IMA_ItemID) 
                      Else isnull(IMA_CubicVolume,0) End)),0) as TotalUnitVol
        ,SOM_DefaultVATCodeID as Vat
        ,SOM_SpecialInst as RemarkPO
        ,SOM_Buyer as OrderNumber
        ,isnull(EMP_MidName,'-')+' '+ isnull(EMP_LastName,'-')+' ('+ isnull(EMP_FirstName,'-')+')' as LastUpdateBy
        ,SOI_LineNbrTypeCode as LineTypeCode
        ,isnull(IMA_Kit,'') as Kit
        ,IMA_ItemID as ItemID--> Add
        ,day(SOM_DefaultRequiredDate) as [Day]
        ,month(SOM_DefaultRequiredDate) as [Month]
        ,year(SOM_DefaultRequiredDate) as [Year]

Into #OPS

FROM    SalesOrder
        Inner Join SalesOrderLine on SOM_RecordID = SOI_SOM_RecordID
        left Join ITEM On IMA_RecordID = SOI_IMA_RecordID
        left join ItemAliasDetail on IAD_RecordID = SOI_IAD_ID 
        inner Join SalesOrderDelivery On SOD_SOI_RecordID = SOI_RecordID
        inner Join EMP On SOM_EMP_RecordID = EMP_RecordID
        inner Join Customer On CUS_RecordId = SOM_CUS_RecordId
        inner join CustomerShipTo on CSA_RecordID =  SOM_DefaultCSA_RecordID

Where SOD_RequiredDate Between dbo.Fn_Rc_DateWithTime(@ExfacDateFrom,0) and dbo.Fn_Rc_DateWithTime(@ExfacDateTo,1)

And CUS_CustomerID = @CustomerID
And SOI_LineNbrTypeCode = 'Item'

order by  SOM_SalesOrderID,SOD_SOLineNbr
--Select * From #OPS
Select 
CustomerID,
CustomerName,
ItemID,
AliasName,
PartName = (Select  IMA_ItemName From Item Where IMA_ItemID = ItemID ), 
PrivateRemark,
Qty,
ExFacDate,
SalesOrderID,
CustomerPO,
OrderNumber,
where IMA_ItemID = ItemID)
UserDef1 = (Select WH_LOC from VW_ItemID_WH_Loc where IMA_ItemID = ItemID)

Into #TempFinish
From #OPS
order by ExFacDate

Select * From #TempFinish
Drop table #OPS
Drop table #TempFinish

Answer 1

当您使用Sub查询和=、>、< etc操作符和返回多个记录的子查询时，此错误将显示出来。

试着改变这个：

PartName = (Select  IMA_ItemName From Item Where IMA_ItemID = ItemID )

对此：

PartName = (Select Top 1  IMA_ItemName From Item Where IMA_ItemID = ItemID 

这适用于查询中存在相同场景的所有位置。