CODEWITHSUNDEEP
sqlsql-servercase
MNada
MNada

Reputation: 383

SQL - Select statement inside case

I want to select particular columns from a table when some field in other table is not null.

In other words, I have two tables, they have common fields so when field named "salesordrkey" in table number 1 is not null then I need to get the common fields from table number 2 otherwise get the common fields from table number 1

Here's what I am trying to do

Select slsordr.salesordrkey,
       whissue.warehissuekey, 
       issuppk.issueprodpackkey,
       (Case When  whissue.salesordrkey Is Not Null Then
           (Select slsordr.busipartnerkey,
                   slsordr.contractkey,
                   slsordr.salesmankey,
                   slsordr.customerkey)
        Else
           (Select whissue.busipartnerkey,
                   whissue.contractkey,
                   whissue.salesmankey,
                   slsordr.customerkey)
        End)
 From warehissues whissue 
     Inner Join issueprodpacks issuppk on whissue.warehissuekey = issuppk.warehissuekey
     Left Join slssalesordrs slsordr on whissue.salesordrkey = slsordr.salesordrkey 
 Where
      whissue.partitionkey = @prtnkey and 
      issuppk.prodpackkey = @prodpackkey

but this didn't work .. is there any other way to achieve that?

Upvotes: 0

Views: 199

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

I am assuming that your logic is backwards. Instead of:

case when  whissue.salesordrkey is not null . . .

I think you intend:

case when  slsordr.salesordrkey is not null

The first doesn't check to see if the left join is working.

So, you can use coalesce():

select slsordr.salesordrkey, whissue.warehissuekey, issuppk.issueprodpackkey,
       coalesce(slsordr.busipartnerkey, whissue.busipartnerkey) as busipartnerkey,
       coalesce(slsordr.contractkey, whissue.contractkey) as contractkey,
       coalesce(slsordr.salesmankey, whissue.salesmankey),
       coalesce(slsordr.customerkey, whissue.customerkey)
from warehissues whissue inner join
     issueprodpacks issuppk
     on whissue.warehissuekey = issuppk.warehissuekey left join 
     slssalesordrs slsordr
     on whissue.salesordrkey = slsordr.salesordrkey
where whissue.partitionkey = @prtnkey and 
      issuppk.prodpackkey = @prodpackkey

I should note that this is not 100% exactly the same logic. It is possible that some records with a match have NULL values in the slsordr table. Using coalesce() would then bring in the values from the other table, even though there is a match. However, this is often the logic.

Otherwise, you need to use multiple case statements:

       (case when slsorderkey is not null then slsordr.busipartnerkey
             else whissue.busipartnerkey
        end) as busipartnerkey,

case is an expression. It returns only one value, not multiple values.

Upvotes: 2

Related Questions