CODEWITHSUNDEEP
mysql
Roy
Roy

Reputation: 1225

COALESCE() to get Not null as well as NULL Values

I have my Mysql Query as :

select RecordID,ID,Name,Description,RecordDateTimeUTC,Location from
eemployee where RecordID like ? and(isNull(RecordDateTimeUTC) OR RecordDateTimeUTC=  CASE  WHEN COALESCE(?,'') = ''  THEN RecordDateTimeUTC  ELSE ?  END)........

Now my query There are lott of fields(? --> this is where i am putting my data for filter perpose) upon which user can query so i am using coalesce but the disadvantage of using coalesce is it doesn't give me null values when i want to get all data present in employee table , so to get null values i have used isNull function.

Now i am testing the above query for two use cases

1-->User can pass percentage in RecordID:-In this case he should get all data either null as well as not null values :

    select RecordID,ID,Name,Description,RecordDateTimeUTC,Location from
        eemployee where RecordID like '%' 
and(isNull(RecordDateTimeUTC) OR RecordDateTimeUTC=  CASE  WHEN COALESCE('','') = ''  THEN RecordDateTimeUTC  ELSE ''  END)

2-->User can filter data based on RecordDateTimeUTC:- If user passes this parameter then he should get all not null data satisfying the above filteration.:-

select RecordID,ID,Name,Description,RecordDateTimeUTC,Location from
        eemployee where RecordID like '%' and(isNull(RecordDateTimeUTC) OR RecordDateTimeUTC=  CASE  WHEN COALESCE('2012-07-09 11:11:00','') = ''  THEN RecordDateTimeUTC  ELSE '2012-07-09 11:11:00'  END)

For the first use case it is working fine but for the second use case it is giving me filtered data as well as null Data

So what should be my query so that both my use cases are supported by the above single query. I am using MYSQl Query Browser to test my query

Thanks in advance

Upvotes: 1

Views: 400

Answers (1)

lc.
lc.

Reputation: 116538

It looks like you're trying to make parameters optional while using a single static SQL statement. In that case I would imagine you mean something like:

SELECT RecordID,ID,Name,Description,RecordDateTimeUTC,Location 
FROM eemployee 
WHERE (@RecordId IS NULL OR RecordID LIKE @RecordId)
AND (@RecordDateTimeUTC IS NULL OR RecordDateTimeUTC = @RecordDateTimeUTC)

If the parameter value is NULL, it will be omitted completely from the filter, otherwise the passed value will be used.

Note you can't use ? here because of the need to re-use the parameter - unless you want to specify each parameter twice, that is. Specifying a name makes the query a lot more readable IMHO.

Upvotes: 2

Related Questions