View join on column with case statement

Question

I have a view with a column using case statement. Is there a way I can join this dynamic value (column: health) with another table?

Table Product

| id | status |
| -- | ------ | 
| 1  | OK     |
| 2  | STABLE |
| 3  | FAILED |
| 4  | ABORT  |

TABLE ProductDetail

| health | score |
| ------ | ----- |
| GOOD   | 100   |
| FAIR   | 80    |
| POOR   | 60    |

Creating a view joining this column(health) with productDetail dynamically does not work

CREATE OR REPLACE VIEW MyView AS
   SELECT
   product.id,
   product.status,
   CASE
       WHEN product.status='OK' THEN 'GOOD'
       WHEN product.status='STABLE' THEN 'FAIR'
       ELSE 'POOR'
   END AS health,
   pd.score
   FROM Product product
   LEFT JOIN ProductDetail pd ON health = pd.health;

My actual case column is complex, it checks multiple fields. Appreciate any tips.

Answer 1

Transform your select from product into a local table by making it a sub-select creating the health column along the way. Then Join Product Details. (see demo)

create or replace view MyView as                 
    select id, status, pd.health                        
      from (                                            
             select product.id, product.status,         
                case                                             
                    when product.status='OK'    then 'GOOD'         
                    when product.status='STABLE'then 'FAIR'     
                    else 'POOR'                                  
                end as health 
               from product 
           ) p
       left join ProductDetail pd ON pd.health = p.health;