RE: Derived Index V6R1

Michael,

Why all the gyrations? Why not just create a date data type in the index in the format you want? This is easy for me to say as we are still on V5R4 so I don't get to use derived indexes yet. Only one or two more weeks though.

Given that you could create your derived index as a date data type then your select would simply need a date data type or date formatted constant for comparison.

Rick

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
bounces@xxxxxxxxxxxx] On Behalf Of Schutte, Michael D
Sent: Tuesday, August 30, 2011 8:26 AM
To: Midrange Systems Technical Discussion
Subject: Derived Index V6R1

Looking for a best solution for implementing and using derived index. I have
a table that has a date in it MMDDYYYY format. It's a large historical file that
we often fight with getting data from. I would like to create this derived
index with the date as YYYYMMDD...

create index mylib/r06_derived on mylib/sales ( company,
dec(substr(digits(dcodte),5,4) concat substr(digits(dcodte),3,2) concat
substr(digits(dcodte),1,2), 8, 0) as dtyyymmdd
)

This creates just fine. My question is how to use it.

Personally I would hate to duplicate "company = ? and
dec(substr(digits(dcodte),5,4) concat substr(digits(dcodte),3,2) concat
substr(digits(dcodte),1,2), 8, 0) between ? and ?" in every SQL statement I
write to use the derived index.

I could create a view over the physical file that does cast dcodte to the
format I want, then I can select from the view using the new field. Ideally, I
would be able to use a UDF (not supported). However, I saw something
about UDT (user defined types). Is this something I can take advantage of?


Join BE Mail now and receive a grocery coupon for a Buy One, Get One Free
Side Dish in your Inbox! You can also look forward to exclusive monthly offers
from Bob Evans as well as insider news about our restaurants, grocery food
products, recipes, chefs and local community activities. For more
information, visit
www.bobevans.com/bemail<http://www.bobevans.com/bemail>

________________________________
Notice from Bob Evans Farms, Inc: This e-mail message, including any
attachments, may contain confidential information that is intended only for
the person or entity to which it is addressed. Any unauthorized review, use,
disclosure or distribution is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply e-mail and destroy all copies of
the original message and any attachments.
--
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.

Privileged and Confidential. This e-mail, and any attachments there to, is intended only for use by the addressee(s) named herein and may contain privileged or confidential information. If you have received this e-mail in error, please notify me immediately by a return e-mail and delete this e-mail. You are hereby notified that any dissemination, distribution or copying of this e-mail and/or any attachments thereto, is strictly prohibited.