[Drew Rosen]

I have the following query that uses a multivalued parameter.  The user would key in many order numbers which would then access the following query I am getting the following error message

Msg 512, Level 16, State 1, Line 20

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I do not know if there is another method of setting a variable 

Here is the sql query

declare

@totalrate decimal(9,5) = 4.5,

@driverexcess decimal(9,5) = .25,

@multipledrivers varchar(1),

@orderno varchar(max),

@men int,

@lead int,

@driver int,

@OrdPriKey int

–AS

set @multipledrivers=‘N’

set @orderno=N’n04-28-15,bkas0087012,u1772-1025-7′

–declare

SET @OrdPriKey = (SELECT TOP 1 PriKey

FROM moverssuite2.dbo.Orders orders WHERE OrderNo = (Select * From dbo.ParseMultiValuedParameter( @orderno,‘,’ ) ) AND Archived = 0)

SET @men=(SELECT COUNT(DISTINCT CrewID)

FROM moverssuite2.dbo.LocServ ls JOIN

moverssuite2.dbo.LSCrew lsc ON ls.PriKey = lsc.LSPriKey WHERE OrdPriKey = @OrdPriKey )

SET @lead=(SELECT COUNT(DISTINCT CrewID)

FROM moverssuite2.dbo.LocServ ls JOIN

moverssuite2.dbo.LSCrew lsc ON ls.PriKey = lsc.LSPriKey WHERE OrdPriKey = @OrdPriKey and

lsc.LeadMan=1 )

set @driver=(SELECT COUNT(DISTINCT CrewID)

FROM moverssuite2.dbo.LocServ ls JOIN

moverssuite2.dbo.LSCrew lsc ON ls.PriKey = lsc.LSPriKey WHERE OrdPriKey = @OrdPriKey and

LaborTypeFID in (7,8,9))

SELECT @men

Select @lead

Select @driver

select b.OrderNo

,case

when c.LeadMan=1 and @multipledrivers=‘N’ then (((@totalrate–@driverexcess)/@men)+@driverexcess)

when c.LeadMan=0 and @multipledrivers=‘N’ then (((@totalrate–@driverexcess)/@men))

when c.leadMan=1 and @multipledrivers=‘Y’ and c.LaborTypeFID in (7,8,9) then (((@totalrate-(@driver*@driverexcess))/@men)+@driverexcess)

when c.leadman=0 and @multipledrivers=‘Y’ and c.LaborTypeFID in (7,8,9) then (((@totalrate-(@driver*@driverexcess))/@men)+@driverexcess)

when c.leadman=1 and @multipledrivers=‘Y’ and c.LaborTypeFID not in (7,8,9) then (((@totalrate-(@driver*@driverexcess))/@men))

when c.leadman=0 and @multipledrivers=‘Y’ and c.LaborTypeFID not in (7,8,9) then (((@totalrate-(@driver*@driverexcess))/@men))

else 0

end as rate

,* from

MoversSuite2.dbo.LocServ a inner join

MoversSuite2.dbo.Orders b on

a.OrdPriKey=b.PriKey inner join

MoversSuite2.dbo.LSCrew c on

a.PriKey=c.LSPriKey inner join

MoversSuite2.dbo.Sysuser d on

c.CrewID=d.SysUserID inner join

MoversSuite2.dbo.LaborType e on

c.LaborTypeFID=e.PriKey

where OrderNo=(Select * From dbo.ParseMultiValuedParameter( @orderno,‘,’ ) )

Thanks for any help. 

Drew G. Rosen

——————————
Drew Rosen
Carolina Services of Fayetteville, Inc
Fayetteville NC
——————————

[Matthew Arp]

SET @OrdPriKey = (SELECT TOP 1 PriKey

FROM moverssuite2.dbo.Orders orders WHERE OrderNo = (Select * From dbo.ParseMultiValuedParameter( @orderno,‘,’ ) ) AND Archived= 0)

This is incorrect, you can not have a OrderNo = (select *), SQL does not know what column you are trying to compare OrderNo to.

——————————
Matthew Arp
Business Systems Developer
Hunton Group
Houston TX
——————————
——————————————-

[Matthew Arp]

More than Likely it should be

OrderNo IN( SELECT Whatever FROM Parse….)

——————————
Matthew Arp
Business Systems Developer
Hunton Group
Houston TX
——————————
——————————————-

[Blair Christensen]

I’d actually suggest looking to change your query to support a table variable.  That will support the user being able to supply a list and you can join against the table to find the information desired.  You can try using a WHERE [column value] IN clause, but that one is going to be dependent on the user putting commas in between each item they pass in.

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-