Conditional Query Using "AS" field

Question

SELECT "public"."mv_tags_per_org"."count" AS "count", "public"."mv_tags_per_org"."tag_name" AS "Tag Name",
CASE 
    WHEN "public"."mv_tags_per_org"."ngo_id" = 30 then 'SSS'
    WHEN "public"."mv_tags_per_org"."ngo_id" = 33 then 'PF'
    WHEN "public"."mv_tags_per_org"."ngo_id" = 34 then 'DS'
    ELSE 'Maybe'
END AS "NPO"
FROM "public"."mv_tags_per_org"

WHERE "NPO???" = "SSS"

Above you can see my code. It is currently returning exactly the output I want when you remove the "WHERE" function. I'm adding the "WHERE" function and attempting to access the new column I made called "NPO". It seems as if the column does not exist to the SQL editor, but it does exist when the query is ran. How do I access it?

Thanks!

I removed the inconsistent database tags. Please tag only with the database you are really using. But no databases allow the use of aliases in the `where` corresponding to the `select` where they are defined. There are four typical solutions: repeat the expression, use a subquery, use a CTE, use a lateral join. — Gordon Linoff, Mar 03 '20 at 15:06

The Impaler · Accepted Answer · 2020-03-03T20:38:20.857

1

Enclose your query into a "table expression" so you can produce a named column. Then you can use it in the WHERE clause:

select *
from ( -- table expression 'x' starts here
  SELECT
    "public"."mv_tags_per_org"."count" AS "count",
    "public"."mv_tags_per_org"."tag_name" AS "Tag Name",
  CASE 
    WHEN "public"."mv_tags_per_org"."ngo_id" = 30 then 'SSS'
    WHEN "public"."mv_tags_per_org"."ngo_id" = 33 then 'PF'
    WHEN "public"."mv_tags_per_org"."ngo_id" = 34 then 'DS'
    ELSE 'Maybe'
  END AS "NPO"
  FROM "public"."mv_tags_per_org"
) x
WHERE "NPO" = 'SSS'

Note: "table expressions" are also called "derived tables" and "inline views" by different teams of people.

edited Mar 03 '20 at 20:38

answered Mar 03 '20 at 15:07

The Impaler

45,731
9
39
76

1

That's actually called a "derived table" – Mar 03 '20 at 15:08
1

And SSS should be within Single quotes - 'SSS' – CR7SMS Mar 03 '20 at 15:12

score 0 · Answer 2 · answered Mar 03 '20 at 15:09

The WHERE clause cannot relate to a column alias defined in the SELECT clause (because the former it is evaluated before the latter).

This does not really matter for your use case, which can be simplified as:

SELECT 
    "public"."mv_tags_per_org"."count" AS "count",    
    "public"."mv_tags_per_org"."tag_name" AS "Tag Name",
CASE 
    WHEN "public"."mv_tags_per_org"."ngo_id" = 30 then 'SSS'
    WHEN "public"."mv_tags_per_org"."ngo_id" = 33 then 'PF'
    WHEN "public"."mv_tags_per_org"."ngo_id" = 34 then 'DS'
    ELSE 'Maybe'
END AS "NPO"
FROM "public"."mv_tags_per_org"
WHERE "public"."mv_tags_per_org"."ngo_id" = 30

Conditional Query Using "AS" field

2 Answers2