Solved

Materalized Query Table and {CURRENT DATE}

Posted on 2008-10-20
3
645 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Automating Terraform w Jenkins & AWS CodeCommit

How to configure Jenkins and CodeCommit to allow users to easily create and destroy infrastructure using Terraform code.

Question has a verified solution.

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

Occasionally Windows/Microsoft Updates will fail to update. We have found a code that will delete all temporary files and re-register all dll's related to Windows/Microsoft Updates! This works 99% of the time to get the updates working again! The…
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.
Suggested Courses

627 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