How to get the result sorted by "ORDINAL_POSITION" in Snowflake stored procedure?

Question

I have written a Snowflake stored procedure for creating a DDL for view. I am getting all the columns using two queries written inside the stored procedure which have manipulations performed for "text" and "date" datatypes. First query is for "text" and second query is for "date" datatype. Second query gives the dynamic SQL in the first execution, that has to be looped through again to get the column names. At the end, both the query results (i.e. column names from both queries) are appended and the view script is generated.

CREATE OR REPLACE PROCEDURE ADMIN.generateScript (TABLENAME varchar, TABLESCHEMA varchar)
RETURNS varchar
LANGUAGE JAVASCRIPT
AS
$$
var database
database = snowflake.execute( {sqlText: "select CURRENT_DATABASE();"} );
database.next()
database_value = database.getColumnValue(1);
var TABLENAME_local =  TABLENAME ;
var TABLESCHEMA_local =  TABLESCHEMA ;
var column_count = 0
var newschema 

//Create a query by accumulating the strings and execute the query for text columns

var q1= " select CASE WHEN DATA_TYPE = 'TEXT' THEN ',nullif('||COLUMN_NAME||','''''''') AS ' ||COLUMNS.COLUMN_NAME WHEN DATA_TYPE != 'TEXT' THEN ','||COLUMN_NAME"
q1 +=   " ELSE ','||COLUMN_NAME END AS ALIAS, ORDINAL_POSITION from "+ database_value +".INFORMATION_SCHEMA.COLUMNS where  TABLE_NAME = '" 
q1 += TABLENAME_local 
q1 += "' and TABLE_SCHEMA =  '"
q1 += TABLESCHEMA_local 
q1 += "' and COLUMN_NAME not like '%_SDC_%' and DATA_TYPE not in ('TIMESTAMP_TZ','TIMESTAMP_NTZ','TIMESTAMP_LTZ', 'DATE') order by ORDINAL_POSITION;"

var rs =  snowflake.execute( {sqlText: q1} );
var return_value = "";
while (rs.next())  {
        column_count += 1;
      return_value += rs.getColumnValue(1);
       }


//Create another query by accumulating the strings and execution of this query gives dynamic SQL as result for date datatype columns

var q2 = " select COLUMN_NAME,'select '''||COLUMN_NAME||''',CASE WHEN (select count(distinct cast('||COLUMN_NAME||' AS TIME)) from "+ database_value +".'||TABLE_SCHEMA||'.'||TABLE_NAME||')>1 THEN '',convert_timezone(''''''''UTC'''''''','||COLUMN_NAME||') AS '||COLUMN_NAME||''' ELSE '','||COLUMN_NAME||'::DATE AS '||COLUMN_NAME||''' END AS STATEMENT ' "
q2 += " ,ORDINAL_POSITION from "+ database_value +".INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '"
q2 += TABLENAME_local 
q2 += "' and TABLE_SCHEMA =  '"
q2 += TABLESCHEMA_local 
q2 += "' and COLUMN_NAME not like '%_SDC_%' and DATA_TYPE in ('TIMESTAMP_TZ','TIMESTAMP_NTZ','TIMESTAMP_LTZ', 'DATE') order by ORDINAL_POSITION;"
 
var rs1 =  snowflake.execute( {sqlText: q2} );
var rs2
while (rs1.next())  {
    rs2 =  snowflake.execute( {sqlText: rs1.getColumnValue(2)} );
    //Looping through the dynamic SQL output to get the actual columns
    while (rs2.next())  {
        column_count += 1;
      return_value += rs2.getColumnValue(2);
      }
       }
 
 //Remove the first comma in the first column
 rs1 =  snowflake.execute( {sqlText: "select RIGHT( '" + return_value + "',LEN('" + return_value + "')-1);"} );
 rs1.next()
 return_value = rs1.getColumnValue(1);

 
 //Creating view
 newschema = snowflake.execute( {sqlText: "select SUBSTRING('"+TABLESCHEMA_local+"',0,CHARINDEX('_','"+TABLESCHEMA_local+"')-1);"} );
 newschema.next()
 newschema_value = newschema.getColumnValue(1);
 query_statement =  "CREATE OR REPLACE view "+newschema_value+".vw_" +TABLENAME_local+" AS SELECT " + return_value + " from " + TABLESCHEMA_local + "."+ TABLENAME_local + " --WHERE ISDELETED = FALSE" ;
 return query_statement;
 
 
$$;

CALL ADMIN.generateScript('Table_ABC','SCHEMA_RAW')

However the view script generated, doesn't have the column names based on the ORDINAL_POSITION, this code appends the first query which is "text" columns first and then the second query which is "date" columns. I want to generate the script which has columns based on ORDINAL_POSITION which can be a combination of both text and date datatype like this view:

CREATE OR REPLACE view Test_View AS 
 SELECT nullif(xyz,'') AS XYZ,
 convert_timezone('UTC',CREATEDDATE) AS CREATEDDATE, 
 nullif(abc,'') AS ABC, 
 MODIFIED_DATE::DATE AS MODIFIED_DATE
 from TABLESCHEMA.TABLENAME;

TIA

user_2117872 · Answer

The direct, simplest answer is that for your ORDER BY to work you need to union your records together BEFORE ordering.

In essence:

SELECT column
(
select num_column as column, ORDINAL_POSITION
from table

union

select date_column as column, ORDINAL_POSITION
from table
)
order by ORDINAL_POSITION

The less simple answer is that I'd recommend pulling your basic column information out from your two queries and then using Javascript to assemble it into the view definition and doing the ordering. It would save you from writing SQL within SQL within Javascript.

And there's a stackoverflow for everything from "how to remove a leading comma in Javascript" and "how to sort an array with multiple columns in Javascript". It'll take some effort, but it can definitely simplify your code, and could be worth learning how to take advantage of the power of Javascript in Snowflake procedures.

Bonus tip: if you use template literals it can significantly cleanup your code and make it much more readible by avoiding the q1 += " syntax.

How to get the result sorted by "ORDINAL_POSITION" in Snowflake stored procedure?

1 Answers