Limitation of macros=true parameter while using nested SQL queries - 11.x

The macros=true parameter has a limitation when used in nested SQL queries.

For example, consider the following use case where the output of an outer query is fed as an input to the inner SQL query:

select ap.directory_id,
CONCAT('{sql-query:dataSource=SQLSERVER|table=false}
use jiradbnew;
select user_name, directory_id, display_name, email_address, dir.directory_position
from cwd_user usr
join cwd_directory dir on dir."id" = usr."directory_id" and dir."id"= ', ap.DIRECTORY_ID, ' {sql-query}') as "JiraVal"
from cwd_group ap

In this case, if macros is set to true, the inner query executes first, thus, substitution values for directory_id is not available. Therefore, this query is not executed successfully.