Re: Creating a dynamic view in SQL

[Jim Essinger <dilbernator@xxxxxxxxx>]

That should read

where idate(mydate) > current_date - 365 days

or as Vern has so wisely posted  - 1 year

On Wed, Aug 19, 2009 at 7:15 AM, Jim Essinger <dilbernator@xxxxxxxxx> wrote:

> I don't know if this will work, but how about changing your file date to a
> date data type by your favorite method, mine is to us iDate function freely
> given by Alan Campin.
>
> Then you could do something like;
>
> where idate(mydate) > current_date - 365
>
> You could translate the date yourself, but why go to all that work when
> someone else has already done it for you?
>
> HTH
>
> Jim
>
>
> On Wed, Aug 19, 2009 at 6:45 AM, Alan Shore <AlanShore@xxxxxxxx> wrote:
>
> > Good morning all
> > hopefully I can explain this AND make sense
> > We have a master file that holds a date as a numeric field (really old
> > system that is behind the times)
> > I've been asked to create a view over the master file that will ONLY
> > contain records of 1 year or less
> > In other words, todays view will have 1 years worth of data
> > tomorrows view will again only contain 1 years worth of data. Some records
> > on todays view will NOT be on tomorrows view as they are older than 1 year
> > here is the where clause of the SQL
> >
> > where
> > current_date -
> > date(substr(digits(dt.orddte),1,2)||'/'||
> >     substr(digits(dt.orddte),3,2)||'/'||
> >     substr(digits(decimal(decimal(substr(
> >            digits(dt.orddte),5,2)),3,0)),2,2)) <= 100;
> >
> > If I run the SQL now, it will satisfy the one year situation, but that
> > same
> > view tomorrow will contain records older than one year, therefore I will
> > need to drop and re-create the view and as the master file contains a
> > GINORMOUS number of records, it can take a while to construct the view
> > Hence my question
> >
> > Hope this makes sense
> >
> > As always
> > ANY help is MUCH appreciated
> >
> >
> >
> > Alan Shore
> > Programmer/Analyst, Distribution
> > E:AShore@xxxxxxxxxxx <E%3AAShore@xxxxxxxxxxx>
> > P:(631) 200-5019
> > C:(631) 880-8640
> > "If you're going through Hell, keep going" - Winston Churchill
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/midrange-l.