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?
ITEM_NAME ITEM_NO SPOILED Apples A15354 N Bananas BYHUG1 N Grapes GR0013 Y Oranges ORULYE N Guavas GUOIUW Y
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.
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.