Projection with a field creates from another field

kannan_1

Hi, 

Is it possible to use CAST and CASE functions while creating a projection of a table ???

Daniel_Leybovic

Hi!

No. https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/AdministratorsGuide/ConfiguringTheDB/Phys...

Table is logical object, projection is physical object - column in projection is reference to table column, expression not allowed. Only PK/FK join are allowed.

PS
Hm...

CREATE PROJECTION
AS SELECT
    CASE WHEN (sysdate() - login_ts) % randomint(7) = 5
...

How to create projection for this? Encoding? Sorting?

kannan_1

Thanks Daniel.... So there is no way to create a projection with derived fields of a base table ??

The fields in projection should always be a direct set of base table fields ??

Daniel_Leybovic

Hi Kannan!

In projection no, but you can optimize a projection for calculation and create a VIEW. Also you can create a derived column.

For example:

daniel=> create table calendar ("date" date not null, "year" int default year("date"), "month" int default month("date"), "day" int default day("date")); CREATE TABLE daniel=> copy calendar(date) from stdin direct; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 2000-01-01 >> 2000-01-02 >> 2000-01-03 >>\ \ .                                     --- PARSER ((( daniel=> select * from calendar ;     date    | year | month | day  ------------+------+-------+-----  2000-01-01 | 2000 |     1 |   1  2000-01-02 | 2000 |     1 |   2  2000-01-03 | 2000 |     1 |   3 (3 rows)