RE: Another SQL episode - VIEW formats -- MIDRANGE-L

Jeff,

If you look at the output of the DSPFD command, you'll see why.

The record format name is different for the view.

Honestly, I've never used views from RPG. Since they aren't keyed, the only way to read the data is
sequentially, thus not of much used when you're using HLL native I/O.

Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Tuesday, September 18, 2007 11: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.

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.