Please note that I'm not a developer, so any help here would be greatly appreciated.
My problem: I have list in Oracle Responsys that contains all customers booked on a tour. I want to find all records that have missing values for FIRST_NAME (could be Tba/Tbc/Null), so I can send the main booker an email requesting that they update the name details for their additional guests.
The main booker will always have a CUSTOMER_ID_ value ending in '-1'. The records with missing first name values won't.
What I want to do, is select all records from the list that have missing first name values, group them by BOOKING_LOCATOR (a value that is shared by the main booker and their additional guests) - THEN only return the main bookers (records with CUSTOMER_ID_ ending in '-1'.
So far, I've pieced together this rough idea of what Im after from looking at other posts, but it doesn't return anything like what I'm after:
SELECT $A$.BOOKING_LOCATOR, $A$.CUSTOMER_ID_, $A$.EMAIL_ADDRESS_
FROM
(
SELECT $A$.BOOKING_LOCATOR FROM $A$ WHERE ($A$.FIRST_NAME IN ('TBA', 'TBC', 'Tba', 'Tbc') OR $A$.FIRST_NAME IS NULL) AND $A$.POLAR_BOOKING_STATUS != 'C' AND $A$.BOOKING_STATUS != 'Waitlist' AND $A$.EMBARK_DATE >= SYSDATE+1
GROUP BY $A$.BOOKING_LOCATOR
) $A$
INNER JOIN (
SELECT $A$.BOOKING_LOCATOR AS BOOKINGNUM, $A$.CUSTOMER_ID_, $A$.EMAIL_ADDRESS_ FROM $A$ WHERE $A$.CUSTOMER_ID_ LIKE '%-1'
) $A$ ON $A$.BOOKING_LOCATOR = $A$.BOOKINGNUM
Can someone guide me in the right direction?
Give a name to the sub-selects:
SELECT A.BOOKING_LOCATOR, B.CUSTOMER_ID_, B.EMAIL_ADDRESS_
FROM
(
SELECT $A$.BOOKING_LOCATOR FROM $A$ WHERE ($A$.FIRST_NAME IN ('TBA', 'TBC', 'Tba', 'Tbc') OR $A$.FIRST_NAME IS NULL) AND $A$.POLAR_BOOKING_STATUS != 'C' AND $A$.BOOKING_STATUS != 'Waitlist' AND $A$.EMBARK_DATE >= SYSDATE+1
GROUP BY $A$.BOOKING_LOCATOR
) A
INNER JOIN (
SELECT $A$.BOOKING_LOCATOR AS BOOKINGNUM, $A$.CUSTOMER_ID_, $A$.EMAIL_ADDRESS_ FROM $A$ WHERE $A$.CUSTOMER_ID_ LIKE '%-1'
) B ON A.BOOKING_LOCATOR = B.BOOKINGNUM;