Snowflake – 语法错误：意外的’‘。查询大小限制？

huangapple
go评论92阅读模式
英文:

SNowflake - Syntax error: unexpected '<EOF>'. Query size limit?

问题

您遇到的问题可能与过程中的字符数限制有关。尝试通过删除注释来增加列数可能有效，因为注释会增加字符数。您可以尝试优化您的代码以减少字符数，或者查看您使用的数据库管理系统是否有字符数限制的文档，以了解更多关于限制的信息。

不过，请注意，对于复杂的存储过程，过多的列可能会使代码难以维护和理解。在添加大量列之前，最好考虑是否可以重新设计数据库模式，以更好地满足您的需求。

如果您需要进一步的帮助或解决方案，请提供更多信息，我将尽力提供支持。

英文:

I have a procedure and I want to modify this. When I use this request:

  1. CREATE OR REPLACE PROCEDURE import_stage_sfr_ac_reeng_fixe_handicall_bordeaux(p_loading_mode VARCHAR)
  2. RETURNS VARCHAR NOT NULL
  3. LANGUAGE SQL
  4. AS
  5. BEGIN
  6.  
  7. 	IF (:p_loading_mode =&#39;incremental&#39;) THEN
  8.         COPY INTO SFR_AC_REENG_FIXE_HANDICALL_BORDEAUX (
  9.         	V_NUM_COMMANDE_MIG,
  10.         	V_SITE_CMD,
  11.         	V_ACTIVITE_EZY,
  12.         	V_TYPE_AC,
  13.             D_JOUR_CMD,
  14.             D_JOUR_PANIER,
  15.             V_SEMAINE_CMD,
  16.             V_MOIS_CMD,
  17.             V_LOGIN_CC_CMD,
  18.             V_NUM_CONTRAT,
  19.  
  20.             V_TYPE_ACTE,
  21.             B_MIG,
  22.             V_COURANT_PTA_MIG,
  23.             V_LIBL_COURANT_PTA_MIG,
  24.             V_FUTUR_PTA_MIG,
  25.             V_LIBL_FUTUR_PTA_MIG,
  26.             V_LIBL_FUTUR_PTA_MIG_COURT,
  27.             B_REMISE,
  28.             V_LIBL_REMISE,
  29.             N_MT_REMISE_M_TTC,
  30.  
  31.             B_USAGE,
  32.             V_LIBL_USAGE,
  33.             V_NOM,
  34.             V_PRENOM,
  35.             N_FACIAL_AVANT,
  36.             N_FACIAL_APRES,
  37.             N_REMISE_AVANT,
  38.             N_REMISE_APRES,
  39.             N_MOUVEMENT_VALEUR,
  40.             V_CODE_CAMPAGNE,
  41.             
  42.             B_FLAG_HBD,
  43.             V_CODE_CAMPAGNE,
  44.             V_CODE_LOT,
  45.             V_RAC,
  46.             
  47.             V_TECH_NOM_FICHIER,
  48.             V_TECH_STATUT_TRAITEMENT,
  49.             V_TECH_NOM_TRAITEMENT,
  50.             TS_TECH_DEBUT_TRAITEMENT
  51.         ) FROM ( 
  52.         SELECT
  53.         	$12,
  54. 	        $1,
  55. 	        $2,
  56. 	        $3,
  57. 	        TO_DATE($4, &#39;YYYY-MM-DD&#39;),
  58. 	        TO_DATE($5, &#39;YYYY-MM-DD&#39;),
  59. 	        $6,
  60. 	        $7,
  61. 	        $8,
  62. 	        $9,
  63.  
  64. 	        $10,
  65. 	        CAST($11 AS BOOLEAN),
  66. 	        $13,
  67. 	        $14,
  68. 	        $15,
  69. 	        $16,
  70. 	        $17,
  71. 	        CAST($18 AS BOOLEAN),
  72. 	        $19,
  73. 	        CAST($20 AS NUMBER(10, 0)),
  74.             
  75. 	        CAST($21 AS BOOLEAN),
  76. 	        $22,
  77. 	        $23,
  78. 	        $24,
  79. 	        CAST(REPLACE($25, &#39;,&#39;, &#39;.&#39;) AS NUMBER(6, 3)),
  80.         	CAST(REPLACE($26, &#39;,&#39;, &#39;.&#39;) AS NUMBER(6, 3)),
  81.         	CAST(REPLACE($27, &#39;,&#39;, &#39;.&#39;) AS NUMBER(4, 2)),
  82.         	CAST(REPLACE($28, &#39;,&#39;, &#39;.&#39;) AS NUMBER(4, 2)),
  83. 	        CAST(REPLACE($29, &#39;,&#39;, &#39;.&#39;) AS NUMBER(6, 3)),
  84.             $30,
  85.  
  86. 	        TO_BOOLEAN($31),
  87. 	        $32,
  88. 	        $33,
  89. 	        $34,
  90.             
  91.         	METADATA$FILENAME,
  92.         	&#39;I&#39;,
  93.         	&#39;INGEST MODE : &#39;|| :p_loading_mode,
  94.         	current_timestamp(0)
  95.         FROM  @ES_SFR )
  96.         FILE_FORMAT  = ( FORMAT_NAME  = FF_INGEST_CSV_SFR)
  97.         PATTERN  =  &#39;.*incremental.*ac_reeng_fixe_handicall_bordeaux.*\.csv&#39;;
  98. 	END IF;

It work. But if I add just a column, I have the message Syntax error: unexpected &#39;&lt;EOF&gt;&#39;. no matter the column.

Example:

  1. CREATE OR REPLACE PROCEDURE import_stage_sfr_ac_reeng_fixe_handicall_bordeaux(p_loading_mode VARCHAR)
  2. RETURNS VARCHAR NOT NULL
  3. LANGUAGE SQL
  4. AS
  5. BEGIN
  6.  
  7. 	IF (:p_loading_mode =&#39;incremental&#39;) THEN
  8.         COPY INTO SFR_AC_REENG_FIXE_HANDICALL_BORDEAUX (
  9.         	V_NUM_COMMANDE_MIG,
  10.         	V_SITE_CMD,
  11.         	V_ACTIVITE_EZY,
  12.         	V_TYPE_AC,
  13.             D_JOUR_CMD,
  14.             D_JOUR_PANIER,
  15.             V_SEMAINE_CMD,
  16.             V_MOIS_CMD,
  17.             V_LOGIN_CC_CMD,
  18.             V_NUM_CONTRAT,
  19.  
  20.             V_TYPE_ACTE,
  21.             B_MIG,
  22.             V_COURANT_PTA_MIG,
  23.             V_LIBL_COURANT_PTA_MIG,
  24.             V_FUTUR_PTA_MIG,
  25.             V_LIBL_FUTUR_PTA_MIG,
  26.             V_LIBL_FUTUR_PTA_MIG_COURT,
  27.             B_REMISE,
  28.             V_LIBL_REMISE,
  29.             N_MT_REMISE_M_TTC,
  30.  
  31.             B_USAGE,
  32.             V_LIBL_USAGE,
  33.             V_NOM,
  34.             V_PRENOM,
  35.             N_FACIAL_AVANT,
  36.             N_FACIAL_APRES,
  37.             N_REMISE_AVANT,
  38.             N_REMISE_APRES,
  39.             N_MOUVEMENT_VALEUR,
  40.             V_CODE_CAMPAGNE,
  41.             
  42.             B_FLAG_HBD,
  43.             V_CODE_CAMPAGNE,
  44.             V_CODE_LOT,
  45.             V_RAC,
  46.             V_PROMO_TR,
  47.             
  48.             V_TECH_NOM_FICHIER,
  49.             V_TECH_STATUT_TRAITEMENT,
  50.             V_TECH_NOM_TRAITEMENT,
  51.             TS_TECH_DEBUT_TRAITEMENT
  52.         ) FROM ( 
  53.         SELECT
  54.         	$12,
  55. 	        $1,
  56. 	        $2,
  57. 	        $3,
  58. 	        TO_DATE($4, &#39;YYYY-MM-DD&#39;),
  59. 	        TO_DATE($5, &#39;YYYY-MM-DD&#39;),
  60. 	        $6,
  61. 	        $7,
  62. 	        $8,
  63. 	        $9,
  64.  
  65. 	        $10,
  66. 	        CAST($11 AS BOOLEAN),
  67. 	        $13,
  68. 	        $14,
  69. 	        $15,
  70. 	        $16,
  71. 	        $17,
  72. 	        CAST($18 AS BOOLEAN),
  73. 	        $19,
  74. 	        CAST($20 AS NUMBER(10, 0)),
  75.             
  76. 	        CAST($21 AS BOOLEAN),
  77. 	        $22,
  78. 	        $23,
  79. 	        $24,
  80. 	        CAST(REPLACE($25, &#39;,&#39;, &#39;.&#39;) AS NUMBER(6, 3)),
  81.         	CAST(REPLACE($26, &#39;,&#39;, &#39;.&#39;) AS NUMBER(6, 3)),
  82.         	CAST(REPLACE($27, &#39;,&#39;, &#39;.&#39;) AS NUMBER(4, 2)),
  83.         	CAST(REPLACE($28, &#39;,&#39;, &#39;.&#39;) AS NUMBER(4, 2)),
  84. 	        CAST(REPLACE($29, &#39;,&#39;, &#39;.&#39;) AS NUMBER(6, 3)),
  85.             $30,
  86.  
  87. 	        TO_BOOLEAN($31),
  88. 	        $32,
  89. 	        $33,
  90. 	        $34,
  91.             $35,
  92.             
  93.         	METADATA$FILENAME,
  94.         	&#39;I&#39;,
  95.         	&#39;INGEST MODE : &#39;|| :p_loading_mode,
  96.         	current_timestamp(0)
  97.         FROM  @ES_SFR )
  98.         FILE_FORMAT  = ( FORMAT_NAME  = FF_INGEST_CSV_SFR)
  99.         PATTERN  =  &#39;.*incremental.*ac_reeng_fixe_handicall_bordeaux.*\.csv&#39;;
  100. 	END IF;

I search since this morning, I was able to add more column by deleting the comments. I therefore really wonder if this is not a limitation of the number of characters in the procedure. Any idea ?

Thanks

答案1

得分: 1

要解决问题，您需要在代码块周围添加$$符号进行引用。

示例：

  1. CREATE OR REPLACE PROCEDURE import_stage_sfr_ac_reeng_fixe_handicall_bordeaux(p_loading_mode VARCHAR)
  2. RETURNS VARCHAR NOT NULL
  3. LANGUAGE SQL
  4. AS
  5. $$
  6. BEGIN
  7. --您的代码脚本块
  8. END
  9. $$;

文档参考。

英文:

To fix the issue you need to add the $$ sings to quote the code block.

Example:

  1. CREATE OR REPLACE PROCEDURE import_stage_sfr_ac_reeng_fixe_handicall_bordeaux(p_loading_mode VARCHAR)
  2. RETURNS VARCHAR NOT NULL
  3. LANGUAGE SQL
  4. AS
  5. $$
  6. BEGIN
  7. --your code the scripting block
  8. END
  9. $$;

Doc reference.

huangapple
  • 本文由 发表于 2023年5月17日 20:51:32
  • 转载请务必保留本文链接：https://go.coder-hub.com/76272324.html
  • eof
  • procedure
  • snowflake-cloud-data-platform
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定