Good day Ganga and welcome to the QnA forums,
I see a few issues in your code which are not related to your original question. Let's start with your question, but keep reading for better query in the future.
The SELECT statement is not allowed in user-defined functions
Yep! azure synapse does not support reference any database data, and it is not related of using SELECT inside the function or other ways.
You can see it well documented here: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-function-sql-data-warehouse?view=aps-pdw-2016-au7#arguments
The function_body cannot contain a SELECT statement and cannot reference database data. The function_body cannot reference tables or views.
This behavior is different then SQL Server.
Since you did not provided the DDL+DML in order to reproduce the issue, we can only give you general solution and you will need to implement it yourself - or provide the missing information if not succeed.
Optional workaround the limitation
(1) You can reference any database data (execute the select query) before you call the function and use the values as input parameters in the function.
(2) You can use CROSS APPLY if you want to use data from table(s) as input to a function which is used inline with a query.
Back to the start, and some more points
(1) Using SELECT as input to a scalar variable is huge problematic since SELECT returns a SET of records and not a single one. Most database server "know" how to by pass this poor coding by using their interpretation (for example using the first or the last row returned from the query). If this approach is a must and you know by sure that your query can return only one row then it is recommended to use "top 1" to avoid issues.
(2) Your function supposed to return AVRCHAR(4)
according to your definition RETURNS VARCHAR(4)
, but you declares @ret_yr varchar(12)
which mean that returns VARCHAR(12)
. Moreover! If the condition of the IF is not TRUE then you function return INTEGER RETURN 0
. This make no sense.