CODEWITHSUNDEEP
c#sqlwhere-clause
nermik
nermik

Reputation: 1505

SQL Where Clause condition

In sql where clause; if pv.SalePrice is null, I want to use pv.Price. How can I do this?

WHERE    
    @FilterRangePriceValueMin < pv.SalePrice OR pv.SalePrice is null
     AND (@FilterRangePriceValueMax > pv.SalePrice OR pv.SalePrice is null)

Upvotes: 3

Views: 564

Answers (7)

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

Another approach using IFNULL---------> MYSQL

WHERE
    IFNULL(pv.SalePrice, pv.Price)
    BETWEEN @FilterRangePriceValueMin AND @FilterRangePriceValueMax

NVL ---------------------------> ORACLE

WHERE
    NVL(pv.SalePrice, pv.Price)
    BETWEEN @FilterRangePriceValueMin AND @FilterRangePriceValueMax

ISNULL ---------------------------> SQL SERVER

WHERE
    ISNULL(pv.SalePrice, pv.Price)
    BETWEEN @FilterRangePriceValueMin AND @FilterRangePriceValueMax

Upvotes: 0

prog2011
prog2011

Reputation: 61

If i understand you, I don't try but may be it can be useful for you.

(pv.SalePrice is not null and  (@FilterRangePriceValueMin < pv.SalePrice   AND @FilterRangePriceValueMax > pv.SalePrice ))
or 
(pv.SalePrice is null and  (@FilterRangePriceValueMin < pv.Price AND (@FilterRangePriceValueMax > pv.Price ) )

Upvotes: 0

KM.
KM.

Reputation: 103579

this may be a stretch, since prices are usually not indexed and are not usually good index candidates. However, if you do have a good usable index on pv.SalePrice and another good usable index on pv.Price and your table is large, a UNION on this will run much faster than a COALESCE:

SELECT
   ...
   FROM ...
   WHERE pv.SalePrice>=@FilterRangePriceValueMin 
     AND pv.SalePrice<=@FilterRangePriceValueMax
UNION
SELECT
   ...
   FROM ...
   WHERE pv.Price>=@FilterRangePriceValueMin
     AND pv.Price<=@FilterRangePriceValueMax

The idea is that two index queries will be faster than a complete table scan. Also, it is best to use UNION ALL if possible, but I can't tell (from limited info in question) if you'll get duplicates or not.

Upvotes: 1

WizardDBA
WizardDBA

Reputation: 46

I suggest using the case clause.

CASE WHEN pv.SalePrice IS NULL THEN pv.SalePrice ELSE ' '

Upvotes: 0

Soner G&#246;n&#252;l
Soner G&#246;n&#252;l

Reputation: 98740

Use a CASE statement

@FilterRangePriceValueMin < (CASE WHEN pv.SalePrice IS NULL THEN pv.Price ELSE pv.SalePrice END) OR pv.SalePrice is null
AND (@FilterRangePriceValueMax > (CASE WHEN pv.SalePrice IS NULL THEN pv.Price ELSE pv.SalePrice END) OR pv.SalePrice is null)

Or you can use COALESCE

Returns the first nonnull expression among its arguments.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460038

You can use COALESCE which returns the first nonnull expression in its arguments.

WHERE    
   @FilterRangePriceValueMin < COALESCE(pv.SalePrice, pv.Price) 
   AND @FilterRangePriceValueMax > COALESCE(pv.SalePrice, pv.Price)

Upvotes: 3

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

You can use the COALESCE function to try items for NULL in order, and then take the first non-null one for comparison. You can also use BETWEEN to avoid writing out the call twice:

WHERE
    COALESCE(pv.SalePrice, pv.Price)
    BETWEEN @FilterRangePriceValueMin AND @FilterRangePriceValueMax

Upvotes: 11

Related Questions