How to write ADF dynamic expression with SQL statements in multiple levels

I am trying to write ADF dynamic content for filter activity where I need to filter files, having dates in their name, coming from get metadata activity by multiple conditions. I am writing a SQL query which uses activity output in WHERE condition and then provides a DATE as a output. Then I need to select only the files whose date in name is greater or Equals to the SQL query's output DATE.

Again the files which satisfy above condition I need to make sure their extension is either .csv or .zip.

Basically I have 3 types of filenames like - 'a_01-01-2001.csv','b_01-01-2001.csv','c_01-01-2001.csv'. now same file names also with .zip and .ok(filtered out) for each week. Now these files have dates in their name itself. And I have Control Table in SQLDB which holds last_success_run_date for three of the file(which more likely going to same for all). Now based on this last_success_run_date in SQLDB I have to perform Incremental load on FTP source. So using the SQL statement used above I'm checking if [current Item name] of Filter Activity (Not ForEach) has date in it greaterorequal to last_successful_run_date

For which I am writing the dynamic content but it is resulting in error. I don't understand how to write it properly.

I tried writing this

@and(or(endswith(item().name,'.zip'),endswith(item().name,'.csv')),
greaterOrEquals(formatDateTime(substring(item().name,6,10),'yyyy-MM-dd'),
formatDateTime(
select last_success_run_Date from dbo.ADF_Control_Table 
where FileName = '@{substring(replace(item().name,".zip",""),17, sub(length(item().name), 21))}',
'yyyy-MM-dd'
)))

I am assuming that in the activity where you're filtering files (probably a ForEach activity with an If Condition inside), you need to use its output.

You can use a variable last_success_run_Date as a variable or output from a previous activity :

@and(
    or(
        endswith(item().name, '.zip'), 
        endswith(item().name, '.csv')
    ),
    greaterOrEquals(
        formatDateTime(substring(item().name, 6, 10), 'yyyy-MM-dd'),
        formatDateTime(variables('lastSuccessRunDate'), 'yyyy-MM-dd')
    )
)

Update :

I think you will need a Lookup Activity that queries your database to retrieve the last_success_run_date:

 SELECT last_success_run_date FROM dbo.ADF_Control_Table WHERE FileName = 'your condition here'

Then update the expression like below :

@and(
    or(
        endswith(item().name, '.zip'), 
        endswith(item().name, '.csv')
    ),
    greaterOrEquals(
        formatDateTime(substring(item().name, indexOf(item().name, '_') + 1, 10), 'yyyy-MM-dd'),
        formatDateTime(variables('lastSuccessRunDate'), 'yyyy-MM-dd')
    )
)