CODEWITHSUNDEEP
sqloracle-database
Bob
Bob

Reputation: 10815

If inside select clause

Assume I have following SQL select clause:

SELECT * FROM some_table_1 t1
  join some_table t2 on t2.some_id =  t1.id and
  t2.school_id = 56 and
  t1.is_valid = 1 and 
  t2.status in (15, 16, 17, 18);

But I have also to check, if t2.status = 18, then I have also to check if t2.date < 01.01.2015

How can I add this condition to this select?

Upvotes: 1

Views: 80

Answers (3)

Boneist
Boneist

Reputation: 23588

It sounds like you only want to join on rows that have a status of 18 when the date (which is a bad name for a column, since it's an Oracle reserved word. I'm also going to assume it's of DATE datatype) is less than 1st Jan 2015. If so, then the following should do the trick:

SELECT *
FROM   some_table_1 t1
       join some_table t2 on t2.some_id =  t1.id 
                             and t2.school_id = 56
                             and t1.is_valid = 1
                             and (t2.status in (15, 16, 17)
                                  or (t2.status = 18 and t2.date < to_date('01.01.2015', 'dd.mm.yyyy.')));

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 157116

Just add an or checking for 'not 18', or matching your second condition:

SELECT *
FROM   some_table_1 t1
join   some_table t2
on     t2.some_id = t1.id
and    t2.school_id = 56
and    t1.is_valid = 1
and    t2.status in (15, 16, 17, 18)
/* added part */
where  ( t2.status != 18
         or t2.date < to_date('01.01.2015', 'dd.MM.yyyy')
       )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You can add:

where t2.status <> 18 or t2.date < date '2015-01-01'

Upvotes: 0

Related Questions