CODEWITHSUNDEEP
sqldb2-400
HDWomack
HDWomack

Reputation: 77

SQL: Case when being called in a where clause

I'm working with a pretty big query that I typed, but I going to shorten it on here. What I'm trying to do is select a bunch of columns and then run a case when and use a where not in clause to filter the results so I only see what a user is Authorized. If I remove the PERM NOT IN from my where clause it returns what a user is authorized and unauthorized for and I don't want to see that. I'm not 100% sure if it is possible and if it can be done another way or simpler that would be great.

Here's my query:

SELECT 
    usname AS Name, ususer AS Username, usseclevel AS Default_Level, 
    usglsecl AS GL_SecurityLevel, aoopid, aoseclevel
    CASE 
       WHEN aoauopid >= 00500 AND aoAuOpID < 10000 
          THEN
             CASE 
                WHEN usglsecl >= aoseclevel 
                   THEN 'Authorized' 
                   ELSE 'Unauthorized' 
             END
    END AS PERM
FROM
    library.file 
JOIN
    library.file ON usseclevel >= aoseclevel
WHERE 
    ususer NOT IN ('*ALL', 'daffron') 
    AND PERM NOT IN ('Unauthorized, 'null')
ORDER BY 
    ususer

If you would like for me to post my whole entire query I will, but I don't think it requires the whole query to get this desired output.

Upvotes: 0

Views: 202

Answers (1)

The Impaler
The Impaler

Reputation: 48850

I'm gonna go out on a limb here since I'm not sure which variant of SQL are you using. You can't normally use a column alias in the WHERE clause. Off the top of my head, I see three options:

Option 1 - Use a "table expression" to produce a bona fide column name from the alias:

select * from (
  Select usname as Name, ususer as Username, usseclevel as Default_Level, 
  usglsecl as GL_SecurityLevel, aoopid, aoseclevel
  Case When aoauopid >=00500 and aoAuOpID <10000 then case when 
  usglsecl>=aoseclevel then 'Authorized' else 'Unauthorized' end END AS PERM
  from library.file join library.file on usseclevel>=aoseclevel
  WHERE ususer NOT IN('*ALL','daffron') 
) x
WHERE PERM NOT IN('Unauthorized,'null')
ORDER BY ususer

Option 2 - Use a CTE (Common Table Expression):

with x as (
  Select usname as Name, ususer as Username, usseclevel as Default_Level, 
  usglsecl as GL_SecurityLevel, aoopid, aoseclevel
  Case When aoauopid >=00500 and aoAuOpID <10000 then case when 
  usglsecl>=aoseclevel then 'Authorized' else 'Unauthorized' end END AS PERM
  from library.file join library.file on usseclevel>=aoseclevel
  WHERE ususer NOT IN('*ALL','daffron') 
)
select * from x 
WHERE PERM NOT IN('Unauthorized,'null')
ORDER BY ususer

Option 3 - Repeat the whole CASE again in the WHERE clause (instead of PERM):

Select usname as Name, ususer as Username, usseclevel as Default_Level, 
usglsecl as GL_SecurityLevel, aoopid, aoseclevel
Case When aoauopid >=00500 and aoAuOpID <10000 then case when 
usglsecl>=aoseclevel then 'Authorized' else 'Unauthorized' end END AS PERM
from library.file join library.file on usseclevel>=aoseclevel
WHERE ususer NOT IN('*ALL','daffron') 
and Case When aoauopid >=00500 and aoAuOpID <10000 then case when 
usglsecl>=aoseclevel then 'Authorized' else 'Unauthorized' end END
    NOT IN('Unauthorized,'null')
ORDER BY ususer

Upvotes: 4

Related Questions