SQLite Forum

How to convert the data type of the new column in the view?
Login

How to convert the data type of the new column in the view?

(1) By anonymous on 2021-06-02 09:32:39 [link]

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

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.