Comparison in the WHERE condition

Question

Post reply

Comparison in the WHERE condition

christi1711

SSC-Addicted

Points: 441
More actions
April 9, 2022 at 3:20 pm

#4013194

Hi all,
I would like to add some condition to the query but not for how it will be correct.
For example, my query:
SELECT Product_Id, Product_Name, Created_Date
FROM Product
I want to add condition: if product_id are 1 and 2 them created_date < 20220301, if not - any filtering.
Could you please help me?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246987 More actions · Answer 1

WHERE (ProductId in (1,2) AND Created_Date < '20220301') OR ProductId NOT IN (1,2)

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 2

Lets break this down into sets.

You are combining two sets of data from this table:

All Product rows where [product_id] > 2
All Product rows where [product_id] is 1 or 2 and created_date < 20220301

There's an intersection: Regardless of product_id, all rows with created_date < 20220301 are to be selected.

SELECT ...
  FROM Product
 WHERE Product_Id > 2 OR create_date < '2022-03-01'

Eddie Wuerch
MCM: SQL

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

The code is simple enough so why not do it in a UNION ALL between two queries to get rid of the "OR"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

Eddie Wuerch wrote:

Lets break this down into sets.
You are combining two sets of data from this table:
All Product rows where [product_id] > 2
All Product rows where [product_id] is 1 or 2 and created_date < 20220301
There's an intersection: Regardless of product_id, all rows with created_date < 20220301 are to be selected.
SELECT ...
  FROM Product
 WHERE Product_Id > 2 OR create_date < '2022-03-01'

~~What happens if the ProductID is (for example) 10 and the date is also less than '2022-03-01'?~~

Heh... never mind. I see it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)