John Bench John Bench - 7 months ago 7
SQL Question

IF statement in WHERE clause with Oracle SQL on count

I am trying to develop a PUT method for a website. I am using the following code to make sure the information the user is entering is different from what we already have before making changes to the database (also to prevent a whole bunch of log files in case the user hits submit too many times):

SELECT COUNT(*) AS count
FROM iam.credential
WHERE iam.credential.CREDENTIAL_TYPE = :1
AND iam.credential.CREDENTIAL_NAME = :2
AND iam.credential.LOST_OR_STOLEN = :3
AND iam.credential.STATUS = :4
AND iam.credential.EXPIRATION_DATE = :5
AND iam.credential.ISSUING_LOCATION = :6
AND iam.credential.PHYSICAL_FORM = :7
AND iam.credential.ASSOCIATED_DEVICE = :8
AND iam.credential.DISPLAY_NAME = :9;


I am grabbing the values from my webpage, but I am running into issues when the value is NULL. I want to be able to do something like the following:

SELECT COUNT(*) AS count
FROM iam.credential
WHERE
IF iam.credential.CREDENTIAL_TYPE is not null THEN
iam.credential.CREDENTIAL_TYPE = :1
ELSE
iam.credential.CREDENTIAL_TYPE is null
END IF
AND
IF iam.credential.CREDENTIAL_NAME is not null THEN
iam.credential.CREDENTIAL_NAME = :2
ELSE
iam.credential.CREDENTIAL_NAME is null
END IF
//and so on


I can't use

SELECT COUNT(*) AS count
FROM iam.credential
WHERE (iam.credential.CREDENTIAL_TYPE = :1
OR iam.credential.CREDENTIAL_TYPE is null)


because that will return a count of 2 when I only want the one that matches what the user input.

Basically I want the count to either return a 1 or a 0 for if the record exists or if it doesn't.

I want the WHERE clause to be dynamically changed based on what the user provides.

If the user doesn't provide a value because it is not required it will be null. I need query to change to

credential_name is null


because

credential_name = null


doesn't work in oracle.

if it is not null then I need it to be

credential_name = :1


and it will be filled with the value that the user provided.

credential_name is :1


doesn't work in oracle either.

I will get two records back if I have two credentials of the same type but one record has a (null) value for CREDENTIAL_NAME and the other has 'DaisyDuck'.

Answer

You may just want to check that both the column value and the supplied value are null, or the column and the supplied value match:

SELECT COUNT(*) AS count
FROM iam.credential
WHERE (iam.credential.CREDENTIAL_TYPE = :1
OR (iam.credential.CREDENTIAL_TYPE is null AND :1 is null))
AND (iam.credential.CREDENTIAL_NAME = :2
OR (iam.credential.CREDENTIAL_NAME is null AND :2 is null))
AND ...

You may not have to do that for every value if you have table columns that are not nullable and you're checking that the matching user-supplied value is not null before you reach this point.

Your approach has a potential flaw though. In a multi-user system two sessions could enter the same values, both check and get a count of zero, and both then insert - causing a duplicate row unless you also have a unique constraint across all the values. If you do have such a constraint then the count will prevent a violation on insert some of the time, but not always, so may not be worth the extra trip to the database.

Comments