RE: sql question
Date: Tue, 5 Jan 2010 17:30:49 -0500
Message-ID: <B61B5DD02B60453BA46522A57CEFCA21_at_rsiz.com>
I'm still confused. If you run a query on (yyyymmdd) 20100115, do you want the data from the entire month of 200912, or do you want 20091215 through 20100114, or exactly what? "The last month of data" is ill defined. What is in the column(s) you're running the filter against?
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Joan Hsieh
Sent: Tuesday, January 05, 2010 2:41 PM
To: oracle_l
Subject: Re: sql question
there is some confusion with the question. My question is how to always get the last month of the data? The job was set to run on 2010/1/15, then I need to get 2009/12 in the where clause.
Thanks again,
Joan
Joan Hsieh wrote:
> Hi,
>
> I am trying to modify a query with where clause like '&&year%%month%'to
> retrieve all the data by each month of the year. I have managed this
> query to set up automatically run on the 15th of the month without any
> problem until it turned the year of 2010.
>
> I used select substr(add_months(sysdate,-1),5,2) from dual to collect
> the month.
> and select substr(sysdate,1,4) from dual to get the year.
>
> The problem is I can't get the data on this month cause I will generate
> the date like '201012%'.
>
> Do you know how to handle this? Using if statement? if the month is 12,
> then the year should be -1? I am not sure how to accomplish this.
>
> Thanks,
>
> Joan
>
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Tue Jan 05 2010 - 16:30:49 CST