RE: SQL View

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Damn, that's a thought I never had Eric.

Therefore I could create a view with certain fields, and with derived
fields like UDF's.  Then I could create multiple indexes to sort by
various methods.  And the optimizer would select the best index.

Now, if they could fix the RPG compiler so that this was supported by
native file I/O.  And the optimizer could pick the best index.

Could you imagine changing your BPCS programs to use the IIM file instead
of IIML02?  And the fact that you accessed the file with a key it would
figure out which index to use at run time?
Could you imagine only opening the IIM file and, using the free format
%chain to chain by item number once, now let's free format setll/read by
item description to fill a subfile?

That would probably entail instead of IIML01 (Select only active records,
sorted by item number) and a IIML02 (select all records sorted by item
number) a
IIMI01 (Sort by item number), IIMV01(Select only active records), and who
would need IIML02?  If you wanted to access all records sorted by item
number you would just access IIM and the optimizer would figure out to use
the IIMI01 index.  Although I suspect some DB guru might suggest building
a IIMI02 index that sorts by active record code and then item number.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




"DeLong, Eric" <EDeLong@Sallybeauty.com>
Sent by: midrange-l-admin@midrange.com
12/13/2002 03:26 PM
Please respond to midrange-l

        To:     "'midrange-l@midrange.com'" <midrange-l@midrange.com>
        cc:
        Fax to:
        Subject:        RE: SQL View


Uh..... no.

SQL by definition separates the view from the index.  The combination of
the
two in a single object exists only on OS400.  That's a logical file.  In
SQL
world, combining the two would limit capability.  Separation allows any
view
to be accessed by any index.

Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Smith, Dave [mailto:DSmith@tshsc.com]
Sent: Friday, December 13, 2002 2:08 PM
To: midrange-l@midrange.com
Subject: SQL View


Will our mighty iseries ever allow us to Create a SQL View with an Order
By
clause?  Anybody have any info on this? IBM?

I did some searching on this and did not find much other than a design
change request by Rob Berendt about a year ago and the response from IBM:
"IBM agrees with the request and a solution appears to be a desirable
objective. A solution however may not presently appear feasible or
implementable. No IBM commitment is made or implied as to the eventual
delivery of an acceptable solution."

David Smith
Tri-State Hospital Supply Corp.
IT Consultant
dsmith@tshsc.com
517.546.5400
_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
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@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.