I'm working on a query in Oracle using the old-style outer join syntax (+), and I'm running into the following error:
ORA-01417: a table may be outer joined to at most one other table
I understand that Oracle doesn't allow a table to be outer joined to more than one other table using this syntax. However, I need to check for missing customer notification preferences across multiple relationships.
Here’s a my query:
SELECT c.customer_id, nic.notification_item_id, ns.schedule_id
FROM customers c,
notification_item_channels nic,
notification_sub_items nsi,
notification_channels nc,
subitem_schedules ss,
notification_schedules ns,
notification_items ni,
cust_notif_prefs cnp
WHERE nic.sub_item_id = nsi.sub_item_id
AND nic.channel_id = nc.channel_id
AND nsi.sub_item_id = ss.sub_item_id
AND ss.schedule_id = ns.schedule_id
AND ni.item_id = nsi.item_id
AND ni.item_type IN (1, 2)
AND ni.item_id = 11
AND c.customer_id = cnp.customer_id(+)
AND nic.notification_item_id = cnp.notification_item_id(+)
AND ns.schedule_id = cnp.schedule_id(+)
AND cnp.preference_id IS NULL;
Data
Table Creation
CREATE TABLE customers (
customer_id NUMBER PRIMARY KEY,
customer_name VARCHAR2(50)
);
CREATE TABLE notification_items (
item_id NUMBER PRIMARY KEY,
item_name VARCHAR2(50),
item_type NUMBER
);
CREATE TABLE notification_sub_items (
sub_item_id NUMBER PRIMARY KEY,
item_id NUMBER
);
CREATE TABLE notification_channels (
channel_id NUMBER PRIMARY KEY,
channel_name VARCHAR2(50)
);
CREATE TABLE notification_item_channels (
notification_item_id NUMBER PRIMARY KEY,
sub_item_id NUMBER,
channel_id NUMBER
);
CREATE TABLE subitem_schedules (
sub_item_id NUMBER,
schedule_id NUMBER
);
CREATE TABLE notification_schedules (
schedule_id NUMBER PRIMARY KEY,
schedule_desc VARCHAR2(50)
);
CREATE TABLE cust_notif_prefs (
preference_id NUMBER PRIMARY KEY,
customer_id NUMBER,
notification_item_id NUMBER,
schedule_id NUMBER
);
Insert date
-- Customers
INSERT INTO customers VALUES (1, 'Alice');
INSERT INTO customers VALUES (2, 'Bob');
-- Notification Items
INSERT INTO notification_items VALUES (11, 'Trade Alert', 1);
INSERT INTO notification_items VALUES (12, 'Balance Update', 2);
-- Notification Sub Items
INSERT INTO notification_sub_items VALUES (101, 11);
INSERT INTO notification_sub_items VALUES (102, 12);
-- Notification Channels
INSERT INTO notification_channels VALUES (201, 'Email');
INSERT INTO notification_channels VALUES (202, 'SMS');
-- Notification Item Channels
INSERT INTO notification_item_channels VALUES (301, 101, 201);
INSERT INTO notification_item_channels VALUES (302, 102, 202);
-- Subitem Schedules
INSERT INTO subitem_schedules VALUES (101, 401);
INSERT INTO subitem_schedules VALUES (102, 402);
-- Notification Schedules
INSERT INTO notification_schedules VALUES (401, 'Daily');
INSERT INTO notification_schedules VALUES (402, 'Weekly');
-- Customer Notification Preferences
INSERT INTO cust_notif_prefs VALUES (1, 1, 11, 401);
I want to find customers who do not have a notification preference set for a specific item and schedule. The customer_notification_preferences table links customers, notification items, and schedules. I need to check for nulls across all three relationships.
I know I can use ANSI-style joins, but the current codebase uses legacy join standards. ANSI is planned for later, but right now we need a quick solution that's safe for legacy code. We need a fix that works without refactoring everything.
How can I rewrite this query using old Oracle join syntax to avoid the ORA-01417 error? Is there a workaround that still allows me to check for missing preferences across multiple columns?
I myself prefer Oracle-style over ANSI in most situations, but this is one where ANSI is definitely better. As you've noted that you do not want to rewrite the entire thing in ANSI, you can mix them, as long as the ANSI style joins come later than the Oracle-style ones and the Oracle-style ones are all inner joins. So one option is to just convert that one final join to ANSI and leave all the others:
SELECT c.customer_id,
nic.notification_item_id,
ns.schedule_id
FROM customers c,
notification_item_channels nic,
notification_sub_items nsi,
notification_channels nc,
subitem_schedules ss,
notification_schedules ns,
notification_items ni
LEFT OUTER JOIN customer_notification_preferences cnp ON c.customer_id = cnp.customer_id
AND nic.notification_item_id = cnp.notification_item_id
AND ns.schedule_id = cnp.schedule_id
WHERE nic.sub_item_id = nsi.sub_item_id
AND nic.channel_id = nc.channel_id
AND nsi.sub_item_id = ss.sub_item_id
AND ss.schedule_id = ns.schedule_id
AND ni.item_id = nsi.item_id
AND ni.item_type IN (1, 2)
AND ni.item_id = 11
AND cnp.preference_id IS NULL
However, per your request, to write this using purely Oracle-style and get around the limitation of outer joining from more than one table, you simply need to delay the join until in an outer query block, so that there's effectively only one "table" (row source, the nested query block) joining to it:
SELECT x.*
FROM (SELECT c.customer_id,
nic.notification_item_id,
ns.schedule_id
FROM customers c,
notification_item_channels nic,
notification_sub_items nsi,
notification_channels nc,
subitem_schedules ss,
notification_schedules ns,
notification_items ni
WHERE nic.sub_item_id = nsi.sub_item_id
AND nic.channel_id = nc.channel_id
AND nsi.sub_item_id = ss.sub_item_id
AND ss.schedule_id = ns.schedule_id
AND ni.item_id = nsi.item_id
AND ni.item_type IN (1, 2)
AND ni.item_id = 11) x,
customer_notification_preferences cnp
WHERE x.customer_id = cnp.customer_id(+)
AND x.notification_item_id = cnp.notification_item_id(+)
AND x.schedule_id = cnp.schedule_id(+)
AND cnp.preference_id IS NULL;
Of course, since you aren't actually pulling anything from that outer joined table, but only checking to see that there isn't a row (on the assumption that preference_id is a non-nullable [PK likely] column), you could more simply just write a NOT IN or NOT EXISTS subquery:
SELECT c.customer_id,
nic.notification_item_id,
ns.schedule_id
FROM customers c,
notification_item_channels nic,
notification_sub_items nsi,
notification_channels nc,
subitem_schedules ss,
notification_schedules ns,
notification_items ni
WHERE nic.sub_item_id = nsi.sub_item_id
AND nic.channel_id = nc.channel_id
AND nsi.sub_item_id = ss.sub_item_id
AND ss.schedule_id = ns.schedule_id
AND ni.item_id = nsi.item_id
AND ni.item_type IN (1, 2)
AND ni.item_id = 11
AND NOT EXISTS (SELECT *
FROM customer_notification_preferences cnp
WHERE c.customer_id = cnp.customer_id
AND nic.notification_item_id = cnp.notification_item_id
AND ns.schedule_id = cnp.schedule_id)