SQL query and if statement

Lee R Liddick Jr
Lee R Liddick Jr used Ask the Experts™
on
In my where statement I need to check two areas, if the first isn't populated, check the other.

if a.C_EFF_DT is NOT NULL then a.C_EFF_DT < date(current date - 6 months)
if a.C_EFF_DT is NULL then b.C_EFF_DT < date(current date - 6 months)

So basically if the first C_EFF_DT is null then check the second C_EFF_DT, if it has a date, then check it and don't worry about the second date.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
<cfif a.C_EFF_DT is NOT NULL>
 a.C_EFF_DT < date(current date - 6 months)
<cfelse>
 a.C_EFF_DT is NULL then b.C_EFF_DT < date(current date - 6 months)
</cfif>
case
  when a.C_EFF_DT is NOT NULL then a.C_EFF_DT < date(current date - 6 months)
  when a.C_EFF_DT is NULL then b.C_EFF_DT < date(current date - 6 months)
  else (a date if the above two are false)
end
Lee R Liddick JrReporting Analyst

Author

Commented:
I didn't want this in CF...I am doing this in SAS SQL.  The case end in the where statement didn't work.  It doesn't like the less than sign...the query gives an error in the log.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Lee R Liddick JrReporting Analyst
Commented:
I figured it out myself, not even using 'if' or 'case' statements:
(
     a.c_eff_dt is NOT NULL
          AND a.c_eff_dt < date(current date - 6 months)
OR
     a.c_eff_dt is NULL
          AND b.c_eff_dt < date(current date - 6 months)
)
Lee R Liddick JrReporting Analyst

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for leerljr68's comment #a39901107

for the following reason:

Accepting own comment since I figured it out myself with no assistance from the other expert comments.  Thank you.
Most Valuable Expert 2015
Commented:
FYI, always wrap statements that mix AND/OR in parenthesis. Otherwise you're leaving the order of evaluation up to the db, which may or may not be what you want:

(
     <!--- first condition --->
     ( a.c_eff_dt is NOT NULL AND a.c_eff_dt < date(current date - 6 months)  )
OR
     ( a.c_eff_dt is NULL AND b.c_eff_dt < date(current date - 6 months)  )
)
Lee R Liddick JrReporting Analyst

Author

Commented:
I'm awarding points to those who responded.
Lee R Liddick JrReporting Analyst

Author

Commented:
Even though I answered my own question, I'm awarding points for taking the time to respond.  Thank you all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial