Execute Immediate not working to fetch table name based on year

[MR_DHC]

 I am trying to pass the year as argument so it can be used in the table name. 

Ex: there are tables like Claims_total_2021 , Claims_total_2022 and so on till 2025. Now I want to pass the year in parameter , say 2024 and it must fetch the table Claims_total_2024 from database.

this is the code I am trying : 

DECLARE claim_year STRING;

SET claim_year = (

  SELECT CAST(CLAIM_YEAR - 1 AS STRING)

  FROM dbengineering_prod.claimscommercial.clms_commercial_ref_years R

  WHERE R.ANALYSIS_TYPE_ID = 1

    AND R.YEAR_RANK = 1

);

EXECUTE IMMEDIATE

  'SELECT 1

   FROM Claims_total_' || claim_year || '

   LIMIT 10';

so this must query from Claims_total_2024 in the query.

This is throwing the following error : [CONFIG_NOT_AVAILABLE] Configuration claim_year is not available. SQLSTATE: 42K0I

Any suggestion would be appreciated.

[martinson]

Without VAR, the SET command attempts to set a Spark session configuration instead.

DECLARE claim_year STRING;

SET VAR claim_year = (
SELECT CAST(CLAIM_YEAR - 1 AS STRING)
FROM dbengineering_prod.claimscommercial.clms_commercial_ref_years R
WHERE R.ANALYSIS_TYPE_ID = 1
AND R.YEAR_RANK = 1
);

EXECUTE IMMEDIATE
'SELECT 1
FROM `dbengineering_prod.claimscommercial.Claims_total_' || claim_year || '`
LIMIT 10';

SET Variable info-

https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-aux-set-variable

You need to disambiguate this due to the shared syntax between Databricks SQL and Spark, in Spark SET is commonly used in configs.

Info on Spark Configuration 

https://spark.apache.org/docs/latest/configuration.html

[MR_DHC]

Hi Martinson,

Thank you for the response. I tried using VAR and it still throws syntax error at || . so I tried $ it still has same error:

[PARSE_SYNTAX_ERROR] Syntax error at or near '||'. SQLSTATE: 42601 line 3, pos 95

[martinson]

EXECUTE IMMEDIATE requires the parameter to either be a string or a variable. You are trying to pass an expression through, causing the error. I believe that if you set up a SET VAR with the expression and then pass that through, that should solve your issue. Here is an example of what that could look like.

Let me know if that works or the error it returns

DECLARE claim_year STRING;

SET VAR claim_year = (
  SELECT CAST(CLAIM_YEAR - 1 AS STRING)
  FROM dbengineering_prod.claimscommercial.clms_commercial_ref_years R
  WHERE R.ANALYSIS_TYPE_ID = 1
    AND R.YEAR_RANK = 1
);

DECLARE sql_str STRING;

SET VAR sql_str = 'SELECT 1
   FROM `dbengineering_prod.claimscommercial.Claims_total_' || claim_year || '`
   LIMIT 10';

EXECUTE IMMEDIATE sql_str;

[MR_DHC]

Hi Again! 

Sorry to trouble , this is still causing the error: 

[PARSE_SYNTAX_ERROR] Syntax error at or near '$'. SQLSTATE: 42601 == SQL (line 1, position 1) == EXECUTE IMMEDIATE sql_str

[martinson]

Hi MR_DHC! Can you try using '||' instead of '$'?

[Sidhant07]

Hi,

I believe the solution shared by Martison would fix this issue.

In Databricks SQL, when using EXECUTE IMMEDIATE, the SQL string must be a single variable or single string literal, not an inline expression using string concatenation ('...' || claim_year || '...').

https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-aux-execute-immediate

DECLARE claim_year STRING;
SET VAR claim_year = (
SELECT CAST(CLAIM_YEAR - 1 AS STRING)
FROM dbengineering_prod.claimscommercial.clms_commercial_ref_years R
WHERE R.ANALYSIS_TYPE_ID = 1 AND R.YEAR_RANK = 1
);

DECLARE sqlstr STRING;
SET VAR sqlstr = 'SELECT 1 FROM Claims_total_' || claim_year || ' LIMIT 10';

EXECUTE IMMEDIATE sqlstr;