topic Re: How can i pass one of the values from one function to another as an argument in Databricks SQL? in Data Engineering

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

Sudeshna — Tue, 29 Mar 2022 18:27:57 GMT

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?

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

Hubert-Dudek — Tue, 29 Mar 2022 22:18:17 GMT

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

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

Sudeshna — Wed, 30 Mar 2022 08:21:50 GMT

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