Re: Creating a dynamic view in SQL

[Alan Shore <AlanShore@xxxxxxxx>]

Thanks for your reply Vern
I'll definitely look into your code, BUT wont I have the same problem - in
that the view wont be dynamic.
In other words, I will still have to drop and recreate the view each day?



Alan Shore
Programmer/Analyst, Distribution
E:AShore@xxxxxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 08/19/2009 09:11:31 AM:

> Alan
>
> I'm not sure where your <= 100 fits here, but here's what should work. I
> also don't think you need the double-barrel decimal thing. You gain
> nothing by converting 2 digits to a 3-digit packed, then taking the
> final 2 digits - they'll always be the same as you started with. And,
> since everything will be in this century, help SQL out by adding '20' in
> there. Assuming your numeric value is YYMMDD, try this. Adding the extra
> year digits means you don't have to worry about date format from your
job.
> where
>
> date('20' concat substr(digits(dt.orddte),1,2) concat '-'
>           concat substr(digits(dt.orddte),3,2) concat '-'
>           concat substr(digits(dt.orddte),5,2))
>      >= current date - 1 year
>
> HTH
> Vern
>
>
> Alan Shore 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
> > 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.