Solved

Materalized Query Table and {CURRENT DATE}

Posted on 2008-10-20
3
617 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
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:
Kdo 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction Often we come across situations wherein our batch files would be needing to reboot Windows for a variety of reasons. A few of them would be like: (1) Setup files have been updated whose changes can take effect only after a reboot …
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
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.

863 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

23 Experts available now in Live!

Get 1:1 Help Now