create scalar function

Question

create scalar function

Shambhu Rai 1,411

Hi Expert,

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

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

Shambhu Rai 1,411 Reputation points

2023-12-05T10:35:21.5733333+00:00

suggestion pls
Saravanan Ganesan 1,830 Reputation points MVP

2023-12-05T11:12:47.7533333+00:00
Hi Shambhu,

In Databricks, you can craft User-Defined Functions (UDFs) using either Python or Scala. Here's an example of how to seamlessly transform your SQL Server scalar function into a Databricks UDF using Python.

If you have a Spark DataFrame in your Databricks notebook, you can create a Python UDF to transform your data.

from pyspark.sql.functions import udf from pyspark.sql.types import IntegerType # Define the UDF function def gettrans_udf(PickupCompany, SupplyCountry, TxnSource, locId, ExternalSiteId): worldSiteid = 0 if TxnSource == 'CDX': #logic of your code pass return worldSiteid # Register the UDF gettrans_spark_udf = udf(gettrans_udf, IntegerType()) # Assuming you have a DataFrame df, you can use the UDF like this result_df = df.withColumn("result_column", gettrans_spark_udf("PickupCompany", "SupplyCountry", "TxnSource", "locId", "ExternalSiteId"))

The above is just a starting point, and you may need to adjust it depending on your data and needs. If you're using Scala instead of Python, you can create a Scala UDF in a similar way.

If you found my answer useful kindly do upvote and click "accept the answer", still you have queries kindly do connect me here.

Regards,

Saravanan Ganesan.

Shambhu Rai 1,411

Hi Expert,

sorry icould not copy complete query... can give some example for multiple if and elseif condition and select condition



      
      
      
CREATE function [dbo].[ufnGetGlobslSiteIdForTxns](@PickupCompany nvarchar(2), @SupplyCountry int, @TxnSource nvarchar(10),       
@SiteId nvarchar(50), @ExternalSiteId nvarchar(50))      
RETURNS INT      
AS      
      
BEGIN      
  DECLARE @GlobalSiteid INT      
  SET @GlobalSiteid = 0      
      
If (@TxnSource = 'CDX')      
BEGIN      
     
IF (@PickupCompany = 'BP' and @SupplyCountry in (3,4,6,7,8,10,11,14,15,29,50,53)) or      
 (@PickupCompany in ('AR','AS','CT','ES','GU','GV','JU','OT','TX', 'MP'))      
        
Select top 1 @GlobalSiteid = GlobalSiteid from Sitestore ss (nolock)       
left join country c (nolock) on ss.CountryAlphaCode = c.CountryISOAlphaCode      
left join sitedetails sd (nolock) on ss.ESPRetailSiteMNMC = sd.ESPRetailSiteMNMC and ss.countryAlphaCode = sd.countryAlphaCode      
where ss.SiteId = @SiteId and c.CountryCode = @SupplyCountry      
order by 1 desc      
      
    
Else IF (@PickupCompany = 'BP' and @SupplyCountry in (2,12,19)) or      
 (@PickupCompany in ('AP','OM','RL','ST','WE','TR'))      
        
Select top 1 @GlobalSiteid = GlobalSiteid from sitedetails sd (nolock)       
left join country c (nolock) on sd.CountryAlphaCode = c.CountryISOAlphaCode      
where --sd.ESPRetailSiteMNMC = @ExternalSiteId       
SUBSTRING(sd.ESPRetailSiteMNMC, PATINDEX('%[^0]%', sd.ESPRetailSiteMNMC), LEN(sd.ESPRetailSiteMNMC)) = SUBSTRING(@SiteId, PATINDEX('%[^0]%', @SiteId), LEN(@SiteId)) --Removing prefixing zeroes      
and c.CountryCode = @SupplyCountry      
and sd.LegalOwner = @PickupCompany      
order by 1 desc      
      
      
Else IF (@PickupCompany = 'BP' and @SupplyCountry in (5))       
        
Select top 1 @GlobalSiteid = GlobalSiteid from sitedetails sd (nolock)       
left join country c (nolock) on sd.CountryAlphaCode = c.CountryISOAlphaCode      
where c.CountryCode = @SupplyCountry and (wherecode = @SiteId or ObjectCode = @SiteId)      
order by 1 desc      
      
   
Else IF (@PickupCompany = 'TO')       
        
