Oracle SQL: Using Variables in If Then Statement

Question

I'm having trouble getting an if then statement to work in the following query:

define start_date = '&START_DATE'
define end_date   = '&END_DATE'
define co_cd      = '&COMPANY_CODE'
define myacct     = '&4_DIGIT_ACCOUNT'

select ivc.pmt_dt, ivc.ivc_cd, gl.seq#, gl.gl_acct_cd, ivc.ve_cd, ve.ve_name, gl.amt
from ivc ivc
  left outer join ve ve
    on ivc.ve_cd = ve.ve_cd
  left outer join ivc$gl_acct gl
    on (ivc.ivc_cd = gl.ivc_cd and ivc.ve_cd = gl.ve_cd)
where ivc.post_dt >= '&start_date'
  and ivc.post_dt <= '&end_date'
  and substr(gl_acct_cd,1,4) = '&myacct'
  and if '&co_cd' = 'BSD' then
        substr(gl_acct_cd,6,2) in ('04','08','11','31','37')
      elseif '&co_cd' = 'JEYE' then
        substr(gl_acct_cd,6,2) in ('03','20','21','22','23','24')
      elseif '&co_cd' = 'BSF' then
        --something
      elseif '&co_cd' = 'ALL' then
        --something
      else
        substr(gl_acct_cd,6,2) in '&co_cd'
      end if
order by gl.amt desc

What am I doing wrong on the If Then statement? I keep getting an invalid relational operator error. I've searched quite a bit online but cannot find an answer. Any help is appreciated. I know enough to be dangerous with this stuff...do quite a bit of sql queries but they typically don't get too complicated. Thanks!

IF THEN END IF cannot be used in a SQL statement, only in PL/SQL. CASE WHEN ENDon the other hand, can. — Sebas
– Sebas, Commented May 20, 2013 at 17:52
Additionally, PL/SQL's "elseif" is actually spelled "ELSIF" without an extra "e". See docs. — ean5533
– ean5533, Commented May 20, 2013 at 17:55

David Aldridge · Accepted Answer · 2013-05-20 17:55:58Z

3

With SQL being set-oriented, there isn't really a procedural method like IF.

Instead, structure the query like this:

and (
  ('&co_cd' = 'BSD'  and substr(gl_acct_cd,6,2) in ('04','08','11','31','37')) or
  ('&co_cd' = 'JEYE' and substr(gl_acct_cd,6,2) in ('03','20','21','22','23','24')) or
  ('&co_cd' = 'BSF'  and --something) or
  ('&co_cd' = 'ALL'  and --something) or
  ('&co_cd' not in ('BSD','JEYE','BSF','ALL') and substr(gl_acct_cd,6,2) in '&co_cd'))

answered May 20, 2013 at 17:55

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ean5533 · Accepted Answer · 2013-05-20 17:58:24Z

2

If then else will not work in SQL statements you should use CASE WHEN but in your case, case when is not good option try this

and (
      ('&co_cd' = 'BSD' and substr(gl_acct_cd,6,2) in ('04','08','11','31','37'))
      OR 
      ('&co_cd' = 'JEYE' and substr(gl_acct_cd,6,2) in ('03','20','21','22','23','24'))
      OR 
      ('&co_cd' = 'BSF' and --something)
      OR 
      ('&co_cd' = 'ALL' and --something)
      OR
      ('&co_cd' NOT IN ('BSD','JEYE','BSF', 'ALL') and substr(gl_acct_cd,6,2) in '&co_cd')
     )

edited May 20, 2013 at 17:58

ean5533

9,0143 gold badges42 silver badges65 bronze badges

answered May 20, 2013 at 17:56

rs.

27.6k13 gold badges71 silver badges93 bronze badges

1 Comment

Richard Over a year ago

Awesome! Exactly what I needed and super fast response! Thank you!

Collectives™ on Stack Overflow

Oracle SQL: Using Variables in If Then Statement

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related