This subquery is part of the SELECT statement for a much larger query but obviously I cut it down to keep things simple.
Essentially the end result I want is:
1) The Subquery should search the table for all rows for all rows containing the HEADER_ID of the current row
2) If it finds a single 'Y' in COLUMN_A in the current results then the value should be 'BOOKED'
2) Otherwise they are obviously all 'N' and it should return 'CLOSED'
I don't think performance should be an issue the most each header id might return is 8-10
Example A:
N BOOKED
Y BOOKED
N BOOKED
N BOOKED
Y BOOKED
Example B:
N CLOSED
N CLOSED
N CLOSED
I put together the code below to see if I could get the logic right but I've filed I've failed....
SELECT
OPEN_FLAG AS COLUMN_A,
(SELECT DECODE(COUNT(1),0,'BOOKED','CLOSED')
FROM dual
WHERE EXISTS ( SELECT 1
FROM ONT.OE_ORDER_LINES_ALL
WHERE OPEN_FLAG = 'Y') AND HEADER_ID = 599995)
AS COLUMN_B
FROM ONT.OE_ORDER_LINES_ALL
WHERE HEADER_ID = 599995
Thanks in advance., any help is appreciated.
I've been baying my head against the wall for 2 hours with this between google and my own efforts and I just can't get it right