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'.
Aucun commentaire:
Enregistrer un commentaire