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