I have requirement like exit seats of flight each row at least one seat must be used(occupied).
For ex : Exit row seats are 4ABC,4DEF,5ABC,5DEF . in 4ABC at least one seat to be used and 4 DEF at least one seat should be used same for 5th row also. if 4ABC,4DEF,5ABC,5DEF each row at least one seat not used then need throw warning . I have query which return below results
SELECT FLIGHT_CARRIER,FLIGHT_NUMBER,SEAT_NUMBER,PAX_ID,LOCATION_ATT,flight_date,int_row_pos,availability_attribute
FROM SEAT_ALLOC WHERE AIRLINE ='LL'
AND FLIGHT_CARRIER = 'LL'
AND LOCATION_ATT LIKE '%E%'
AND FLIGHT_NUMBER='7893'
AND flight_date=to_date('2021-10-10', 'YYYY-MM-DD');
Result
now how can validate each row to check availability_attribute is available.
I am planning to use HashMap to assign those values . But is there any query level we can handle like using case .
With sample data as below:
WITH
seat_alloc (FLIGHT_CARRIER, FLIGHT_NUMBER, SEAT_NUMBER, PAX_ID, LOCATION_ATT, INT_ROW_POS, FLIGHT_DATE, AVAILABILITY_ATTRIBUTE) AS
( Select 'LL', 7893, '3D', Null, 'W-B', 3, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 7893, '3E', Null, 'B-M', 3, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 7893, '3F', Null, 'A-B', 3, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 7893, '4A', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 7893, '4B', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 7893, '4C', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 7893, '4D', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 7893, '4E', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 7893, '4F', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 7893, '5A', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 7893, '5B', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 7893, '5C', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 7893, '5D', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 7893, '5E', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 7893, '5F', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 7893, '6A', Null, 'W-B', 6, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 7893, '6B', Null, 'B-M', 6, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 7893, '6C', Null, 'A-B', 6, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual
),
... create CTE with exit rows statuses:
exit_rows_stat AS
( Select FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS,
Case When SubStr(SEAT_NUMBER, -1) IN('A', 'B', 'C') Then 'ABC'
When SubStr(SEAT_NUMBER, -1) IN('D', 'E', 'F') Then 'DEF'
When SubStr(SEAT_NUMBER, -1) IN('H', 'J', 'K') Then 'HJK'
Else 'XXX'
End "EXIT_BLOCK",
Count(*) "BLOCK_SEATS",
Count(Case When AVAILABILITY_ATTRIBUTE = 'Occupied' Then 1 End) "OCCUPIED_CNT"
From seat_alloc
Group By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS,
Case When SubStr(SEAT_NUMBER, -1) IN('A', 'B', 'C') Then 'ABC'
When SubStr(SEAT_NUMBER, -1) IN('D', 'E', 'F') Then 'DEF'
When SubStr(SEAT_NUMBER, -1) IN('H', 'J', 'K') Then 'HJK'
Else 'XXX'
End
Having Max(InStr(LOCATION_ATT, 'E')) > 0
)
--
-- FLIGHT_CARRIER FLIGHT_NUMBER FLIGHT_DA INT_ROW_POS EXIT_BLOCK BLOCK_SEATS OCCUPIED_CNT
-- -------------- ------------- --------- ----------- ---------- ----------- ------------
-- LL 7893 11-OCT-23 4 DEF 3 3
-- LL 7893 11-OCT-23 4 ABC 3 3
-- LL 7893 11-OCT-23 5 DEF 3 0
-- LL 7893 11-OCT-23 5 ABC 3 0
--
To fetch warnings join the above CTE with your data:
-- M a i n S Q L :
SELECT s.FLIGHT_CARRIER, s.FLIGHT_NUMBER, s.FLIGHT_DATE, s.INT_ROW_POS, e.EXIT_BLOCK,
Case When e.OCCUPIED_CNT = 0
Then 'ALERT *** Exit Row ' || s.INT_ROW_POS || ' Block ' || e.EXIT_BLOCK || ' all seats are empty!'
End "WARNING"
FROM seat_alloc s
LEFT JOIN exit_rows_stat e ON(e.FLIGHT_CARRIER = s.FLIGHT_CARRIER And
e.FLIGHT_NUMBER = s.FLIGHT_NUMBER And
e.FLIGHT_DATE = s.FLIGHT_DATE And
e.INT_ROW_POS = s.INT_ROW_POS)
GROUP BY s.FLIGHT_CARRIER, s.FLIGHT_NUMBER, s.FLIGHT_DATE, s.INT_ROW_POS, e.EXIT_BLOCK,
Case When e.OCCUPIED_CNT = 0
Then 'ALERT *** Exit Row ' || s.INT_ROW_POS || ' Block ' || e.EXIT_BLOCK || ' all seats are empty!'
End
ORDER BY s.FLIGHT_CARRIER, s.FLIGHT_NUMBER, s.FLIGHT_DATE, s.INT_ROW_POS, e.EXIT_BLOCK
... the result should be ...
-- R e s u l t :
-- FLIGHT_CARRIER FLIGHT_NUMBER FLIGHT_DATE INT_ROW_POS EXIT_BLOCK WARNING
-- -------------- ------------- ----------- ----------- ---------- --------------------------------------------------------
-- LL 7893 11-OCT-23 3
-- LL 7893 11-OCT-23 4 ABC
-- LL 7893 11-OCT-23 4 DEF
-- LL 7893 11-OCT-23 5 ABC ALERT *** Exit Row 5 Block ABC all seats are empty!
-- LL 7893 11-OCT-23 5 DEF ALERT *** Exit Row 5 Block DEF all seats are empty!
-- LL 7893 11-OCT-23 6
This should give you warnings for different carriers/flight numbers/planes/dates/... and resulting dataset could be processed further to get you what you need.
UPDATE - Dynamic blocks of exit rows (after comments)
It looks like it is possible to get different blocks and seats of interest using just data provided. For this we will need a bit bigger sample dataset with three different seat settings.
WITH
seat_alloc (FLIGHT_CARRIER, FLIGHT_NUMBER, SEAT_NUMBER, PAX_ID, LOCATION_ATT, INT_ROW_POS, FLIGHT_DATE, AVAILABILITY_ATTRIBUTE) AS
( -- FLIGHT 1 - 6 seats - 2 blocks - abc + def - 2 exit rows
Select 'LL', 1111, '4A', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 1111, '4B', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 1111, '4C', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 1111, '4D', Null, 'A-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 1111, '4E', Null, 'E-M', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 1111, '4F', Null, 'W-E', 4, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 1111, '5A', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 1111, '5B', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 1111, '5C', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 1111, '5D', Null, 'A-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 1111, '5E', Null, 'E-M', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 1111, '5F', Null, 'W-E', 5, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
-- FLIGHT 2 - 8 seats - 3 blocks - ab + cdef + gh - 2 exit rows
Select 'LL', 2222, '8A', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 2222, '8B', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 2222, '8C', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 2222, '8D', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 2222, '8E', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 2222, '8F', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 2222, '8G', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 2222, '8H', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 2222, '9A', Null, 'W-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 2222, '9B', Null, 'E-M', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 2222, '9C', Null, 'A-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 2222, '9D', Null, 'A-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 2222, '9E', Null, 'E-M', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 2222, '9F', Null, 'W-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 2222, '9G', Null, 'E-M', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 2222, '9H', Null, 'W-E', 9, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
-- FLIGHT 3 - 7 seats - 3 blocks - ab + cde + fg - 3 exit rows
Select 'LL', 3333, '8A', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '8B', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '8C', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '8D', Null, 'A-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '8E', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '8F', Null, 'W-E', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '8G', Null, 'E-M', 8, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '19A', Null, 'W-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 3333, '19B', Null, 'E-M', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 3333, '19C', Null, 'A-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '19D', Null, 'A-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 3333, '19E', Null, 'E-M', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '19F', Null, 'W-E', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 3333, '19G', Null, 'E-M', 19, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 3333, '29A', Null, 'W-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '29B', Null, 'E-M', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '29C', Null, 'A-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 3333, '29D', Null, 'A-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 3333, '29E', Null, 'E-M', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual Union All
Select 'LL', 3333, '29F', Null, 'W-E', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Occupied' From Dual Union All
Select 'LL', 3333, '29G', Null, 'E-M', 29, To_Date('11.10.2023', 'dd.mm.yyyy'), 'Available' From Dual
),
Here we have three types of exit rows. First with 6 seats in the row that we wil split into 2 blocks with three seats each. Second flight has 8 seat row that will be split into 3 blocks wit 2-4-2 seats. The third flight has 7 seats exit rows to be split into three blocks with 2-3-2 seats. below is cte (exit_rows_stat) to do it:
exit_rows_stat AS
( Select Distinct FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS,
LISTAGG(Case When InStr(LOCATION_ATT, 'E') > 0 Then INT_ROW_POS || SubStr(SEAT_NUMBER, -1) End, '|') WITHIN GROUP (Order By SubStr(SEAT_NUMBER, -1))
OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS) "SEATS",
Case When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 6 Then 3
When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 7 Then 2
When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 8 Then 2
End "BLOCK_1",
Case When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 6 Then 3
When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 7 Then 3
When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 8 Then 4
End "BLOCK_2",
Case When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 6 Then 0
When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 7 Then 2
When Count(Distinct SubStr(SEAT_NUMBER, -1)) OVER(Partition By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE) = 8 Then 2
End "BLOCK_3"
From seat_alloc
),
/*
FL FLIGHT_NUMBER FLIGHT_DA INT_ROW_POS E SEATS BLOCK_1 BLOCK_2 BLOCK_3
-- ------------- --------- ----------- - ------------------------------ ---------- ---------- ----------
LL 1111 11-OCT-23 4 Y 4A|4B|4C|4D|4E|4F 3 3 0
LL 1111 11-OCT-23 5 Y 5A|5B|5C|5D|5E|5F 3 3 0
LL 2222 11-OCT-23 8 Y 8A|8B|8C|8D|8E|8F|8G|8H 2 4 2
LL 2222 11-OCT-23 9 Y 9A|9B|9C|9D|9E|9F|9G|9H 2 4 2
LL 3333 11-OCT-23 8 Y 8A|8B|8C|8D|8E|8F|8G 2 3 2
LL 3333 11-OCT-23 19 Y 19A|19B|19C|19D|19E|19F|19G 2 3 2
LL 3333 11-OCT-23 29 Y 29A|29B|29C|29D|29E|29F|29G 2 3 2 */
Next is another cte (exit_row_seat_blocks) to define the seats in blocks:
exit_row_seat_blocks AS
( Select FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS,
BLOCK_1, BLOCK_2, BLOCK_3,
'|' || SubStr(SEATS, 1, InStr(SEATS, '|', 1, BLOCK_1) - 1) || '|' "BLOCK_1_SEATS",
'|' || Case When BLOCK_3 = 0 Then SubStr(SEATS, InStr(SEATS, '|', 1, BLOCK_1) + 1)
Else SubStr(SEATS, InStr(SEATS, '|', 1, BLOCK_1) + 1, InStr(SEATS, '|', 1, BLOCK_1 + BLOCK_2) - InStr(SEATS, '|', 1, BLOCK_1) - 1)
End || '|' "BLOCK_2_SEATS",
'|' || Case When BLOCK_3 != 0 Then SubStr(SEATS, InStr(SEATS, '|', 1, BLOCK_1 + BLOCK_2) + 1) End || '|' "BLOCK_3_SEATS"
From exit_rows_stat
Order By FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS
)
/*
FL FLIGHT_NUMBER FLIGHT_DA INT_ROW_POS BLOCK_1 BLOCK_2 BLOCK_3 BLOCK_1_SEATS BLOCK_2_SEATS BLOCK_3_SEATS
-- ------------- --------- ----------- ---------- ---------- ---------- ------------------------ ------------------------ ------------------------
LL 1111 11-OCT-23 4 3 3 0 |4A|4B|4C| |4D|4E|4F| ||
LL 1111 11-OCT-23 5 3 3 0 |5A|5B|5C| |5D|5E|5F| ||
LL 2222 11-OCT-23 8 2 4 2 |8A|8B| |8C|8D|8E|8F| |8G|8H|
LL 2222 11-OCT-23 9 2 4 2 |9A|9B| |9C|9D|9E|9F| |9G|9H|
LL 3333 11-OCT-23 8 2 3 2 |8A|8B| |8C|8D|8E| |8F|8G|
LL 3333 11-OCT-23 19 2 3 2 |19A|19B| |19C|19D|19E| |19F|19G|
LL 3333 11-OCT-23 29 2 3 2 |29A|29B| |29C|29D|29E| |29F|29G| */
With this we can build the strings of empty seats per blocks. If that list of empty seats per block is equal to block's seat list from above then show alert in warning column...
SELECT FLIGHT_CARRIER, FLIGHT_NUMBER, PAX_ID, INT_ROW_POS, FLIGHT_DATE, Max(WARNING) "WARNING"
FROM ( Select Distinct a.FLIGHT_CARRIER, a.FLIGHT_NUMBER, a.SEAT_NUMBER, a.PAX_ID, a.LOCATION_ATT, a.INT_ROW_POS, a.FLIGHT_DATE, a.AVAILABILITY_ATTRIBUTE,
CASE WHEN InStr(b.BLOCK_3_SEATS, a.SEAT_NUMBER) > 0 And
'|' || LISTAGG(Distinct Case When a.AVAILABILITY_ATTRIBUTE = 'Available' And InStr(b.BLOCK_3_SEATS, '|' || a.SEAT_NUMBER || '|') > 0
Then a.SEAT_NUMBER End, '|') WITHIN GROUP (Order By a.SEAT_NUMBER)
Over(Partition By a.FLIGHT_CARRIER, a.FLIGHT_NUMBER, a.FLIGHT_DATE, a.INT_ROW_POS) || '|' = Replace(b.BLOCK_3_SEATS, '||', '')
THEN 'ALERT - Exit Row ' || a.INT_ROW_POS || ' all block 3 seats ' || b.BLOCK_3_SEATS || ' are empty!'
WHEN InStr(b.BLOCK_2_SEATS, a.SEAT_NUMBER) > 0 And
'|' || LISTAGG(Distinct Case When a.AVAILABILITY_ATTRIBUTE = 'Available' And InStr(b.BLOCK_2_SEATS, '|' || a.SEAT_NUMBER || '|') > 0
Then a.SEAT_NUMBER End, '|') WITHIN GROUP (Order By a.SEAT_NUMBER)
Over(Partition By a.FLIGHT_CARRIER, a.FLIGHT_NUMBER, a.FLIGHT_DATE, a.INT_ROW_POS) || '|' = BLOCK_2_SEATS
THEN 'ALERT - Exit Row ' || a.INT_ROW_POS || ' all block 2 seats ' || b.BLOCK_2_SEATS || ' are empty!'
WHEN InStr(b.BLOCK_1_SEATS, a.SEAT_NUMBER) > 0 And
'|' || LISTAGG(Distinct Case When a.AVAILABILITY_ATTRIBUTE = 'Available' And InStr(b.BLOCK_1_SEATS, '|' || a.SEAT_NUMBER || '|') > 0
Then a.SEAT_NUMBER End, '|') WITHIN GROUP (Order By a.SEAT_NUMBER)
Over(Partition By a.FLIGHT_CARRIER, a.FLIGHT_NUMBER, a.FLIGHT_DATE, a.INT_ROW_POS) || '|' = BLOCK_1_SEATS
THEN 'ALERT - Exit Row ' || a.INT_ROW_POS || ' all block 1 seats ' || b.BLOCK_1_SEATS || ' are empty!'
END "WARNING"
From seat_alloc a
Left Join exit_row_seat_blocks b ON(b.FLIGHT_CARRIER = a.FLIGHT_CARRIER And
b.FLIGHT_NUMBER = a.FLIGHT_NUMBER And
b.FLIGHT_DATE = a.FLIGHT_DATE And
b.INT_ROW_POS = a.INT_ROW_POS)
)
GROUP BY FLIGHT_CARRIER, FLIGHT_NUMBER, PAX_ID, INT_ROW_POS, FLIGHT_DATE
ORDER BY FLIGHT_CARRIER, FLIGHT_NUMBER, FLIGHT_DATE, INT_ROW_POS
/*
FL FLIGHT_NUMBER PAX_ID INT_ROW_POS FLIGHT_DA WARNING
-- ------------- ------ ----------- --------- ----------------------------------------------------------------
LL 1111 4 11-OCT-23
LL 1111 5 11-OCT-23 ALERT - Exit Row 5 all block 1 seats |5A|5B|5C| are empty!
LL 2222 8 11-OCT-23
LL 2222 9 11-OCT-23 ALERT - Exit Row 9 all block 2 seats |9C|9D|9E|9F| are empty!
LL 3333 8 11-OCT-23
LL 3333 19 11-OCT-23 ALERT - Exit Row 19 all block 3 seats |19F|19G| are empty!
LL 3333 29 11-OCT-23 ALERT - Exit Row 29 all block 2 seats |29C|29D|29E| are empty! */