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.