Stored procedure throwing "Missing Column Specification"

Question

I am creating a Snowflake Stored procedure using Javascript. I am getting an SQL Exception thrown by the procedure, saying "Missing Column Specification".

The stack trace points to this part:

var cmd_outputCreation = `CREATE OR REPLACE TABLE ${TBL_OUTPUT} AS(
                                    SELECT A.*, B.RESULT[0], B.RESULT[1]
                                    FROM(
                                      SELECT ${API_FUNCTION}(joined.*) AS result
                                      FROM (
                                          SELECT ${c_id}, ${c_location_name}, 
                                          ${c_street_address}, ${c_city}, ${c_region}, 
                                          ${c_postal_code}, ${c_latitude}, ${c_longitude},
                                          ${c_country_code}
                                          FROM ${TBL_INPUT} 
                                      ) AS joined
                                    ) AS B
                                    INNER JOIN ${TBL_INPUT} AS A
                                    ON A.RECID = B.RESULT[0]
                                  )`;
var stmt_outputCreation = snowflake.createStatement( {sqlText: cmd_outputCreation} );
var result_outputCreation = stmt_outputCreation.execute();

However, I am not able to see any error here. The query itself runs on SQL if I use it without the SP (obviously replacing the variable names with actual SQL Tables/columns)

And I know that "Missing Column Specification" means "A column has no name", but this doesn't seem to be the case. What am I missing here?

Greg Pavlik Greg Pavlik · Accepted Answer · 2020-12-16T19:47:58

You need to alias your B.RESULT[0] and B.RESULT[1] to give the columns unique names:

create temp table bar as select array_construct(1, 2) as b;

-- This gets missing column specification
create temp table foo as select b[0], b[1] from bar;

-- This does not
create temp table foo as select b[0] as B0, b[1] as B1 from bar;

Stored procedure throwing "Missing Column Specification"

1 Answers