IF statement in where clause

[acesn8s]

Hi, I am trying to SELECT records from 2 tables. I want to get records WHERE TABLE2_TERM_DATE is NULL unless the TABLE2_CONTACT field is NOT NULL. If the TABLE2_CONTACT is NULL and TABLE2_TERM_DATE is Null then I don't want those records. Below is the code I have so far, any help would be appreciated.

SELECT *
FROM TABLE1, TABEL2
WHERE TABLE1_ID = TABLE2_ID
AND TABLE2_TERM_DATE is NULL and TABLE1_CHANGE_IND is NULL
AND TABLE1_ACTIVITY_DATE >= '01-MAY-05'
ORDER BY TABLE1_LAST_NAME, TABLE1_FIRST_NAME, TABLE1_MI, TABLE1_ID

 

[Golom]

SELECT * 

FROM TABLE1, TABLE2 

WHERE TABLE1.ID = TABLE2.ID 
  AND TABLE1.CHANGE_IND IS NULL
  AND TABLE1.ACTIVITY_DATE >= '01-MAY-05' 

  AND TABLE2.TERM_DATE IS NULL 

  AND NOT (TABLE2.CONTACT IS NULL AND TABLE2.TERM_DATE IS NULL)

ORDER BY TABLE1.LAST_NAME, TABLE1.FIRST_NAME, TABLE1.MI, TABLE1.ID

 

[acesn8s]

Thank you very much Golom. I was tring Case statements and that wasn't working out for me and I did not know that I could wright a statement like:
AND NOT (TABLE2.CONTACT IS NULL AND TABLE2.TERM_DATE IS NULL)

 

[Golom]

You can't wright* one ... but you can write one.


* Definition - a worker skilled in the manufacture especially of wooden objects

 

[acesn8s]

LOL! Thank you for that correction as well.