Select top 1 @GlobalSiteid = GlobalSiteid from Sitestore ss (nolock)       
left join country c (nolock) on ss.CountryAlphaCode = c.CountryISOAlphaCode      
left join sitedetails sd (nolock) on ss.ESPRetailSiteMNMC = 'TO' + sd.ESPRetailSiteMNMC and ss.countryAlphaCode = sd.countryAlphaCode      
where ss.SiteId = @SiteId and c.CountryCode = @SupplyCountry      
order by 1 desc      
      
     
Else IF (@PickupCompany in ('AV','DK','Q8'))      
        
Select top 1 @GlobalSiteid = GlobalSiteid from sitedetails sd (nolock)       
left join country c (nolock) on sd.CountryAlphaCode = c.CountryISOAlphaCode      
where --sd.ESPRetailSiteMNMC = @ExternalSiteId       
SUBSTRING(sd.ESPRetailSiteMNMC, PATINDEX('%[^0]%', sd.ESPRetailSiteMNMC), LEN(sd.ESPRetailSiteMNMC)) = SUBSTRING(@ExternalSiteId, PATINDEX('%[^0]%', @ExternalSiteId), LEN(@ExternalSiteId)) --Removing prefixing zeroes      
and c.CountryCode = @SupplyCountry      
and sd.LegalOwner = @PickupCompany      
order by 1 desc      
      
END      
      
ELSE If (@TxnSource = 'OAC')      
BEGIN      
    
IF (@PickupCompany = 'TO')      
      
select top 1 @GlobalSiteid = s.GlobalSiteid from sitedetails s (nolock)       
left join SiteFepDetails f on s.globalsiteid = f.globalsiteid      
left join country c (nolock) on s.CountryAlphaCode = c.CountryISOAlphaCode      
where FepSiteId = TRIM(@SiteId) and c.CountryCode = @SupplyCountry       
order by 1 desc      
      
ELSE      
     
      
select top 1 @GlobalSiteid = s.GlobalSiteid from sitedetails s (nolock)       
left join SiteFepDetails f on s.globalsiteid = f.globalsiteid      
left join country c (nolock) on s.CountryAlphaCode = c.CountryISOAlphaCode      
where FepSiteId = TRIM(RIGHT(@SiteId, LEN(@SiteId) - 4)) and c.CountryCode = @SupplyCountry       
order by 1 desc      
      
END  
ELSE IF (@TxnSource = 'HEROES')   
BEGIN  
 SELECT  TOP 1 @GlobalSiteid = S.GlobalSiteId  
   FROM  SiteDetails(NOLOCK) S  
  WHERE  SUBSTRING(TRIM(@SiteId), PATINDEX('%[^0]%', TRIM(@SiteId)), LEN(TRIM(@SiteId))) = SUBSTRING(TRIM(ESPRetailSiteMNMC), PATINDEX('%[^0]%', TRIM(ESPRetailSiteMNMC)), LEN(TRIM(ESPRetailSiteMNMC)))  
  ORDER BY 1 DESC  
END  
      
RETURN @GlobalSiteid;      
END 
GO

Shambhu Rai 1,411 Reputation points

2023-12-07T09:43:47.9666667+00:00

suggestion pls

Saravanan Ganesan 1,830 MVP

Hi Shambhu,

kindly find the below databricks function for the above given SQL query.

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("example").getOrCreate()

# Define a UDF (User Defined Function) using Spark SQL
@udf
def ufnGetGlobalSiteIdForTxns(PickupCompany, SupplyCountry, TxnSource, SiteId, ExternalSiteId):
    GlobalSiteid = 0

    if TxnSource == 'CDX':
        if (PickupCompany == 'BP' and SupplyCountry in (3, 4, 6, 7, 8, 10, 11, 14, 15, 29, 50, 53)) or \
                (PickupCompany in ('AR', 'AS', 'CT', 'ES', 'GU', 'GV', 'JU', 'OT', 'TX', 'MP')):
            # Your CDX logic here
            pass
        elif (PickupCompany == 'BP' and SupplyCountry in (2, 12, 19)) or \
                (PickupCompany in ('AP', 'OM', 'RL', 'ST', 'WE', 'TR')):
            # Your second CDX logic here
            pass
        elif (PickupCompany == 'BP' and SupplyCountry == 5):
            # Your third CDX logic here
            pass
        elif PickupCompany == 'TO':
            # Your fourth CDX logic here
            pass
        elif PickupCompany in ('AV', 'DK', 'Q8'):
            # Your fifth CDX logic here
            pass

    elif TxnSource == 'OAC':
        if PickupCompany == 'TO':
            # Your OAC TO logic here
            pass
        else:
            # Your OAC logic here
            pass

    elif TxnSource == 'HEROES':
        # Your HEROES logic here
        pass

    return GlobalSiteid

