There are special use cases where you need to enable the parameter Macros= true. Let's take a look at one such use case below :
When to use
If your SQL query is in such a case that you want to execute the inner query first so that it can act as input to the outer query, then you need to use this parameter. Basically, if the inner query is independent then you need to use this option. Please see a sample SQL below :
select 'ABC' as col3, id from testtable a where a.id = '{sql-query:dataSource=SQLSERVER|table=false}select id FROM testtable WHERE id = 347878201 {sql-query}'
When NOT to use
There are certain cases where your inner query expects value/input from the outer query. In these cases, you should not use this parameter. Please see the sample SQL below:
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
The query first executes and generates output
||directory_id ||JiraVal || |1 |{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"= 1 {sql-query} |
The option output = wiki will render the inner SQL query and thereby produces the final output.
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"= 1