Re: change sql statement and make it simple

From: Michael Bialik <michael_bialik_at_my-deja.com>
Date: 2000/07/10
Message-ID: <8kd9he$5gi$1@nnrp1.deja.com>#1/1

Hi.

Try following:

SELECT customer_id
FROM customer
WHERE contact_date >
TO_DATE(DECODE(SIGN(SYSDATE -TO_DATE('0107' || TO_CHAR

                    (SYSDATE,'YYYY'), 'DDMMYYYY')),
  1, '0107' || TO_CHAR(SYSDATE,'YYYY'), '0101' ||TO_CHAR
                     (SYSDATE,'YYYY')),'DDMMYYYY')

HTH. Michael

In article <Z4X95.11599$Tb7.78617_at_news-server.bigpond.net.au>, "Cobra__77" <Cobra__77_at_hotmail.com> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Jul 10 2000 - 00:00:00 CDT