× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. September 2007

RE: Another SQL episode - VIEW formats

Try this,

Use the "Retrieve SQL source" functionality in iNav on both the table and the view.

The retrieved source will explicitly list options that were implied when you actually created the
objects.

See if anything jumps out at you.

Otherwise, open a PMR with IBM.

Charles


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Tuesday, September 18, 2007 12:41 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Another SQL episode - VIEW formats

Same fields (see the response to Rob, I did SELECT *).


The format level ID on the TABLE is:

Record Format List
Record Format Level
Format Fields Length Identifier
SHRECORD 33 85 31FEBF6E48B30


The format level ID on the VIEWs are:

Record Format List
Record Format Level
Format Fields Length Identifier
SHRECORD 33 85 34414193CBB64


--
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.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Wilt, Charles
Sent: Tuesday, September 18, 2007 12:28 PM
To: Midrange Systems Technical Discussion
Subject: RE: Another SQL episode - VIEW formats

Well are you just lucky to be on v5r4.... <grin>

So what is different?



Charles


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Tuesday, September 18, 2007 12:23 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: Another SQL episode - VIEW formats

Actually, the RCDFMT name is the same, because I stated so
explicitly
in the CREATE VIEW.

--
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.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Wilt, Charles
Sent: Tuesday, September 18, 2007 11:56 AM
To: Midrange Systems Technical Discussion
Subject: RE: Another SQL episode - VIEW formats

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.

--
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 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.

--
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 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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.