Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> change sql statement and make it simple

change sql statement and make it simple

From: Cobra__77 <Cobra__77_at_hotmail.com>
Date: 2000/07/09
Message-ID: <Z4X95.11599$Tb7.78617@news-server.bigpond.net.au>#1/1

I have the following query....it checks which half of the year is last month..
and then if it is first half it returns 1-Jan-2000 and if it is second half 1-July-2000

is there a way to get rid of the in-line view and do it in one go...and not use a function on custmer contact_date too...

any ideas...
thanks

select customer_id
from customers a,

     (select trunc(add_months(sysdate,-1),'year') begin_date
      from dual
      where to_number(to_char(add_months(sysdate,-1),'mm')) between 1 and 6
      union
      select add_months(trunc(add_months(sysdate,-1),'year'),6) begin_date
      from dual
      where to_number(to_char(add_months(sysdate,-1),'mm')) between 7 and 12
      ) b

where a.contact_date > b.begin_date Received on Sun Jul 09 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US