# Assuming you have a DataFrame named df
df = df.withColumn("GlobalSiteid", ufnGetGlobalSiteIdForTxns("PickupCompany", "SupplyCountry", "TxnSource", "SiteId", "ExternalSiteId"))

This code defines a User Defined Function (UDF) using PySpark's udf decorator. You can then apply this UDF to a DataFrame using the withColumn method. Make sure to replace "df" with your actual DataFrame name.

If you found my answer useful kindly do upvote and click "accept the answer", still you have queries kindly do connect me here.

Regards,

Saravanan Ganesan.

Shambhu Rai 1,411 Reputation points

2023-12-07T15:45:46.0566667+00:00

its gives an error

AnalysisException: [UNRESOLVED_ROUTINE] Cannot resolve function ufnGetGlobslSiteIdForTxns on search path

Shambhu Rai 1,411

when i am trying to use this in merge statement is not working

AnalysisException: [UNRESOLVED_ROUTINE] Cannot resolve function ufnGetGlobslSiteIdForTxns on search path [system.builtin, system.session, spark_catalog.default].; line 3 pos 19


MERGE into transactions.Transactions_Crdx AS TARGET
 USING (SELECT H100.*,     
  GLOBAL_SITE_ID = ufnGetGlobslSiteIdForTxns("H100.H100_PICKUP_COMPANY","H100.H100_SUPPLY_COUNTRY",'CDX',"H100.HT_GLOPS_LOS_NO","H100.H100_EXTERNAL_LOS_NO")
  FROM hist100_tempview AS H100) AS SOURCE
 ON (TARGET.TxnGUID = SOURCE.TxnGUID)
 WHEN MATCHED THEN
  UPDATE     
     SET RecordMOdifiedDate  = GETDATE(),

Shambhu Rai 1,411 Reputation points

2023-12-08T10:43:10.93+00:00

suggestion pls
Shambhu Rai 1,411 Reputation points

2023-12-08T17:26:13.86+00:00

sugggestion pls
Shambhu Rai 1,411 Reputation points

2023-12-09T23:53:05.0566667+00:00

suggestion pls
ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2023-12-18T09:33:00.13+00:00

Hi Shambhu Rai, Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

1 answer

Your answer

Shambhu Rai 1,411 Reputation points

2023-12-05T10:35:21.5733333+00:00

suggestion pls
Saravanan Ganesan 1,830 Reputation points MVP

2023-12-05T11:12:47.7533333+00:00

Hi Shambhu,

In Databricks, you can craft User-Defined Functions (UDFs) using either Python or Scala. Here's an example of how to seamlessly transform your SQL Server scalar function into a Databricks UDF using Python.

If you have a Spark DataFrame in your Databricks notebook, you can create a Python UDF to transform your data.

from pyspark.sql.functions import udf from pyspark.sql.types import IntegerType # Define the UDF function def gettrans_udf(PickupCompany, SupplyCountry, TxnSource, locId, ExternalSiteId): worldSiteid = 0 if TxnSource == 'CDX': #logic of your code pass return worldSiteid # Register the UDF gettrans_spark_udf = udf(gettrans_udf, IntegerType()) # Assuming you have a DataFrame df, you can use the UDF like this result_df = df.withColumn("result_column", gettrans_spark_udf("PickupCompany", "SupplyCountry", "TxnSource", "locId", "ExternalSiteId"))

The above is just a starting point, and you may need to adjust it depending on your data and needs. If you're using Scala instead of Python, you can create a Scala UDF in a similar way.

If you found my answer useful kindly do upvote and click "accept the answer", still you have queries kindly do connect me here.

Regards,

Saravanan Ganesan.
Shambhu Rai 1,411 Reputation points

2023-12-07T09:43:47.9666667+00:00

suggestion pls
Saravanan Ganesan 1,830 Reputation points MVP

2023-12-07T13:38:29.23+00:00

Hi Shambhu,

kindly find the below databricks function for the above given SQL query.

