AW: Creating a dynamic view in SQL

Hi,

In either way I'd create several views:
1. View will contain all fields of the base table plus a real date field,
where you convert the numeric date into a real date.
BTW the scalar function DATE can convert a 14 character string in the
format YYYYMMDDHHMMSS (without separators) into a real date.

2. To create the next view, just use this view and select whatever records
you want, which may be much easier:

CREATE VIEW MySchema/MyFirstView
As (Select a.*,
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)) as MyDate
from MyBaseFile);

CREATE VIEW MySchema/MyNextView
As (Select *
From MyFirstView
Where MyDate >= Current_Date - 1 Year);

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Alan Shore
Gesendet: Wednesday, 19. August 2009 14:46
An: Midrange Systems Technical Discussion
Betreff: Creating a dynamic view in SQL


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