RE: Another SQL episode - VIEW formats

["Wilt, Charles" <WiltC@xxxxxxxxxx>]

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.