Using COALESCE correctly in WHERE clause

Can someone explain why coalesce doesn’t work in the where clause given the following conditions below? How do we use coalesce correctly in this case without changing the below coalesce conditions and only for spoiled = Y?

Table Fruit:

  ITEM_NAME     ITEM_NO     SPOILED
  Apples        A15354        N 
  Bananas       BYHUG1        N
  Grapes        GR0013        Y     
  Oranges       ORULYE        N
  Guavas        GUOIUW        Y

Query:

  select fruit.item_name
  from fruit
  where fruit.item_no = coalesce('A15354','CURR_NOT_IN_TABLE','GR0013','GUOIUW')
  and fruit.spoiled = 'Y'

Using the query above will not return anything. Desired output should be grapes.

Desired Output:

  Grapes

Answer

We can use ROW_NUMBER here to select what you want with priorities:

WITH cte AS (
    SELECT f.*, ROW_NUMBER() OVER (ORDER BY DECODE(ITEM_NO, 'A15354', 1,
                                                            'CURR_NOT_IN_TABLE', 2,
                                                            'GR0013', 3,
                                                            'GUOIUW', 4, 5)) rn
    FROM fruit f
    WHERE spoiled = 'Y'
)

SELECT ITEM_NAME
FROM cte
WHERE rn = 1;

The idea here is to assign a priority from 1 to 5 for each item which is spoiled. We use ROW_NUMBER to generate a sequence always starting with 1 being the highest available priority.