CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
almaje
almaje

Reputation: 3

SQL Server : even I used 'In' the subquery returned more than 1 values

Since yesterday I am stuck with this request, it returns an error when hs.HOSPITALIZESPECIALTYID equal to 3 or 4. But when my field equals 1 or 2 it works.

hs.HOSPITALIZESPECIALTYID = 1 ====> su.UNITKEY equals  3 or 4
hs.HOSPITALIZESPECIALTYID = 2 ====> su.UNITKEY equals   9 or 10
hs.HOSPITALIZESPECIALTYID = 3 ====> su.UNITKEY equals   12
hs.HOSPITALIZESPECIALTYID = 4 ====> su.UNITKEY equals   13 or 14

And here is the error:

The subquery returned more than 1 values. This is not allowed when the subquery follows =,! =, <, <=,>,> = Or when it is used as an expression.

Thanks for your help

select c.[HISTORYNUMBER]
      ,c.[NAMECUSTOMER]
      ,c.[FIRSTSURNAMECUSTOMER]
      --,c.[SECONDSURNAMECUSTOMER] 
      ,ts.DESCRIPTION as Genre
      ,convert(date,c.[DATEBIRTH],103) as 'Date de naissance'
      ,c.[ADDRESS]
      ,a.[ENTITY]
      ,s.SPECIALTYDESCRIPTION
      ,su.DESCRIPTION as Unité
      ,hs.HOSPITALIZATIONDATE
     ,hs.OUTPUTDATE
     ,hs.MOVEMSPECIALITYID
      from HealthcareProcs..HospProcess hs
inner join PatientManagement..Customer c
on c.CUSTOMERID = hs.CUSTOMERID
inner join Configuration..agreement a
on hs.AGREEMENTID = a.[KEY]
inner join Configuration..specialty s
on s.[KEY] = hs.HOSPITALIZESPECIALTYID
left join configuration..SpecialtyUnits su
on  su.SPECIALTY = s.[KEY]
inner join Configuration..Hospitalspecialty hsp
on hsp.SPECIALITYID = s.[KEY]
inner join configuration..tables ts
on ts.[KEY] = c.GENDER
and ts.[TABLE] = 'EstGenero'

where hs.outputdate is null 
and hs.HOSPITALIZESPECIALTYID in (3)
and hs.ENTRYID is not null
--and hospitalizationdate >= @datedebut + ' 00:00:00'
--and hospitalizationdate <=  @datefin+ ' 23:59:59'
--and hsp.HEALTHCENTREID = @centre
and su.UNITKEY in (12)

and c.GENDER in (case 
                when (su.UNITKEY = 4 or su.UNITKEY = 10) and (hs.HOSPITALIZESPECIALTYID = 1 or hs.HOSPITALIZESPECIALTYID = 2) then 0   
                when (su.UNITKEY = 3 or su.UNITKEY = 9) and (hs.HOSPITALIZESPECIALTYID = 1 or hs.HOSPITALIZESPECIALTYID = 2) then 1 
                when (su.UNITKEY in (3,4) or su.UNITKEY in (9,10) ) and (hs.HOSPITALIZESPECIALTYID = 1 or hs.HOSPITALIZESPECIALTYID = 2) then (select distinct gender from PatientManagement..Customer where GENDER in (0,1))
                when hs.HOSPITALIZESPECIALTYID = 3 then (select [key] from configuration..tables ts where  ts.[TABLE] = 'EstGenero')
                when hs.HOSPITALIZESPECIALTYID = 4 and su.UNITKEY in (13,14) then (select [key] from configuration..tables ts where  ts.[TABLE] = 'EstGenero')
                    end)

Upvotes: 0

Views: 63

Answers (1)

farzaaaan
farzaaaan

Reputation: 410

Do either of these queries return more than one value? 

select distinct gender from PatientManagement..Customer where GENDER in (0,1)

select [key] from configuration..tables ts where  ts.[TABLE] = 'EstGenero'

Since when your fields are 1 or 2 it works, I think the second query is the one returning more than one value.

Upvotes: 1

Related Questions