Is there a way I can expand a query if one value is null in Oracle SQL?

Question

Is there a way that I can expand an existing table into something like a view, replacing the (null) value with multiple records? Basically, (null) represents that the user has access to all the roles within a group.

For example, can I combine the following two tables:

user	group	role
example1	ABC	100
example1	XYZ	200
example2	ABC	(null)

group	role
ABC	100
ABC	150
ABC	200
ABC	250
ABC	300
XYZ	200
XYZ	400

Into the following table view:

user	group	role
example1	ABC	100
example1	XYZ	200
example2	ABC	100
example2	ABC	150
example2	ABC	200
example2	ABC	250
example2	ABC	300

Is there any way that this could be possible?

MT0 · Accepted Answer · 2025-02-26 01:04:24Z

Join the two tables together on the group and on the role, if it is not null, or ignoring the role otherwise:

CREATE VIEW users_groups (username, grp, role) AS
SELECT u.username,
       u.grp,
       g.role
FROM   users u
       INNER JOIN groups g
       ON (u.grp = g.grp
           AND (u.role = g.role OR u.role IS NULL)
          )

Which, for the sample data:

CREATE TABLE users (username, grp, role) AS
SELECT 'example1', 'ABC', 100  FROM DUAL UNION ALL
SELECT 'example1', 'XYZ', 200  FROM DUAL UNION ALL
SELECT 'example2', 'ABC', NULL FROM DUAL;

CREATE TABLE groups (grp, role) AS
SELECT 'ABC', 100 FROM DUAL UNION ALL
SELECT 'ABC', 150 FROM DUAL UNION ALL
SELECT 'ABC', 200 FROM DUAL UNION ALL
SELECT 'ABC', 250 FROM DUAL UNION ALL
SELECT 'ABC', 300 FROM DUAL UNION ALL
SELECT 'XYZ', 200 FROM DUAL UNION ALL
SELECT 'XYZ', 400 FROM DUAL;

Then SELECTing from the view outputs:

USERNAME	GRP	ROLE
example1	ABC	100
example2	ABC	100
example2	ABC	150
example2	ABC	200
example2	ABC	250
example2	ABC	300
example1	XYZ	200

fiddle

Collectives™ on Stack Overflow

Is there a way I can expand a query if one value is null in Oracle SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related