Indexed view question.....

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 - 2408 days ago
Last viewed on: 25 Apr 2024 (2144 views)

Main URL:

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!

Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)

Posted by: bobcozzi

Site Admin ****

Chicagoland

www.cozTools.com/qryfile

Comment on: Indexed view question.....
Posted: 6 years 7 months 6 days 11 hours 47 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 7 months 4 days 18 hours 24 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.