CODEWITHSUNDEEP
sqloracle-databasecase
Samit Paudel
Samit Paudel

Reputation: 181

Using CASE with Table Join

I am using Oracle 11g.

I have two tables:

enter image description here

enter image description here

I want to display student_name, course, fee and a column with discounted fee where the fee is reduced by 10% if it is either 'BIT' or 'MIT'.

I've come up with following query but it gives an error:

ORA-00923: FROM keyword not found where expected:

SQL> SELECT Student.student_name, Specification.course, Specification.specification_name, Specification.fee
  2  FROM Student
  3  JOIN Specification ON Student.specification_id = Specification.specification_id
  4     CASE Specification.course WHEN 'BIT' THEN 0.9 * Specification.fee
  5                               WHEN 'MIT' THEN 0.9 * Specification.fee
  6     ELSE Specification.fee END "DISCOUNTED FEE";

Upvotes: 0

Views: 57

Answers (2)

D-Shih
D-Shih

Reputation: 46239

From your question, you seem like need to use CASE WHEN in SELECT instead of FROM

SELECT Student.student_name, Specification.course, Specification.specification_name, Specification.fee,
       (CASE WHEN  Specification.cours = 'BIT' THEN 0.9 * Specification.fee
             WHEN  Specification.cours = 'MIT' THEN 0.9 * Specification.fee
        ELSE Specification.fee END) "DISCOUNTED FEE"
FROM Student
JOIN 
    Specification ON Student.specification_id = Specification.specification_id;

I would like to use CASE WHEN with IN let the SQL be cleaner.

SELECT Student.student_name, Specification.course, Specification.specification_name, Specification.fee,
       (CASE WHEN Specification.cours IN ('BIT','MIT') THEN 0.9 * Specification.fee
        ELSE Specification.fee END) "DISCOUNTED FEE"
FROM Student
JOIN 
    Specification ON Student.specification_id = Specification.specification_id;

Upvotes: 1

Popeye
Popeye

Reputation: 35920

You can use decode as follows:

SELECT Student.student_name, 
       Specification.course, 
       Specification.specification_name, 
       decode(Specification.course, 'BIT', 0.9, 'MIT', 0.9, 1)
         * Specification.fee AS "DISCOUNTED FEE"
  FROM Student
  JOIN Specification ON Student.specification_id = Specification.specification_id;

Upvotes: 0

Related Questions