CODEWITHSUNDEEP
sqloracle-databaseselectwhere-clausedecode
Emma
Emma

Reputation: 167

SQL Decode - SELECT & WHERE

I have a query that I think needs to be something like this (the decode will be much bigger)

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 the where could be?

WHERE
decodeMath IN ('A','B','C')
OR decodeEng IN ('A','B');

The goal is to get all of the student marks in a certain format, but if say the user wanted to see students with only an 'A' in english or an 'A' in math (per a selection and the reason for the where) they see only those students - but all of the associated marks

Basically the question is: If I decode and alias in the SELECT do I need to decode in the WHERE as well? Would it be better if I converted the user selections to match the db instead of trying to decode? Although my db has two different types of data as marks, that I'm trying to convert into a single different type, which is also the what the user selects from.

Upvotes: 3

Views: 270

Answers (2)

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

Wrap it into a 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's into a grade from 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')

Upvotes: 2

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

You can not use alias names from the select list in the where clause, only in the order by clause. The reason for this is that the SQL is processed in steps. First the relevant rows are computed (the where clause), then the selected fields are computed (the SELECT clause) and last the rows are sorted (the order by clause).

There are several ways to address 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 select (as in the example above), or you can create the view separately and then use it like it was a table. The latter is very convenient if you need the same view in several SQLs.

I would also recommend that you create a function for the translation from mrk to grade.

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,

Upvotes: 1

Related Questions