Set all column values based on condition existing in one row using Snowflake

Question

Working on a view in Snowflake and based certain criteria I want to set the Pass/Fail column for all rows to "Pass" if a certain output is reached. Example (below)for a give Item number/ Plant combination, where the condition is met for one row, I would like to set all rows to "Pass"

Here is my case statement as is: I'm Having trouble getting this scenario to "Pass" for all rows

case
    when
        ((case
            when 'PIRStatus' is null
            then 'PIR-Missing'
            else 'PIR-Exists'
        end)='PIR-Exists' 
    and "FixedVendor" = 'X' 
    and (case 
            when "SLStatus" = 'SL-Exists'
        then 1 
        else 2
    end) = 1)
then 'Pass'
else 'Fail'
end as "Pass/Fail"

PIRStatus Vendor BlockedVendor FixedVendor SLStatus Pass/Fail PIR-Exists 12547 X SL-Exists Pass PIR-Exists 85996 SL-Missing Fail PIR-Exists 54788 SL-Missing Fail

This is based on a given Item/ Plant combination, as long as any row says pass then I want the other rows to Pass as well

Answer 1

You probably want to use a correlated subquery, which I find is best written with a CTE like this:

WITH CTE_CONDITION AS (
  SELECT 
    id, 
    case when (
      (
        case when 'PIRStatus' is null then 'PIR-Missing' else 'PIR-Exists' end
      )= 'PIR-Exists' 
      and "FixedVendor" = 'X' 
      and (
        case when "SLStatus" = 'SL-Exists' then 1 else 2 end
      ) = 1
    ) then 'Pass' else 'Fail' end as "Pass/Fail" 
  FROM 
    table
) 
SELECT 
  * ,
CASE WHEN EXISTS (SELECT 1 FROM CTE_CONDITION WHERE "Pass/Fail" = 'Pass' AND table.id = CTE_CONDITION.id) 
    THEN 'This ID Passes At Least Somewhere'
    ELSE 'This ID never passes' 
    END as DOES_THIS_EVER_PASS
FROM table 

The thing to remember using EXISTS is that the SELECT portion doesn't really matter (thus, SELECT 1) but it is the WHERE clause that connects the table itself to CTE_CONDITION.

You might even clean this up by creating CTE_PASS and CTE_FAIL and putting conditions in the WHERE clauses, to compartmentalize the logic and avoid that messy CASE statement.

Thus, you could accomplish the same thing with something like:

WITH CTE_PASS AS (
  SELECT id
  FROM table
  WHERE {conditions that mean a pass}
) 
SELECT 
  * ,
CASE WHEN EXISTS (SELECT 1 FROM CTE_PASS WHERE table.id = CTE_CONDITION.id) 
     THEN 'This ID Passes At Least Somewhere'
     ELSE 'This ID never passes' 
     END as DOES_THIS_EVER_PASS
FROM table