Calling function within SP in a LOOP

Off-Topic
1

Hi,

I would like to build a stored procedure that is used in a pipeline. (pipeline ingesting into a SP).
For simplicity, i have limited to only 1 input var.
I’ve tried many different combination

  1. declare a query variable that get the value from the loop.
  2. create a Table function that returns 1 value, but i get : Cannot call table valued function fn_inventory_fact_date_key in a scalar expression
  3. i also tried to assign the variable inside the loop using the function.

Any help would be greatly appreciated.

CREATE PROCEDURE `sp_inventory_fact_import`(batch query
	(
	p_ztimestamp timestamp  not NULL 
)
 )
 RETURNS void 
AS

Declare v_timestamp timestamp;
Declare v_time_id INT; 
Declare v_time_of_day INT;
Declare v_date INT;

-- DECLARE t query(i INT) = SELECT time_of_day_key FROM time_of_day_dim WHERE 24hr = v_time_of_day;
-- DECLARE d query(j INT) = SELECT date_key FROM date_dim WHERE date = v_date;

-- DECLARE v_time_of_day_key int ;
-- DECLARE v_date_key int ;


BEGIN


FOR r IN collect(batch) LOOP

v_timestamp = r.p_ztimestamp;
v_date = DATE(r.p_ztimestamp);
v_time_of_day = HOUR(r.p_ztimestamp); 

-- v_time_of_day_key = fn_inventory_fact_date_key(v_time_of_day) ; 
-- v_date_key =  fn_inventory_fact_date_key(v_date) ; 

INSERT INTO inventory_fact (
 date_key    ,
 time_of_day_key , 
 ztimestamp  , 
 total_count 
)
SELECT 
 fn_inventory_fact_date_key(SCALAR(v_date)),
 fn_inventory_fact_timeofday_key(SCALAR(v_date)),
--    v_date_key,
--    v_time_of_day_key,
--	SCALAR(d),
--	SCALAR(t),
	v_timestamp ;

END LOOP;
END //

2

Franck,

I see that you also have a Support ticket open for this request. We’ll continue to assist you in the Support ticket. Feel free to post the answer here to help others, once we determine a good solution.

Best,

3

A few points of clarification:

SCALAR() must take a query type value as an argument. In some of your code you are passing a type that is not a query to SCALAR().

SCALAR() cannot appear in a SQL SELECT statement. You need to call it in an assignment statement, typically.

TVFs can be called in the FROM clause of a query, not on the right of an assignment.

It’s a best practice to use the datetime type and not the timestamp type inside SP logic. Timestamp is a special type to be reserved only for columns that are to be updated with a new time value automatically when a row is updated–and even that is subjects to change. See the docs on data types for a warning.

4

Hi Hanson,
I tried to use TVF and it did not work either. As for the timestamp, that’s how the data is stored in the files in S3. In data warehouse, it’s common practice to store timestamp UTC in order to be able to convert to different timezones.
This is the latest i have and I still get the following error :
ERROR_MESSAGE: QUERY variable argument to the SCALAR builtin returned zero rows

It looks like the SP is unable to pass v_time_of_day or v_date from the Loop.

Declare v_time_of_day INT;
Declare v_date INT;

DECLARE t query(i INT) = SELECT time_of_day_key FROM time_of_day_dim WHERE 24hr = v_time_of_day;
DECLARE d query(j INT) = SELECT date_key FROM date_dim WHERE date = v_date;

DECLARE v_time_of_day_key int ;
DECLARE v_date_key int ;


BEGIN


FOR r IN collect(batch) LOOP

v_timestamp = r.p_ztimestamp;
v_date = DATE(r.p_ztimestamp);
v_time_of_day = HOUR(r.p_ztimestamp);
v_fw_pod_number = r.p_fw_pod_number ;
.. redacted

v_date_key = SCALAR(d);
v_time_of_day_key = SCALAR(t);

INSERT INTO inventory_fact (
 date_key    ,
 time_of_day_key , 
 ztimestamp  ,
... redacted
)
SELECT 
	v_date_key,
	v_time_of_day_key,
	v_timestamp,
5

However, I had this Stored Proc being used in an pipeline ingesting using a variable inside the loop working.

CREATE PROCEDURE `sp_pipeline_w_ingest`(batch query(
	`p_message` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL, 
	`p_ztimestamp` timestamp NULL, `p_log_file` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL, 
	`p_table_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL, 
	`p_schema_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL, 
	`p_ddl_type` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NULL, 
	) 

RETURNS void 
AS
Declare  v_service_id INT ;
Declare  v_created_at DATETIME ;
Declare  v_updated_at DATETIME ;
DECLARE v_ddl_type VARCHAR(15);
DECLARE v_table_name VARCHAR(65);
DECLARE	v_schema_name VARCHAR(25);	

 DECLARE q query(vv INT) = SELECT count(*) FROM walk_today WHERE service_id = v_service_id;
BEGIN
	FOR r IN collect(batch) LOOP
    
     v_service_id = r.p_message::id ;
	 
 	 v_created_at = r.p_message::$created_at ;
 	 v_updated_at = r.p_message::$updated_at ;
 	 
	 v_table_name = r.p_table_name;
	 v_schema_name = r.p_schema_name;
     v_ddl_type = r.p_ddl_type;
            
	If v_created_at > '2018-12-31 00:00:00' then  
	
		IF v_ddl_type = "insert"   AND v_table_name = "walk" THEN
	 
		insert IGNORE into  walk_today (
  	service_id ,
	created_at,
	updated_at )

		SELECT 
     v_service_id,
	 v_created_at,
	 v_updated_at
	 ;
	 
		END IF;

	END IF;
       END LOOP;
   END //