Pass null in where clause through Oracle command parameter

Question

Using below Oracle query to fetch data through oracle command parameter.

Select count(*) from tbl_name where col_name = :col_name

if i pass :col_name value as 'some value' its working and returning some count. if :col_name is null then it is not working. Because in where clause col_name = null is not correct. Instead we need to use col_name is null.

How to achieve this from c#, based on col_name value i need to use correct query.

col_name = 'some value' : Select count() from tbl_name where col_name = :col_name col_name = null : Select count() from tbl_name where col_name is null

How to achieve this in one single query. If col_name is null, need to use second where clause query, otherwise need to use first where clause query.

thanks in advance

All comparisons with NULL fail, in all databases. Even !=. That's part of SQL, the language. If you want to compare with null you have to use IS NULL or IS NOT NULL — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 12, 2020 at 6:02

Panagiotis Kanavos · Accepted Answer · 2020-10-12 06:14:15Z

You have to use IS NULL or IS NOT NULL in your query to select or exclude NULL values:

Select count(*) from tbl_name where col_name IS NULL

SQL, the language, and all databases that use it, use 3-valued logic. Any boolean expression returns True, False and NULL. Unlike programming languages, NULL isn't a specific value. NULL means the value is UNKNOWN. Perhaps because it's missing, or because there's no meaningful value to store.

If you compare an UNKNOWN value with another, the result can't be anything other than UNKNOWN. So if a value is NULL, the result is NULL too. If name is null, both name = NULL and name != NULL will return NULL, never True or False.

In filtering (ie WHERE, HAVING) and JOINs, NULLs are treated as False, so your query will never match NULL values. After all, you asked for all rows that match a specific condition and for some of them the answer was I don't know if this matches or not. Should these be included in the results? I don't know. The language can't guess, so it asks the programmer to explicitly state if unknown results should be included or not

Dipo · Accepted Answer · 2020-10-12 06:46:35Z

0

To achieve this on one line,

use tenary operations,

(***) ? **** : *****

answered Oct 12, 2020 at 6:46

Dipo

1429 bronze badges

1 Comment

Umapathy S Over a year ago

expecting like below Oracle query Select count(*) from Tbl_name where col_name :col_name From c# passing parameter value like if col_name is not null, then :col_name = 'some value' if col_name is null, then :col_name is 'IS NULL'

Collectives™ on Stack Overflow

Pass null in where clause through Oracle command parameter

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related