CODEWITHSUNDEEP
mysqlsqldatabaseh2database-administration
Pert8S
Pert8S

Reputation: 582

Trying to add update in view creation

I have created one view on base table test1 and i have added two columns in it while creation.

create or replace force view abc1 as (select A.*,cast(NULL as timestamp) AS new1,cast(NULL as varchar2) AS new2 from test1 A);

so after creating view, it has data as follows :

select * from abc1;

TT            | TT_C2                 | TT_DAY     | TT_HOURLY     | OC   | SEV      | EXTRACOL | IDENTIFIER | NEW1 | NEW2
1468309423961 | 2016-07-12 07:43:43.0 | 12-07-2016 | 12-07-2016 07 | abc  | Critical | asdf     | 1          | null | null
1468922692865 | 2016-07-19 10:04:52.0 | 19-07-2016 | 19-07-2016 10 | abc  | Critical | asdf     | 1          | null | null
1568308812001 | 2019-09-12 17:20:12.0 | 12-09-2019 | 12-09-2019 17 | abc1 | Major    | asdf     | 1          | null | null
1468308812001 | 2016-07-12 07:33:32.0 | 12-07-2016 | 12-07-2016 07 | abc2 | Minor    | asdf     | 1          | null | null
1468308815972 | 2016-07-12 07:33:35.0 | 12-07-2016 | 12-07-2016 07 | abc  | Critical | asdf     | 1          | null | null

now i am trying to update new1 column which is of timestmap datatype but i am not able to update the view because of some limitation..

update view abc1 set new1 = DATEADD('SECOND', (TT)/1000), DATE '1970-01-01') WHERE new1 IS NULL;

Error coming is ::

Syntax error in SQL statement "UPDATE ABC1 SET NEW1 = DATEADD('SECOND', (TT)/1000)[*], DATE '1970-01-01') WHERE NEW1 IS NULL "; expected "[, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE, REGEXP, IS, IN, BETWEEN, AND, OR, ,"; SQL statement:
update abc1 set new1 = DATEADD('SECOND', (TT)/1000), DATE '1970-01-01') WHERE new1 IS NULL [42001-176]

How I can add my update "new1" column query into my view creation so that this will be addressed during view creation only.

This is my base table ::

TT            | TT_C2                 | TT_DAY     | TT_HOURLY     | OC   | SEV      | EXTRACOL | IDENTIFIER
1468309423961 | 2016-07-12 07:43:43.0 | 12-07-2016 | 12-07-2016 07 | abc  | Critical | asdf     | 1
1468922692865 | 2016-07-19 10:04:52.0 | 19-07-2016 | 19-07-2016 10 | abc  | Critical | asdf     | 1
1568308812001 | 2019-09-12 17:20:12.0 | 12-09-2019 | 12-09-2019 17 | abc1 | Major    | asdf     | 1
1468308812001 | 2016-07-12 07:33:32.0 | 12-07-2016 | 12-07-2016 07 | abc2 | Minor    | asdf     | 1
1468308815972 | 2016-07-12 07:33:35.0 | 12-07-2016 | 12-07-2016 07 | abc  | Critical | asdf     | 1

thanks in advance !

regards

Upvotes: 0

Views: 50

Answers (1)

Ashay Patil
Ashay Patil

Reputation: 152

Ideally you should not update view.

As per your requirement, you have to use wrapper for getting columns(i.e. select over select).

 create or replace force view abc7 as (select tt,oc,sev, converted_event_time,formatdatetime(converted_event_time,'d-MM-yyyy HH') ,formatdatetime(converted_event_time,'d-MM-yyyy') AS event_time_day from (select tt,oc,sev,DATEADD('SECOND', (tt)/1000, DATE '1970-01-01') AS converted_event_time from test1));

Hope you find this answer useful.

Upvotes: 1

Related Questions