Workflow parameter in sql not working

StevenW
New Contributor III

‎10-08-2024 08:23 AM

I'm using the following input parameters when running from a workflow:

wid_UnityCatalogName = dbutils.jobs.taskValues.get(taskKey="NB_XXX_Workflow_Parameters", key="p_UnityCatalogName", default="xx_lakehouse_dev")
        dbutils.widgets.text("UnityCatalogName", wid_UnityCatalogName)
               
wid_UnityCatalogBronzeJDESchema = dbutils.jobs.taskValues.get(taskKey="NB_XXX_Workflow_Parameters", key="p_UnityCatalogBronzeXXXSchema", default="bronze_xxx")
        dbutils.widgets.text("UnityCatalogBronzeJDESchema", wid_UnityCatalogBronzeJDESchema)
 
And then I run various SQL statements that use these parameters successfully.
On step 16 it fails..
create or replace temp view f0010_view as
select trim(b.ccco) as ccco
       ,trim(b.ccname) as ccname
       ,trim(b.cccrcd) as cccrcd
       , cast(b.CCDFYJ as Int) as CCDFYJ
       --,${UnityCatalogName}.${UnityCatalogBronzeJDESchema}.f_Convert_JDEJulian_ToDate(cast(b.CCDFYJ as Int)) as FISCAL_YEAR_START_DT
       ,${UnityCatalogName}.${UnityCatalogBronzeJDESchema}.f_Convert_JDEJulian_ToDate(cast(b.CCDFYJ as Int)) as FISCAL_YEAR_START_DT
       ,b.CCPNC as CCPNC
 
ERROR:
[PARSE_SYNTAX_ERROR] Syntax error at or near '.'.(line 7, pos 😎 == SQL == create or replace temp view f0010_view as select trim(b.ccco) as ccco ,trim(b.ccname) as ccname ,trim(b.cccrcd) as cccrcd , cast(b.CCDFYJ as Int) as CCDFYJ --,..f_Convert_XXXJulian_ToDate(cast(b.CCDFYJ as Int)) as FISCAL_YEAR_START_DT ,..f_Convert_JDEJulian_ToDate(cast(b.CCDFYJ as Int)) as FISCAL_YEAR_START_DT --------^^^ ,b.CCPNC as CCPNC ,b.CCCALD as CCCALD ,NOP.DESCRIPTION as NORMAL_NO_OF_PERIODS_DESC
 
The ",..f_Convert_XXXJulian_ToDate" is supposed to "xx_lakehouse_dev.bronze_xxx.f_Convert_XXXJulian_ToDate"
 
Steps 12, 13 & 14 all executed successfully with these same parameters.
 
Any idea what this madness is?
 
Thanks,
       
0 Kudos