Query returned more than 1 value

1 

and SCREEN_MASTER.SCR_SCREEN in (
Case When @ScreenId is null
then
(select '0' + cast([ScreenNo] as char(2)) from [dbo].[ZZ_CinemaScreen_ScreenType] where [dbo].[ZZ_CinemaScreen_ScreenType].ScreenTypeId is null)
else
(select '0' + cast([ScreenNo] as char(2)) from [dbo].[ZZ_CinemaScreen_ScreenType] where [dbo].[ZZ_CinemaScreen_ScreenType].ScreenTypeId = @ScreenId)
end

I have a null or number - char situation
There problem is that it will write that the query returned more than one value.
As I can't figure it out (I think case returns a single value so I cannot use an in)
I'm trying to use a temp table at the moment.
But is there a better solution ?
Thanks.

2

can you please provide some sample SCR_SCREEN and ZZ_CinemaScreen_ScreenType data

3

Try writing your predicate like this:

and SCREEN_MASTER.SCR_SCREEN in (

select '0' + cast([ScreenNo] as char(2)) 
from [dbo].[ZZ_CinemaScreen_ScreenType] 
where (@ScreenId is null and [dbo].[ZZ_CinemaScreen_ScreenType].ScreenTypeId is null)
    or (@ScreenId is NOT null and [dbo].[ZZ_CinemaScreen_ScreenType].ScreenTypeId  = @ScreenId)
)
1 Like
4

Hey.
I think you need a not null here?
Or I'm mistaken?
As I need to take all values if it is null

@cinemaId is null and [dbo].[ZZ_CinemaScreen_ScreenType].CinemaCode is not null
5

that's not what you had in your original post.

6

Yes.
It was my fault :slight_smile: