CODEWITHSUNDEEP
sqlviewsnowflake-cloud-data-platform
user19349649
user19349649

Reputation: 1

Create Null Column in Snowflake Query and Define Data Type

I have a View1 with 15 columns and I would like to Union it to View2 which has 100 columns. The 15 columns in View1 align with 15 of the 100 columns in View2 but from my understanding, I can't Union 2 tables or views in Snowflake unless they have the same set of columns.

Instead, I'm trying to add the other 85 columns with a NULL value to View1 and define it in the View1 query but the data types for those 85 missing columns vary (boolean, string, integer, date, etc). I can write something like this:

CREATE VIEW "VIEW1" AS
SELECT FIELD1
      ,FIELD2
      ,...
      ,FIELD15
      ,NULL AS FIELD16
      ,NULL AS FIELD17
      ,...
      ,NULL AS FIELD100
FROM SOURCE_TABLE

But Snowflake is automatically assigning Fields 16-100 a VARCHAR data type which will still create a problem when I go to union View1 and View2. Is there a way to define the data type in the query? Sort of like "NULL AS FIELD16 BOOLEAN"? If not, is there another way (or better way) of doing this? I can't alter the source table, unfortunately.

Upvotes: 0

Views: 6767

Answers (3)

Raja Sekhar Reddy Moola
Raja Sekhar Reddy Moola

Reputation: 11

Type casting will clear the data type problem. enter image description here

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

Is there a way to define the data type in the query? Sort of like "NULL AS FIELD16 BOOLEAN"?

NULL by design has TEXT data type:

CREATE OR REPLACE VIEW v1 
AS
SELECT NULL AS c;

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ILIKE 'v1';
-- DATA TYPE
-- TEXT

When set operator UNION ALL is applied the matching column will be subject to Implicit Casting (“Coercion”).

General Usage Notes

Make sure that the data type of each column is consistent across the rows from different sources.

In order to avoid it, NULL value should be explicitly casted to proper data type:

CREATE VIEW "VIEW1" AS
SELECT FIELD1
      ,FIELD2
      ,...
      ,FIELD15
      ,NULL::BOOLEAN AS FIELD16
      ,NULL::BOOLEAN AS FIELD17
      ,...
      ,NULL::INT AS FIELD100
FROM SOURCE_TABLE

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

You could try to cast the NULL literals to the type you want, e.g.

CREATE VIEW "VIEW1" AS
SELECT FIELD1,
       FIELD2,
       ...
       FIELD15,
       CAST(NULL AS BOOLEAN) AS FIELD16,
       ...
FROM SOURCE_TABLE;

Upvotes: 0

Related Questions