jeudi 21 avril 2016

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'.

Aucun commentaire:

Enregistrer un commentaire