Re: Creating a dynamic view in SQL

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.