Jeff,
I am a bit behind you on the SQL road, but I wouldn't expect views to have
the same ID as the physical. The main reason is that with a view you can do
so much more than "select * from file".
On the other hand, did you declare the view or the table in your RPGLE
program? We use views all the time like this and have no problems. However,
we don't override to other views.
Mike Krebs
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Tuesday, September 18, 2007 10:07 AM
To: 'Midrange Systems Technical Discussion'
Subject: Another SQL episode - VIEW formats
I took our DDS defined sales history file ("SLSHST") and all it's logicals,
and am redoing it in SQL DDL ("SLSALL"). By giving it a different name and
populating both files for now, I can convert programs individually until
finished.
Umpteen years ago, when performance (and disk space) was an issue, I made
SLSHST multimember. Each month was in a different member named SLS200701,
SLS200702, etc. This worked great for many reasons, including user queries.
Users typically wanted in a single month. When they _were_ interested in
more than a month, they queried over one of the logicals, as I built all
logicals over all members.
As you know, multimember is a no-no in SQL. So I put everything in a single
member in the TABLE SLSALL. Then, thinking I was being smart, I created a
VIEW for each month (we keep 24 months detail on the system) called
SLS200701, SLS200702, etc. In this way the users can still get individual
months without having to select by date range each and every time. All is
well.
Getting even smarter, I decided "Hey - for all those monthly reports and
summaries and whatnots I generate for a single month, why don't I just do an
OVRDBF to the view for the month I want and voila! After all, the view is
already there, use it." By this time my arm is hurting from trying to pat
myself on the back so much.
Pride goeth before a fall. Lo and behold the format level Id on the VIEWs
are different than the format level Id on the TABLE. (Although the format
level ID is the same for each VIEW.) I looked at the INDEXes I built over
the TABLE and they have the same format level ID as the TABLE. To make it
work I have to specify LVLCHK(*NO). I hate LVLCHK(*NO) in production.
Is this not a good use for VIEWs? Is there a smarter way to do this? OTOH,
would you expect a VIEW to have the same format level ID as the TABLE? I
_do_ have an INDEX built by date. The idea of the VIEW was not to have to
select by date range over and over and over.
About the time I really think I'm getting SQL, something happens to show me
I'm not yet really 'thinking' in SQL.
Thanks for any insight.
--
Jeff Crosby
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
The opinions expressed are my own and not necessarily the opinion of my
company. Unless I say so.
--
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.
midrange.com
