Execute Immediate not working to fetch table name based on year
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
08-26-2025 02:41 AM
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.
