Re: change sql statement and make it simple
Date: 2000/07/09
Message-ID: <8kapl5$41h$1_at_nnrp2.deja.com>#1/1
Try this ...
This should eliminate your in-line view
select customer_id
from customers a
where a.contact_date > decode(sign(to_number
(to_char(add_months(sysdate,-1),'mm'))-7),'-
',trunc(add_months(sysdate,-1),'year'),add_months
(trunc(add_months(sysdate,-1),'year'),6));
Satyam Josyula
In article <R5X95.11601$Tb7.78696_at_newsserver.
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 Sun Jul 09 2000 - 00:00:00 CEST