Re: Creating a dynamic view in SQL

Thanks for your reply Vern
and also to everyone else who was gracious to reply
You are right. It is rerun every time the view is opened.
I took Birgitta's suggestion of a view over a view, which seems to move the
fastest

Again
thanks to all


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 12:41:45 PM:

Alan

No problem - a view is never static - it is just an SQL SELECT statement
encapsulated in an LF - it is rerun every time you open it.

Vern

Alan Shore wrote:

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.

--
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.