RE: sql question

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <joan.hsieh@xxxxxxxxx>, "'oracle_l'" <ORACLE-L@xxxxxxxxxxxxx>
  • Date: Tue, 5 Jan 2010 17:30:49 -0500

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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 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



Other related posts: