Re: Creating a dynamic view in SQL

I am with Elvis, I just hadn't taken time to flip the operands around, so that your field is not involved in a function - that skews the processing toward table scans, as the function has to be run for every record.

So try this, instead -

where

date(orddte >= digits(substr(char(current date - 1 year, ISO), 3, 2) concat
substr(char(current date - 1 year, ISO), 6, 2) concat
substr(char(current date - 1 year, ISO), 9, 2))

I ran a SELECT using that expression, like below, and got the value 81819 - YYMMDD for August 18th a year ago.

select decimal(substr(char(current date - 1 year, ISO), 3, 2) concat
substr(char(current date - 1 year, ISO), 6, 2) concat
substr(char(current date - 1 year, ISO), 9, 2))
from sysibm/sysdummy1
Using ISO in there forces the date into ISO format YYYY-MM-DD and therefore gives you consistent positioning for the substrings.

Now this would be a nice user-defined function - to create the numeric form from a date - I don't know if Alan Campin has that UDF.

HTH
Vern

Elvis Budimlic wrote:

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