Tengo SP que mira la tabla de columnas del esquema de información y convierte las columnas en minúsculas en mayúsculas. No se pueden envolver códigos dobles alrededor del objeto.
ALTER TABLE SCHEMA_S.TBLNAME RENAME COLUMN ColName to COLNAME; //Won't work ALTER TABLE "SCHEMA_S"."TBLNAME" RENAME COLUMN "ColName" to "COLNAME"; //this works select ('ALTER TABLE ' || TABLE_NAME || ' RENAME COLUMN "' || COLUMN_NAME || '" to "' || UPPER(COLUMN_NAME) || '";') AS COL1 from information_schema.columns WHERE TABLE_NAME = 'TBLNAME' AND TABLE_SCHEMA = 'SCHEMA_S' AND COLUMN_NAME != UPPER(COLUMN_NAME) //this works in SnowflakePero dentro de javascript, no se pueden usar comillas dobles javascript
var table_control = " SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA,'.' ,TABLE_NAME, ' RENAME COLUMN ', COLUMN_NAME, ' to ', UPPER(COLUMN_NAME), ';') AS COL1 " table_control += " FROM INFORMATION_SCHEMA.COLUMNS " table_control += " WHERE TABLE_SCHEMA = " + String.fromCharCode(39) + CURRENT_SCHEMA + String.fromCharCode(39) table_control += " AND TABLE_NAME = " + String.fromCharCode(39) + CURRENT_TABLE + String.fromCharCode(39) table_control += " AND COLUMN_NAME != UPPER(COLUMN_NAME) " ; //this works var table_control = " SELECT CONCAT('ALTER TABLE "', TABLE_SCHEMA,'"."' ,TABLE_NAME, '" RENAME COLUMN "', COLUMN_NAME, '" to "', UPPER(COLUMN_NAME), '";') AS COL1 " table_control += " FROM INFORMATION_SCHEMA.COLUMNS " table_control += " WHERE TABLE_SCHEMA = " + String.fromCharCode(39) + CURRENT_SCHEMA + String.fromCharCode(39) table_control += " AND TABLE_NAME = " + String.fromCharCode(39) + CURRENT_TABLE + String.fromCharCode(39) table_control += " AND COLUMN_NAME != UPPER(COLUMN_NAME) " ; //won't workAparece a continuación, error de compilación de JavaScript: error de sintaxis no detectado: cadena inesperada en CONVERT_TBL_COLUMNS_UPPERCASE en ' var table_control = " SELECT CONCAT('ALTER TABLE "', TABLE_SCHEMA,'"."' ,TABLE_NAME, '" RENAME COLUMN "', COLUMN_NAME, ' " a "', UPPER(COLUMN_NAME), '";') AS COL1 " ' posición 58
En pocas palabras: necesito concatenar objetos (columnas/esquema/tabla) con doble verificación para que pueda cambiar el nombre de la columna en mayúsculas dinámicamente a través de javascript.
Por favor ayuda, me estoy perdiendo algo simple!
Creé un JS UDF que cambiará el nombre de las columnas a mayúsculas.
Utiliza cadenas de plantilla JS para evitar problemas con las comillas de escape:
create or replace procedure alter_table_to_uppercase( TABLE_SCHEMA string, TABLE_NAME string ) returns variant language javascript as $$ var select_cols = ` select column_name from information_schema.columns where table_catalog = current_database() and table_schema = '${TABLE_SCHEMA}' and table_name = '${TABLE_NAME}' and column_name != upper(column_name) `; var statement1 = snowflake.createStatement( {sqlText: select_cols} ); var result_set1 = statement1.execute(); var cols_changed = []; while (result_set1.next()) { var col_name = result_set1.getColumnValue(1); var alter_sql = ` alter table ${TABLE_SCHEMA}.${TABLE_NAME} rename column "${col_name}" to "${col_name.toUpperCase()}" ` snowflake.createStatement({sqlText: alter_sql}).execute(); cols_changed.push(col_name); } return cols_changed; $$Probarlo es fácil:
create or replace table lowercased as select 1 "a", 2 "b", 3 "c" ; call alter_table_to_uppercase(current_schema(), 'LOWERCASED');Algunos pensamientos adicionales que podrían ser útiles para resolver su problema.