Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Posts
    5

    Unanswered: Where clause problem in Oracle 8i - Can you "Where case when then..."

    Hi,

    Have searched hi and low for documentation on this matter.
    What is the correct syntax for pulling Oracle data using a
    where case when? The goal is to change the date range of a
    field based on the system date. If monday the range is
    sysdate-4 thru sysdate-3. If tuesday the range is sysdate-
    3 thru sysdate-1. If wednesday thru friday then range is
    sysdate-2 thru sysdate-1. Also, we're dealing with date
    and time cut-off points.


    The current code which gives a 'missing keyword' error:

    Where Case When to_char(sysdate,'d') = 2 then o.adddte
    between to_date
    ('trunc(sysdate-4) 15:00', 'dd/mm/yyyy hh24:mi')
    and to_date('trunc(sysdate-3) 14:59', 'dd/mm/yyyy
    hh24:mi') when to_char(sysdate,'d') = 3 then o.adddte
    between to_date('trunc(sysdate-3) 15:00', 'dd/mm/yyyy
    hh24:mi') and to_date('trunc(sysdate-1)
    14:59', 'dd/mm/yyyy hh24:mi') when to_char(sysdate,'d') IN
    (1, 4, 6, 5, 7) then o.adddte between to_date('trunc
    (sysdate-2) 15:00', 'dd/mm/yyyy hh24:mi') and to_date
    ('trunc(sysdate-1) 14:59', 'dd/mm/yyyy hh24:mi')
    ..

    Thanks in advance,

    Hank

    .

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Where clause problem in Oracle 8i - Can you "Where case when then..."

    There is more than one problem with your query. To begin with the CASE expression - the key point is that CASE is an expression, not a statement - it has to return a value. Your CASE looks like this:

    Code:
    where 
    case
      When d = 2 then o.adddte between date1 and date2
      when d = 3 then o.adddte between date3 and date4
      when d IN (1, 4, 6, 5, 7) then o.adddte between date5 and date6
    end
    This is incorrect use of a CASE. In fact, you could be using OR:

    Code:
    where 
      (  ( d = 2 and o.adddte between date1 and date2 )
      or (d = 3 and o.adddte between date3 and date4)
      or (d IN (1, 4, 6, 5, 7) and o.adddte between date5 and date6)
      )
    To use CASE, your WHERE clause would have to be:

    Code:
    where o.adddte between
      case
        When d = 2 then date1
        when d = 3 then date3
        else date5
      end
    and 
      case
        When d = 2 then date2
        when d = 3 then date4
        else date6
      end
    The other problem is the invalid expressions in your SQL like this one:

    to_date ('trunc(sysdate-4) 15:00', 'dd/mm/yyyy hh24:mi')

    This should be:

    to_date(to_char(trunc(sysdate-4),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi')

    or more simply:

    trunc(sysdate-4)+15/24

  3. #3
    Join Date
    Feb 2003
    Posts
    5

    Re: Where clause problem in Oracle 8i - Can you "Where case when then..."

    Thanks for your help. I gained a better understanding of this tricky situation. The entire query is included below and still errors 'Missing Keyword'. I'm used to access and vba not Oracle. Thanks andrewst
    Updated using your suggestion:

    [select distinct o.ordnum, o.cponum, SUBSTR(cponum, 1,4) Location_ID, o.cpotyp, o.vc_oracle_ordnum, o.adddte, s.shpsts, s.stgdte, s.carcod, s.srvlvl, ol.prtnum, ol.ordqty, ol.shpqty, (case when to_char(sysdate,'d') = 2 then trunc(sysdate-3) when to_char(sysdate,'d') = 3 then trunc(sysdate-1) when to_char(sysdate,'d') = 4 then trunc(sysdate-1) when to_char(sysdate,'d') = 5 then trunc(sysdate-1) when to_char(sysdate,'d') = 6 then trunc(sysdate-1) end) Target_Date
    from ord o, shipment s, pckwrk p, ord_line ol
    where o.adddte between
    case
    When to_char(sysdate,'d') = 2 then o.adddte between to_date(to_char(trunc(sysdate-4),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi')
    When to_char(sysdate,'d') = 3 then to_date(to_char(trunc(sysdate-3),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi')
    Else to_date(to_char(trunc(sysdate-2),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi')
    end
    and
    case
    When to_char(sysdate,'d') = 2 then to_date(to_char(trunc(sysdate-3),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi')
    When to_char(sysdate,'d') = 3 then to_date(to_char(trunc(sysdate-1),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi')
    Else to_date(to_char(trunc(sysdate-1),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi')
    end
    and o.ordnum = s.ship_id and ol.ordnum = o.ordnum and p.ordnum = o.ordnum
    group by o.ordnum, o.cponum, o.vc_oracle_ordnum, o.adddte, s.shpsts, s.stgdte, s.carcod, s.srvlvl, ol.prtnum, ol.ordqty, ol.shpqty, o.cpotyp
    order by 1

  4. #4
    Join Date
    Feb 2003
    Posts
    5

    Re: Where clause problem in Oracle 8i - Can you "Where case when then..."

    I had read this but didnt know how to phrase it in Oracle of which I have never programmed.

    The whole enchilada using 'OR':

    [select distinct o.ordnum, o.cponum, SUBSTR(cponum, 1,4) Location_ID, o.cpotyp, o.vc_oracle_ordnum, o.adddte, s.shpsts, s.stgdte, s.carcod, s.srvlvl, ol.prtnum, ol.ordqty, ol.shpqty, (case when to_char(sysdate,'d') = 2 then trunc(sysdate-3) when to_char(sysdate,'d') = 3 then trunc(sysdate-1) when to_char(sysdate,'d') = 4 then trunc(sysdate-1) when to_char(sysdate,'d') = 5 then trunc(sysdate-1) when to_char(sysdate,'d') = 6 then trunc(sysdate-1) end) Target_Date
    from ord o, shipment s, pckwrk p, ord_line ol
    where
    ( ( to_char(sysdate,'d') = 2 and o.adddte between to_date(to_char(trunc(sysdate-4),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi') and to_date(to_char(trunc(sysdate-3),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi') )
    or (to_char(sysdate,'d') = 3 and to_date(to_char(trunc(sysdate-3),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi') and to_date(to_char(trunc(sysdate-1),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi'))
    or (to_char(sysdate,'d') IN (1, 4, 6, 5, 7) and to_date(to_char(trunc(sysdate-2),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi') and to_date(to_char(trunc(sysdate-1),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi')) )
    and o.ordnum = s.ship_id and ol.ordnum = o.ordnum and p.ordnum = o.ordnum
    group by o.ordnum, o.cponum, o.vc_oracle_ordnum, o.adddte, s.shpsts, s.stgdte, s.carcod, s.srvlvl, ol.prtnum, ol.ordqty, ol.shpqty, o.cpotyp
    order by 1


    Originally posted by andrewst
    There is more than one problem with your query. To begin with the CASE expression - the key point is that CASE is an expression, not a statement - it has to return a value. Your CASE looks like this:

    Code:
    where 
    case
      When d = 2 then o.adddte between date1 and date2
      when d = 3 then o.adddte between date3 and date4
      when d IN (1, 4, 6, 5, 7) then o.adddte between date5 and date6
    end
    This is incorrect use of a CASE. In fact, you could be using OR:

    Code:
    where 
      (  ( d = 2 and o.adddte between date1 and date2 )
      or (d = 3 and o.adddte between date3 and date4)
      or (d IN (1, 4, 6, 5, 7) and o.adddte between date5 and date6)
      )
    To use CASE, your WHERE clause would have to be:

    Code:
    where o.adddte between
      case
        When d = 2 then date1
        when d = 3 then date3
        else date5
      end
    and 
      case
        When d = 2 then date2
        when d = 3 then date4
        else date6
      end
    The other problem is the invalid expressions in your SQL like this one:

    to_date ('trunc(sysdate-4) 15:00', 'dd/mm/yyyy hh24:mi')

    This should be:

    to_date(to_char(trunc(sysdate-4),'dd/mm/yyyy')||' 15:00', 'dd/mm/yyyy hh24:mi')

    or more simply:

    trunc(sysdate-4)+15/24

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Where clause problem in Oracle 8i - Can you "Where case when then..."

    You still have BETWEEN logic inside some of your CASE expressions.

  6. #6
    Join Date
    Feb 2003
    Posts
    5

    Re: Where clause problem in Oracle 8i - Can you "Where case when then..."

    Thanks again andrewst. A remnant from the last query snuck in there somehow. The code works great now! Thanks for helping me learn Oracle syntax. Debuging is more difficult on this platform than MS.

    Originally posted by andrewst
    You still have BETWEEN logic inside some of your CASE expressions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •