How can i pass one of the values from one function...

Sudeshna · ‎03-29-2022

For eg -

CREATE OR REPLACE TABLE table2(a INT, b INT);

INSERT INTO table2 VALUES (100, 200);

CREATE OR REPLACE FUNCTION func1() RETURNS TABLE(a INT, b INT) RETURN

(SELECT a+b, a*b from table2);

create or replace function calc(p DOUBLE) RETURNS TABLE(val DOUBLE) RETURN (SELECT a from func1());

select calc(a) from func1();

This throws me an error -

Now, i understand the way i am trying to do this is wrong. Is there any way to do this? If so, then how?

Hubert-Dudek · ‎03-29-2022

Yes, it is possible, but with different logic. For scalar, so calc(a) in select calc(a) from func1(); it can only be a query as a table for a scalar is not allowed. So please try something like:

CREATE OR REPLACE FUNCTION func_table() RETURNS TABLE(a INT, b INT) RETURN (SELECT a+b, a*b from table2);
 
CREATE OR REPLACE FUNCTION func_multiply(p INT) RETURNS INT RETURN SELECT p * 2;
 
select func_multiply(a), a FROM func_table();

My blog: https://databrickster.medium.com/

Sudeshna · ‎03-30-2022

@Hubert Dudek , How will it work if func_multiply() also returns a table? Can i write such a select statement in that case?

How can i pass one of the values from one function to another as an argument in Databricks SQL?