I have below code which is used as part of a WHERE clause and its causing the 2620 error.
Expect the variables used to have values like:
SyntaxEditor Code Snippet
CAST((EXTRACT(YEAR FROM VMLA.LOCAL_DATE_KEY)||DD.WK_NBR_IN_YEAR) AS INT)=CAST('2016'||'23'AS INT)
Please suggest what is the cause for 2620 error and how to fix it.
Look at the result of Extract(YEAR From LOCAL_DATE_KEY)||WK_NBR_IN_YEAR, it's not '201623' but ' 2016 23' (due to automatics typecasts applying a Format with leading spaces).
Cast((Trim(Extract(YEAR From LOCAL_DATE_KEY))||Trim(WK_NBR_IN_YEAR)) AS INT)
or better avoid cast to string and back to int using
Extract(YEAR From LOCAL_DATE_KEY) * 100 + WK_NBR_IN_YEAR