Re: Creating a dynamic view in SQL

[Jim Essinger <dilbernator@xxxxxxxxx>]

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.