CODEWITHSUNDEEP
t-sql
Arkadiusz
Arkadiusz

Reputation: 427

Correlated subquery with null

I need to use correlated subquery with CASE WHEN statement. The problem is that there are cases when correlated subquery may return NULL. I combine correlated subquery with another column and because subquery returns NULL I receive null in column "result".

Here's the example:

create table ##sygnatura (ID int, syg_numer varchar(50))

create table ##sprawa (ID int, sp_numer varchar(50))

create table ##dluznik (ID int, nazwa varchar(max))

insert into ##sygnatura 
select null,null

insert into ##sprawa
select 1,'abc'

insert into ##dluznik
select 1,'XYZ'

select sp_numer,
case when nazwa='XYZ' then (select isnull(syg_numer,'') from ##sygnatura where ##sygnatura.ID=##sprawa.ID)+', '+isnull(nazwa, '')  end as result
From ##sprawa 
join ##dluznik on  ##sprawa.ID=##dluznik.ID

Upvotes: 1

Views: 489

Answers (3)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

I think you can easily get the expected result using another JOIN like this :

select sp_numer, 
case when nazwa='XYZ' then isnull(syg_numer + ', ', '') + isnull(nazwa, '')  end as result
From ##sprawa 
join ##dluznik on  ##sprawa.ID=##dluznik.ID
left join ##sygnatura on  ##sprawa.ID=##sygnatura.ID

Upvotes: 0

JMabee
JMabee

Reputation: 2300

You need to move the ISNULL to encapsulate the subquery because you are getting a NULL result due to the subquery not returning any rows:

select sp_numer,
case when nazwa='XYZ' then ISNULL((select isnull(syg_numer,'') from  ##sygnatura where ##sygnatura.ID=##sprawa.ID) + ', ','') + isnull(nazwa, '')    end as result
From ##sprawa 
join ##dluznik on  ##sprawa.ID=##dluznik.ID

Upvotes: 3

Pawel Czapski
Pawel Czapski

Reputation: 1864

Below is one way, however probably there is some better way to resolve your issue than using EXISTS

select sp_numer,
case when nazwa='XYZ' 
            and exists (select 1 from ##sygnatura where ##sygnatura.ID=##sprawa.ID)
        then (select isnull(syg_numer,'') from ##sygnatura where ##sygnatura.ID=##sprawa.ID)+', '+isnull(nazwa, '')  
    else isnull(nazwa, '') 
    end as result
From ##sprawa 
join ##dluznik on  ##sprawa.ID=##dluznik.ID

Upvotes: 0

Related Questions