I am using Oracle 11g.
I have two tables:
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:
x
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";
Advertisement
Answer
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;