Solved

Materalized Query Table and {CURRENT DATE}

Posted on 2008-10-20
3
602 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:Kdo
Comment Utility
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
Comment Utility
@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:
Kdo earned 500 total points
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now