1

This is probably not the ideal query to retrieve rows containing specified values within a column, but the solution below effectively returns the values specified in a new column-alias but includes NULL values. How would the below query be constructed to exclude rows containing NULL values within the column alias?

SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS "NewSalutation"
FROM student.student

I welcome alternative approaches-thanks!

3
SELECT * FROM (
SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
) 
WHERE NewSalutation IS NOT NULL;

If that's your only section in the CASE statement, a logically equivalent query would be:

SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
WHERE salutation = 'Dr.';

... as anything other than Dr. will produce a NULL NewSalutation.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.