Solved

Materalized Query Table and {CURRENT DATE}

Posted on 2008-10-20
3
629 Views
Last Modified: 2013-12-06
I'm working in SQL for AS400 iSeries V5R3 .

So, the only kind of Materialized Query Table I can build is one "MAINTAINED BY USER".
Meaning I must manually rebuild my table ...
So I have a scheduled job which starts at 6am and runs every 90 minutes until 6pm.

ONLY PROBLEM IS, I need the MQT built using TODAY's date as a logical threshhold ...
BUT ... MQT's cannot contain SPECIAL REGISTER values ... so CURRENT DATE is not allowed!

Any TRICKS on how I can build (and re-build) an MQT using TODAY's CURRENT DATE as part of the logic criteria?

0
Comment
Question by:volking
  • 2
3 Comments
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 22760180
How about inserting the value of CURRENT DATE into a table and building the MQT based on that value?

0
 
LVL 5

Author Comment

by:volking
ID: 22760325
@Kent .... yep, I thunk that one up myself, too ... but I had hope someone had a better trick ... that might feel "LESS" like a kludge ... grin ... as a last resort, I'll do that
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 500 total points
ID: 22760744
It's not that much of a kludge if the script goes something like this:

INSERT INTO myparams (pname, pvalue) values ('CurrentDate', CURRENT DATE);
CREATE TABLE .....
...
WHERE date = date (select pvalue from myparms where pname = 'CurrentDate');

  :)


It will almost look like there has been forethough and flexibility built into your processes.  :) :) :)


Kent

Kent
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction How to create multiboot configuration with XP\Vista and Windows 7 on it? And most important question - how to do this correctly so not to have any kind of nightmares we get when system gets screwed? First of all one should realize t…
Sometimes a user will call me frantically, explaining that something has gone wrong and they have tried everything (read - they have messed it up more and now need someone to clean up) and it still does no good, can I help them?!  Usually the standa…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question