topic Re: scalar function in databricks in Get Started Discussions

scalar function in databricks

Shree23 — Mon, 04 Dec 2023 20:10:25 GMT

Hi Expert,

here is sql server scalar function how to convert in databricks function

SQL
CREATE function [dbo].[gettrans](@PickupCompany nvarchar(2),
@SupplyCountry int, @TxnSource nvarchar(10),
@locId nvarchar(50), @ExternalSiteId nvarchar(50))
RETURNS INT
AS

BEGIN
DECLARE @worldSiteid INT
SET @worldSiteid = 0

If (@TxnSource = 'CDX')
BEGIN

Re: scalar function in databricks

Shree23 — Tue, 05 Dec 2023 07:17:51 GMT

Suggestion pls

Re: scalar function in databricks

MathieuDB — Fri, 22 Nov 2024 15:15:08 GMT

Hello @Shree23 ,
In Databricks, you can create scalar or tabular functions using SQL or Python. Here is the documentation .
I converted your SQL Server function to Databricks standards.

CREATE OR REPLACE FUNCTION gettrans( PickupCompany STRING, SupplyCountry INT, TxnSource STRING, locId STRING, ExternalSiteId STRING ) RETURNS INT RETURN ( SELECT CASE WHEN TxnSource = 'CDX' THEN ( -- Add business logic here 0 ) ELSE 1 END ); SELECT gettrans('Purolator', 'CAN', 'CHI', '123', 'MAIN') as transactions;

As you can see, the syntax is pretty straightforward. The datatype can be INT, STRING, ARRAY, DECIMAL...
The variables don't need to be prefixed by anything (@). The rest of the logic is pretty similar to what you will find in SQL Server.

Hope this helps!