SQL decoding - SELECT & WHERE
I have a request which I think should be something like this (the decoding will be much more)
SELECT
firstName,
lastName,
decode(mathMrk, 80, 'A', mathMrk) as decodeMath,
decode(engMrk, 80, 'A', engMrk) as decodeEng,
FROM table
WHERE
decode(mathMrk, 80, 'A', mathMrk) IN ('A','B','C')
OR decode(engMrk, 80, 'A', engMrk) IN ('A','B');
OR where could it be?
WHERE
decodeMath IN ('A','B','C')
OR decodeEng IN ('A','B');
The goal is to get all students' grades in a specific format, but if we say that the user only wanted to see students with the letter "A" in English or "A" in math (optional and for a reason where) they only see those students but all associated labels
Basically the question is, if I decode and alias in SELECT, do I need to decode in WHERE as well? Would it be better if I convert the user settings to match the db instead of trying to decode? Although my db has two different data types as labels, I am trying to convert them to the same type, which is also user selectable.
source to share
You cannot use select list aliases in a sentence where
, only in a sentence order by
. The reason for this is that SQL is processed step by step. The corresponding rows are calculated first (sentence where
), then the calculated fields are calculated (sentence SELECT
) and the last rows are sorted (sentence order by
).
There are several ways to solve the problem. The most common way is to use a view (explicitly or implicitly):
SELECT
firstName,
lastName,
decodeMath,
decodeEng,
FROM
(SELECT
firstName,
lastName,
decode(mathMrk, 80, 'A', mathMrk) as decodeMath,
decode(engMrk, 80, 'A', engMrk) as decodeEng,
FROM table) tableview
WHERE
decodeMath IN ('A','B','C')
OR decodeEng IN ('A','B');
You can either have all the code for the view in your choice (like in the example above), or create the view separately and then use it just like a table. The latter is very handy if you need the same view across multiple SQLs.
I would also recommend creating a function to translate from mrk to class.
CREATE OR REPLACE FUNCTION decodeMrk(mrk in Integer) RETURN Varchar2(1) IS
BEGIN
return CASE WHEN mrk >= 80 THEN 'A'
WHEN mrk >= 60 THEN 'B' ... -- and so on
END;
END;
Then you can use it in your view:
decodeMrk(mathMrk) as decodeMath,
source to share
Paste it into the CASE clause. You can also use CASE in the WHERE clause.
Here is a (simple) working example that should run on any Oracle schema.
I am converting object_id to class 1 to 100 for this example.
with grades as
(select object_name as student,
mod (object_id, 99) + 1 as mathmark,
case
when mod (object_id, 100) + 1 between 0 and 20 then 'F'
when mod (object_id, 100) + 1 between 21 and 40 then 'D'
when mod (object_id, 100) + 1 between 41 and 60 then 'C'
when mod (object_id, 100) + 1 between 61 and 80 then 'B'
when mod (object_id, 100) + 1 between 81 and 100 then 'A'
end
as mathdecode
from user_objects)
select *
from grades
where mathdecode in ('A', 'B')
source to share