RE: Creating a dynamic view in SQL

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

It would help if you told us the date format of your table column, but
judging from your substrings it looks like it's YYMMDD, right? It doesn't
matter I guess, as long as you are able to get the real DATE column out of
it, but perhaps we could have provided a different type of transform.

At a minimum, I recommend you use (CURRENT_DATE - 1 YEAR) date arithmetic
and then compare that against your table's date column.

As a general rule, avoid using || and spell out CONCAT instead in your date
transformation (national language gotchas).

Since you mentioned GINORMOUS number of records, I would actually strongly
encourage going the other way with your date transformation. Meaning, take
(CURRENT_DATE - 1 YEAR) and transform it into the numeric format of your
table column (i.e. YYMMDD). Then do the comparison (hopefully comparing it
numerically doesn't introduce an issue here...).  If there is no SQL index
or keyed LF over dt.orddte column, build one.  This approach ought to make
the view perform VERY well, as it could leverage an index in the access
plan.

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: 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