create a table from another and a literal

Question

I'm working with sqlite and trying to create a table from another.
This works:

create table sources_tmp as select "literal" system,name,user from sources;

but it doesn't allow me to specify the type for the "literal".
I've tried this but doesn't work that way apparently:

create table sources_tmp as select "literal" system as $TYPE,name,user from sources;

Answer 1

Use CAST(). "AS" means something else. Example:

CREATE TABLE TEST(ID INT, NAME VARCHAR);
INSERT INTO TEST VALUES(1, '10');
CREATE TABLE TEST2 AS 
SELECT CAST(ID AS VARCHAR) A, CAST(NAME AS INT) X FROM TEST;