How to convert the data type of the new column in the view？
(1) By anonymous on 2021-06-02 09:32:39 [link] [source]
table like this: CREATE TABLE t_spotprice ( tradingday TEXT(20), product_name TEXT(30), base_span REAL DEFAULT 0, base_price_ratio REAL DEFAULT 0, spot_price REAL DEFAULT 0, future_price REAL DEFAULT 0 ); create a view like this: CREATE VIEW 'test1111' AS SELECT *, (SUM(future_price)) as fp2 FROM t_spotprice WHERE tradingday = 20200103; The result of view 'test1111' is is correct!!,but the datatype of fp2 in the view of 'test1111' is unknown. can I convert to REAL?
(2) By Keith Medcalf (kmedcalf) on 2021-06-02 10:29:12 in reply to 1 [source]
If you mean that you want the affinity of test1111.fp2 to be real then you can declare the view as:
CREATE VIEW test1111 AS SELECT *, cast(SUM(future_price) as REAL) as fp2 FROM t_spotprice WHERE tradingday = 20200103;
The affinity of the result of an expression (which also includes functions) is NONE (No Affinity). This is quite separate from the datatype of the result value.