Midrange News for the IBM i Community

Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Indexed view question.....
has no ratings.
Published: 20 Sep 2017
Revised: 22 Sep 2017 - 2398 days ago
Last viewed on: 15 Apr 2024 (2121 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Indexed view question..... Published by: Chris Proctor on 20 Sep 2017 view comments(2)

Good morning, all. I had a co-worker from another team come to me with the request of creating an indexed view. The thing is, he wants this view to be a timestamp, which doesn't currently exist in the file. I do have a 6.0 digit field specified as the entry date in YYMMDD format, and a entry time as a 6.0 digit field in HHMMSS format. Is there a way to create a view, concatenating these two fields and converting it to a timestamp key for the view?

Any suggestions would be greatly appreciated!

Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Comment on: Indexed view question.....
Posted: 6 years 6 months 27 days 10 hours 58 minutes ago

Huh? That's hard to interpret, but I'll give it a try.

To create a TimeStamp, use TIMESTAMP_FORMAT(digits(dateField)||digits(TimeField), 'YYMMDDHHMISS')

If the fields are 6-digits w/zero decimals the above example will work fine.

You can modify the hours format code to be either HH12 or HH24 (default when using HH by itself is HH24).

That will give you the timestamp value.

I'm not sure what an "Index View" is however, since sadly, SQL Views do not support the ORDER BY clause.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Indexed view question.....
Posted: 6 years 6 months 25 days 17 hours 35 minutes ago

You can use TIMESTAMP(<date value>, <time value>), which could be TIMESTAMP(DATE(<value>), TIME(<value>)), and then you're back to using DIGITS() or CAST() to get your numeric fields into a format that DATE and TIME will interpret properly.

An "indexed view" is a SQL Server thing. They're a Microsoft extension, not ANSI. One of the things it lets you do is create an index on a computed column, such as this timestamp.

You can create a VIEW that has the calculated TIMESTAMP, but I think you'll just have to create an index on the separate date and time columns. But the query optimizer won't use that index if you try to do a WHERE on the calculated TIMESTAMP. Your queries will have to have WHERE expressions and ON conditions using the separate date and time fields.