select value from same row using case on table oracle

select value from same row using case on table oracle

I have a table like that in oracle:

Then I want to select customer_type and my new column named ‘customer_id’. If customer_type is ‘CORPORATE’ then ‘customer_id’ column will filled with shipper_id else if customer_type is ‘RETAIL’ the column (‘customer_id’) will filled with shipper_phone.
This is my query:
select
shipper_id,
shipper_name,
customer_type,
case customer_type when ‘RETAIL’ then shipper_phone
when ‘CORPORATE’ then shipper_id
else ‘Y’
from
connote c
inner join
mst_customer mc
on c.shipper_id = mc.customer_id ;

Solutions/Answers:

Solution 1:

You are missing the END keyword in the CASE statement:

SELECT shipper_id,
  shipper_name,
  customer_type,
  CASE customer_type
    WHEN 'RETAIL'
    THEN shipper_phone
    WHEN 'CORPORATE'
    THEN shipper_id
    ELSE 'Y'
  END
FROM connote c
INNER JOIN mst_customer mc
ON c.shipper_id = mc.customer_id;

The same could be written using DECODE too:

SELECT shipper_id,
  shipper_name,
  customer_type,
  DECODE(customer_type, 
         'RETAIL',
          shipper_phone,
         'CORPORATE',
          shipper_id,
         'Y')
FROM connote c
INNER JOIN mst_customer mc
ON c.shipper_id = mc.customer_id;

References