Snowflake case statement to return multiple columns value in procedure

Bharat 0
Viewed 30

Below is the snowflake procedure in am trying to run.

CREATE OR REPLACE PROCEDURE test()
RETURNS VARCHAR(16777216)
LANGUAGE SQL
AS
$$
DECLARE


V_LAT varchar;
V_LNG varchar;

BEGIN


 INSERT INTO test.crs_compact.case_test 
  (
  c_address,
  c_comment
  )
SELECT
  (CASE WHEN c_nationkey = 0 then (:V_LAT=a.c_address, :V_LNG=c_comment)  
 END) 
from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."CUSTOMER" as a;
  
    
  return 'Data Loaded Successfully';
END;
$$

while calling the procedure getting below error.

Uncaught exception of type 'STATEMENT_ERROR' on line 11 at position 1 : SQL compilation error: error line 7 at position 3 Invalid argument types for function 'IFF': (BOOLEAN, ROW(BOOLEAN, BOOLEAN), NULL)

snowflake-cloud-data-platform
1 Answers

(a.c_address, c_comment) or (:V_LAT=a.c_address, :V_LNG=c_comment) is a ROW

you cannot put a ROW inside a CASE (which is also an IFF)..

Where you have typed looks like you are trying to do this:

SELECT
  a.c_address, c_comment
from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."CUSTOMER" as a
WHERE c_nationkey = 0;

Or more to the point:

INSERT INTO test.crs_compact.case_test 
  (
  c_address,
  c_comment
  )
SELECT
  a.c_address, c_comment
from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."CUSTOMER" as a
WHERE c_nationkey = 0;

Now if you want those null values instead of skipping them:


INSERT INTO test.crs_compact.case_test 
  (
  c_address,
  c_comment
  )
SELECT
  iff(c_nationkey = 0, a.c_address, NULL)
  ,iff(c_nationkey = 0, c_comment, NULL)
from "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."CUSTOMER" as a;
Simeon Pilgrim0
Related