CODEWITHSUNDEEP
sqlsql-server
Hemal
Hemal

Reputation: 73

Case expression, how to get value from column in Then clause

I need to get a value for THEN from Mortgage_Type column if bellow conditions are true.

  1. Mortgage_Type and Category are same table and Equipment from another table.

Tables are joining using Item_No. I need to find the Mortgage_Type of each item. I have 20+ Mortgage_Types if Category is main and Equipment is null then should display relevant Mortgage_Type

 when SUM (Mortgage_Type)is not null and SUM (Equipment)is null and
    sum(Category) ='M' THEN “value from Mortgage_Type column”

Upvotes: 4

Views: 15733

Answers (5)

Shushil Bohara
Shushil Bohara

Reputation: 5656

If your mortgage_type column is not in the table that is using in the join then

--Use this

CASE 
   WHEN (Mortgage_Type)is not null and (Equipment)is null and (Category) ='M' 
    THEN (SELECT Mortgage_Type FROM <table_name> WHERE <condition>)
    ELSE <........>
END

If mortgate_type column't table exist in the join then

--Use this

CASE 
   WHEN (Mortgage_Type)is not null and (Equipment)is null and (Category) ='M' 
    THEN mortgage_type
    ELSE <........>
END

Upvotes: 0

Nikhil Shetkar
Nikhil Shetkar

Reputation: 346

Lets say our two tables are Mortgage_details(Item_No,Mortgage_type,category) and Equipment_Details(Item_No,Equipment) assume you own datatypes for the columns.

In order to select the required Mortgage_Type you could have a case statement in below query as :

select Item_No,
case when Mortgage_Type is not null and Equipment is null and Category ='M' 
THEN Mortgage_Type
Else Mortgage_Type 
End as Relevent_Mortgage_type,
from Mortgage_details m
join Equipment_details e
on m.Item_no = e.Item_no;

Instead of Mortgage_Type in Else You can have any value you want for rows where above conditions are not satisfied.

Upvotes: 0

Allan
Allan

Reputation: 12438

The SQL case statement should be used if you want to output a different name for each value of your category, for example:

*

CASE 
       WHEN cat=1 THEN 'category one'
       WHEN cat=2 THEN 'category two'
       WHEN cat=3 THEN 'category tree'
       ELSE 'other category'
END

*

You could prepare a simple SQL statement with a join on your tables as described hereunder : 

select A.Mortgage_Type,B.Item_No from table_A A, table_B B where A.Item_No = B.Item_No
and A.Mortgage_Type is not null and B.Equipment is null and A.Category ='M'

I do not really understand your "B.equipment is null" condition, is it mandatory? In case you do not have any Morgage_type do you want to give a default value?

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You could do as follow :

CASE  WHEN Col1 is not null AND Col2 is null AND Col3 = 'M' THEN 'value'
      ...
      ELSE 0 END as Column

Or 

CASE Col1 WHEN <value> THEN '' WHEN <value> THEN '' ELSE <your value>END

Upvotes: 1

Ofir Winegarten
Ofir Winegarten

Reputation: 9365

just give the column name:

CASE 
   WHEN (Mortgage_Type)is not null and (Equipment)is null and (Category) ='M' 
    THEN Mortgage_Type
    ELSE <other value>
END

Upvotes: 1

Related Questions