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

[Sudeshna]

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]

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();

 

[Sudeshna]

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