from pyspark.sql import SparkSession spark = SparkSession.builder.appName("example").getOrCreate() # Define a UDF (User Defined Function) using Spark SQL @udf def ufnGetGlobalSiteIdForTxns(PickupCompany, SupplyCountry, TxnSource, SiteId, ExternalSiteId): GlobalSiteid = 0 if TxnSource == 'CDX': if (PickupCompany == 'BP' and SupplyCountry in (3, 4, 6, 7, 8, 10, 11, 14, 15, 29, 50, 53)) or \ (PickupCompany in ('AR', 'AS', 'CT', 'ES', 'GU', 'GV', 'JU', 'OT', 'TX', 'MP')): # Your CDX logic here pass elif (PickupCompany == 'BP' and SupplyCountry in (2, 12, 19)) or \ (PickupCompany in ('AP', 'OM', 'RL', 'ST', 'WE', 'TR')): # Your second CDX logic here pass elif (PickupCompany == 'BP' and SupplyCountry == 5): # Your third CDX logic here pass elif PickupCompany == 'TO': # Your fourth CDX logic here pass elif PickupCompany in ('AV', 'DK', 'Q8'): # Your fifth CDX logic here pass elif TxnSource == 'OAC': if PickupCompany == 'TO': # Your OAC TO logic here pass else: # Your OAC logic here pass elif TxnSource == 'HEROES': # Your HEROES logic here pass return GlobalSiteid # Assuming you have a DataFrame named df df = df.withColumn("GlobalSiteid", ufnGetGlobalSiteIdForTxns("PickupCompany", "SupplyCountry", "TxnSource", "SiteId", "ExternalSiteId"))

This code defines a User Defined Function (UDF) using PySpark's udf decorator. You can then apply this UDF to a DataFrame using the withColumn method. Make sure to replace "df" with your actual DataFrame name.

If you found my answer useful kindly do upvote and click "accept the answer", still you have queries kindly do connect me here.

Regards,

Saravanan Ganesan.
Shambhu Rai 1,411 Reputation points

2023-12-07T15:45:46.0566667+00:00

its gives an error

AnalysisException: [UNRESOLVED_ROUTINE] Cannot resolve function ufnGetGlobslSiteIdForTxns on search path
Shambhu Rai 1,411 Reputation points

2023-12-07T16:13:40.4666667+00:00

when i am trying to use this in merge statement is not working

AnalysisException: [UNRESOLVED_ROUTINE] Cannot resolve function ufnGetGlobslSiteIdForTxns on search path [system.builtin, system.session, spark_catalog.default].; line 3 pos 19

MERGE into transactions.Transactions_Crdx AS TARGET USING (SELECT H100.*, GLOBAL_SITE_ID = ufnGetGlobslSiteIdForTxns("H100.H100_PICKUP_COMPANY","H100.H100_SUPPLY_COUNTRY",'CDX',"H100.HT_GLOPS_LOS_NO","H100.H100_EXTERNAL_LOS_NO") FROM hist100_tempview AS H100) AS SOURCE ON (TARGET.TxnGUID = SOURCE.TxnGUID) WHEN MATCHED THEN UPDATE SET RecordMOdifiedDate = GETDATE(),
Shambhu Rai 1,411 Reputation points

2023-12-08T10:43:10.93+00:00

suggestion pls
Shambhu Rai 1,411 Reputation points

2023-12-08T17:26:13.86+00:00

sugggestion pls
Shambhu Rai 1,411 Reputation points

2023-12-09T23:53:05.0566667+00:00

suggestion pls
ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2023-12-18T09:33:00.13+00:00

Hi Shambhu Rai, Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Answer 1

ShaikMaheer-MSFT 38,551 Microsoft Employee Moderator

Hi Shambhu Rai,

You are getting error as Cannot resolve function ufnGetGlobslSiteIdForTxns

It seems you are calling python function ufnGetGlobslSiteIdForTxns before it got created. In Python language we need to create function first and then only we can make a call for it.

Please check below video to understand more.

Functions in Python

Hope this helps. Please let me know if any further queries.

Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.

ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2023-12-15T07:22:25.18+00:00

Hi Shambhu Rai, Just checking if above answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.
Shambhu Rai 1,411 Reputation points

2023-12-21T12:16:56.7933333+00:00

Sir, please read the question and then answer . All that things is done but not working simply writing one line answer will not help.. write in above code
ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2023-12-27T06:50:46.27+00:00

Cannot resolve function ufnGetGlobslSiteIdForTxns
Above error, actually denotes that you are calling function before its gets created. Kindly recheck once. surely, function call is happening before function getting created.
Shambhu Rai 1,411 Reputation points

2023-12-27T06:59:44.05+00:00

Sir, again. Please read the question then ans

Share via

create scalar function

1 answer

Your answer