I have been all over the place looking for a solution to this and I have tried everything I have found. I have tried doing a declare of res in the declare, then the SQL query in the begin with the return table(res) with it and get the same results.

The Stored Procedure builds with no problem, but when I try to call it with either the call command or in the from of a select command, I get the result:

Stored procedure execution error: data type of returned table does not match expected returned table type

Here is my current code:

CREATE OR REPLACE PROCEDURE "Online_Mobile_Activity"()
RETURNS TABLE ("ACCTINST" NUMBER(38,0), "BRANCH" NUMBER(38,0), "TRAN_DATE" DATE, "TRAN_TIME" TIME(9), "ACCOUNT" VARCHAR, "ACCT_TYP" VARCHAR, "TRAN_AMT" NUMBER(38,0), "TRAN_TYP" VARCHAR, "USER_ID" VARCHAR, "PRIMARY_OWNER" VARCHAR, "EMAIL_ADDRESS" VARCHAR, "NAME_ID" VARCHAR, "OPEN_DATE" DATE, "CLASS_CODE" VARCHAR, "PORTFOLIO" VARCHAR)
LANGUAGE SQL
STRICT
EXECUTE AS OWNER
AS
$$
DECLARE
    res RESULTSET DEFAULT (
    SELECT
        CASE 
            WHEN b."zone_description" = 'One' THEN 1
            WHEN b."zone_description" = 'Zero' THEN 0 
        END AS "ACCTINST",
        CASE 
            WHEN a."sessionanalysis_category" = 'Phone' AND b."zone_description" = 'One' THEN 311 
            WHEN a."sessionanalysis_category" = 'Monitor' AND b."zone_description" = 'One' THEN 310
            WHEN a."sessionanalysis_category" = 'Desktop' AND b."zone_description" = 'One' THEN 310
            WHEN a."sessionanalysis_category" = 'Tablet' AND b."zone_description" = 'One' THEN 311 
            WHEN a."sessionanalysis_category" = 'Phone' AND b."zone_description" = 'Zero' THEN 301 
            WHEN a."sessionanalysis_category" = 'Monitor' AND b."zone_description" = 'Zero' THEN 300
            WHEN a."sessionanalysis_category" = 'Desktop' AND b."zone_description" = 'Zero' THEN 300 
            WHEN a."sessionanalysis_category" = 'Tablet' AND b."zone_description" = 'Zero' THEN 301 
        END AS "Branch",
        DATE(a."session_date") AS "tran_date",
        TIME(a."session_date") AS "tran_time",
        acct."Account Number" AS "ACCOUNT",
        acct."FS Group" AS "ACCT_TYP",
        0 AS "TRAN_AMT",
        CASE 
            WHEN a."sessionanalysis_category" = 'Phone' THEN 'Mobile Log In' 
            WHEN a."sessionanalysis_category" = 'Monitor' THEN 'Online Log In' 
            WHEN a."sessionanalysis_category" = 'Desktop' THEN 'Online Log In' 
            WHEN a."sessionanalysis_category" = 'Tablet' THEN 'Mobile Log In' 
        END AS "TRAN_TYP",
        a."user_id",
        cif."Name" AS "Primary_Owner",
        e."Contact Info" AS "email_address",
        '' AS "name_id",
        acct."Date Opened" AS "open_date",
        acct."Class Code" AS "class_code",
        acct."Portfolio" AS "portfolio"
    FROM "PUBLISH"."Q2"."v_LogonActivity" a
        LEFT JOIN "UserData" b ON a."user_id" = b."user_id"
        LEFT JOIN "UserAccountData" d ON a."user_id" = d."user_id"
        LEFT JOIN "CIF_Name" cif ON d."CIFInternal" = cif."Tax ID Number"
        LEFT JOIN "Portfolios" p ON cif."Name ID" = p."Name 1 ID"
        LEFT JOIN "Email" e ON cif."Name ID" = e."Name ID"
        LEFT JOIN "Total_Deposits_Active" acct ON p."Portfolio Key" = acct."Portfolio Key"
    WHERE DATEDIFF(day, a."session_date", CURRENT_DATE()) = 2
        AND e."Contact Info" IS NOT NULL
        AND acct."Account Number" IS NOT NULL
    QUALIFY ROW_NUMBER() OVER (PARTITION BY a."user_id" ORDER BY a."session_date") = 1);
BEGIN
    RETURN TABLE(res);
END;
$$;

I have been all over the place looking for a solution to this and I have tried everything I have found. I have tried doing a declare of res in the declare, then the SQL query in the begin with the return table(res) with it and get the same results.

The Stored Procedure builds with no problem, but when I try to call it with either the call command or in the from of a select command, I get the result:

Stored procedure execution error: data type of returned table does not match expected returned table type

Here is my current code:

