Reputation: 1159
I have table Groups with 3 rows, ID
, GROUP_NAME
and PARENT_GROUP_ID
, the table itself presents a basic group-subgroup functionality. Example is: if my Group name is - "First Group" with ID = 1, and it has a subgroup named "Sub Group" with ID = 2, and PARENT_GROUP_ID = 1; this means that Sub Group is the subgroup of the group with ID = 1. My problem is that I have a procedure, which loads the data according to parameters(pn_parent_group_id
is a number)
CURSOR c1 IS
SELECT * FROM GROUPS WHERE PARENT_GROUP_ID = pn_parent_group_id;
This query works fine when I send any pn_parent_group_id
into the procedure, and it finds all the necessary subgroups, however when I send NULL to the pn_parent_group_id it works wrong, because it should return those without parent_group_id(meaning only parent groups). I know my explanation is lacking, but I hope you got the idea, any help would be appreciated!
Upvotes: 2
Views: 90
Reputation: 126
You may be interested in the SYS_OP_MAP_NONNULL
function, which allows the comparison NULL = NULL
to be TRUE
.
CURSOR c1 IS
SELECT * FROM GROUPS
WHERE SYS_OP_MAP_NONNULL(parent_group_id) = SYS_OP_MAP_NONNULL(pn_parent_group_id);
This function is not directly documented in Oracle documentation, but does appear in topic documentation - as it has been around for a long while, I think it unlikely that it will be removed. As always with these things, use at your own discretion.
Upvotes: 1
Reputation: 146349
Null is never equal to null. So you need to add an explicit test for null to your cursor.
CURSOR c1 IS
SELECT * FROM GROUPS
WHERE PARENT_GROUP_ID = pn_parent_group_id
or (pn_parent_group_id is null and parent_group_id is null);
Upvotes: 5