CODEWITHSUNDEEP
sqloracle-databasewhere-clause
Hatik
Hatik

Reputation: 1159

SQL Query in Oracle where clause

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

Answers (2)

dipdapdop
dipdapdop

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

APC
APC

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

Related Questions