CREATE OR REPLACE PROCEDURE "Online_Mobile_Activity"()
RETURNS TABLE ("ACCTINST" NUMBER(38,0), "BRANCH" NUMBER(38,0), "TRAN_DATE" DATE, "TRAN_TIME" TIME(9), "ACCOUNT" VARCHAR, "ACCT_TYP" VARCHAR, "TRAN_AMT" NUMBER(38,0), "TRAN_TYP" VARCHAR, "USER_ID" VARCHAR, "PRIMARY_OWNER" VARCHAR, "EMAIL_ADDRESS" VARCHAR, "NAME_ID" VARCHAR, "OPEN_DATE" DATE, "CLASS_CODE" VARCHAR, "PORTFOLIO" VARCHAR)
LANGUAGE SQL
STRICT
EXECUTE AS OWNER
AS
$$
DECLARE
    res RESULTSET DEFAULT (
    SELECT
        CASE 
            WHEN b."zone_description" = 'One' THEN 1
            WHEN b."zone_description" = 'Zero' THEN 0 
        END AS "ACCTINST",
        CASE 
            WHEN a."sessionanalysis_category" = 'Phone' AND b."zone_description" = 'One' THEN 311 
            WHEN a."sessionanalysis_category" = 'Monitor' AND b."zone_description" = 'One' THEN 310
            WHEN a."sessionanalysis_category" = 'Desktop' AND b."zone_description" = 'One' THEN 310
            WHEN a."sessionanalysis_category" = 'Tablet' AND b."zone_description" = 'One' THEN 311 
            WHEN a."sessionanalysis_category" = 'Phone' AND b."zone_description" = 'Zero' THEN 301 
            WHEN a."sessionanalysis_category" = 'Monitor' AND b."zone_description" = 'Zero' THEN 300
            WHEN a."sessionanalysis_category" = 'Desktop' AND b."zone_description" = 'Zero' THEN 300 
            WHEN a."sessionanalysis_category" = 'Tablet' AND b."zone_description" = 'Zero' THEN 301 
        END AS "Branch",
        DATE(a."session_date") AS "tran_date",
        TIME(a."session_date") AS "tran_time",
        acct."Account Number" AS "ACCOUNT",
        acct."FS Group" AS "ACCT_TYP",
        0 AS "TRAN_AMT",
        CASE 
            WHEN a."sessionanalysis_category" = 'Phone' THEN 'Mobile Log In' 
            WHEN a."sessionanalysis_category" = 'Monitor' THEN 'Online Log In' 
            WHEN a."sessionanalysis_category" = 'Desktop' THEN 'Online Log In' 
            WHEN a."sessionanalysis_category" = 'Tablet' THEN 'Mobile Log In' 
        END AS "TRAN_TYP",
        a."user_id",
        cif."Name" AS "Primary_Owner",
        e."Contact Info" AS "email_address",
        '' AS "name_id",
        acct."Date Opened" AS "open_date",
        acct."Class Code" AS "class_code",
        acct."Portfolio" AS "portfolio"
    FROM "PUBLISH"."Q2"."v_LogonActivity" a
        LEFT JOIN "UserData" b ON a."user_id" = b."user_id"
        LEFT JOIN "UserAccountData" d ON a."user_id" = d."user_id"
        LEFT JOIN "CIF_Name" cif ON d."CIFInternal" = cif."Tax ID Number"
        LEFT JOIN "Portfolios" p ON cif."Name ID" = p."Name 1 ID"
        LEFT JOIN "Email" e ON cif."Name ID" = e."Name ID"
        LEFT JOIN "Total_Deposits_Active" acct ON p."Portfolio Key" = acct."Portfolio Key"
    WHERE DATEDIFF(day, a."session_date", CURRENT_DATE()) = 2
        AND e."Contact Info" IS NOT NULL
        AND acct."Account Number" IS NOT NULL
    QUALIFY ROW_NUMBER() OVER (PARTITION BY a."user_id" ORDER BY a."session_date") = 1);
BEGIN
    RETURN TABLE(res);
END;
$$;

• snowflake-cloud-data-platform
• resultset

• Can you please check the data type returned by the SQL and the table that you are returning in your procedure are same, just to confirm start with one column and see where it errors with the datatype mismatch. – Himanshu Kandpal Commented 10 hours ago

1 Answer 1

peering the problem down, to the smallest workable example:

DECLARE
    res RESULTSET DEFAULT (SELECT 1);    
BEGIN
    RETURN TABLE(res);
END;

works happy, so the SQL is valid, we can wrap it in a stored procedure:

CREATE OR REPLACE PROCEDURE so_sp_example()
RETURNS TABLE (val number)
LANGUAGE SQL
STRICT
EXECUTE AS OWNER
AS 
DECLARE
    res RESULTSET DEFAULT (SELECT 1);    
BEGIN
    RETURN TABLE(res);
END;

creates happy and now we call it:

call so_sp_example();

also very happy.

now lets try make errors, my first guess is your output table is not the same shape as the table you declare in the procedure, so lets try that:

CREATE OR REPLACE PROCEDURE so_sp_example2()
RETURNS TABLE (val number, missing text)
LANGUAGE SQL
STRICT
EXECUTE AS OWNER
AS 
DECLARE
    res RESULTSET DEFAULT (SELECT 1);    
BEGIN
    RETURN TABLE(res);
END;

and call

call so_sp_example2();

Stored procedure execution error: data type of returned table does not match expected returned table type

so there is you error message..

now looking at your procedure yuo have 15 columns in the expected output, both are 15, so I would remove the procedure, and look at the types of the SQL and check they match your return type values.