cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Sudeshna
New Contributor III

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 -

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

2 REPLIES 2

Hubert-Dudek
Esteemed Contributor III

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

image.png 

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

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group