如何在Snowflake中编写等效的IF ELSE addhoc SQL查询 [英] How to write an equivalent IF ELSE adhoc sql query in Snowflake

问题描述

我正在尝试创建一个与下面的基于T-SQL的即席查询等效的Snowflake.

I am trying to create a Snowflake equivalent to the below T-SQL based adhoc query.

**T-SQL version**
Declare @i int = 0;

If(@i = 0)
   PRINT '0';
Else
 Begin
   PRINT '1'
   RETURN;
   PRINT '2'
 End


**Snowflake version**
Set i = 0;

If($i = 0)
   Select '0';
Else
 Begin
   Select '1'
   RETURN;
   Select '2'
 End

当我从Snowflake Web UI运行Snowflake查询时，出现错误 SQL编译错误:位置0处的语法错误第1行出现意外的'IF'

When I am running the Snowflake query from the Snowflake Web UI, I am getting an error as SQL compilation error : syntax error line 1 at position 0 unexpected 'IF'

我搜索了雪花文档，但没有找到有关以下方面的有用文档

I have searched the snowflake documentation and did not find helpful documentation for:

1. 如果不是
2. 开始结束
3. 返回

任何帮助将不胜感激.

谢谢.

推荐答案

Snowflake不支持条件T-SQL语句.它在查询中支持条件表达式.

Snowflake does not support conditional T-SQL statements. It does support conditional expressions within a query.

对于条件语句执行，请考虑将Snowflake的存储过程与如果条件表达式的使用不足以满足您的需求，则使用嵌入式JavaScript .

For conditional statement execution, consider using Snowflake's stored procedures with embedded JavaScript if the use of conditional expressions is inadequate for your needs.

一个非常简单的存储过程示例，说明了调用时的条件查询执行:

A very trivial example of a stored procedure, illustrating a conditional query execution upon call:

CREATE OR REPLACE PROCEDURE stored_proc_example(TEST_VALUE INTEGER)
RETURNS VARCHAR
LANGUAGE JAVASCRIPT
AS
$$
    // Declaring SQL statement(s) to execute later
    var command_1 = "SELECT 1";
    var command_2 = "SELECT 2";

    if (TEST_VALUE === 0) {

        var stmt = snowflake.createStatement({ sqlText: command_1 });
        var rs = stmt.execute();
        // Omitted: Perform logic with 'rs' rows to prepare a return value
        return '1';

    } else {

        var stmt = snowflake.createStatement({ sqlText: command_2 });
        var rs = stmt.execute();
        // Omitted: Perform logic with 'rs' rows to prepare a return value
        return '2';

    }
$$
;

运行方式:

SET i=0;
CALL stored_proc_example($i);

SET i=1;
CALL stored_proc_example($i);

这篇关于如何在Snowflake中编写等效的IF ELSE addhoc SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！