This article describes the use case of a SQL database that has two tables called "Zone" and "Rules" tables. Users are allowed to select a "zone_id" from the zone table and then use the "zone_id" to as a foreign key to select the rules associated with that "zone_id". The primary key in the zone table is Zone_id and the foreign key in rules table is "Demog".
Instructions
This example makes use of the below-given apps.
The below groovy code provides users with a form that has a select list dropdown to select the user choice value of zone id. This zone id is then passed to the rules table which is displayed upon hitting the Run Button.
{groovy:output=wiki}
import com.atlassian.renderer.v2.RenderMode
def choiceSql = "select distinct concat(zone_id, ':', zone_id , ':') from zone"
def choiceMacro = "{sql-query:datasource=yoga-sql|table=false} ${choiceSql} {sql-query}"
def choices = subRenderer.render(choiceMacro, context, RenderMode.suppress(RenderMode.F_FIRST_PARA))
def runMacro = """
{run:id=dynamic|autorun=true|replace=report:2010::select::${choices.replaceAll(" ","::")}}
{sql-query:datasource=yoga-sql}
select * from rules where demog = \$report
{sql-query}
{run}
"""
out.println runMacro
{groovy}
The below groovy code adds row formatting on the SQL output using CSS in SQL for Confluence
{groovy:output=wiki}
import com.atlassian.renderer.v2.RenderMode
def choiceSql = "select distinct concat( zone_id,':', zone_id,':') from zone ;"
def choiceMacro = "{sql-query:datasource=yoga-sql|table=false} ${choiceSql} {sql-query}"
def choices = subRenderer.render(choiceMacro, context, RenderMode.suppress(RenderMode.F_FIRST_PARA))
def runMacro = """
{run:id=dynamic|autorun=true|replace=report:2010::select::${choices.replaceAll(" ","::")} |help=@self|heading = For the desired zone, please choose the respective zone ID from the table shown above}
{sql-query:datasource=yoga-sql|rowStyles= ,background-color:lightblue,}
select * from rules where demog = '\$report';
{sql-query}
{run}
"""
out.println runMacro
{groovy}
Modify the SQL data source, table names, and columns as per the instance.