...
The following example demonstrates a number of advanced techniques using this approach. It uses 2 inner SQL Query macros who's query depends on data provided by the outer query. Although not shown here, this technique can drive a query from another data source just as easily as from the same data source. Those queries can produce tables, as illustrated here, or a single value by using table=false in the wiki markup for the inner query.
Example
Result View
Edit View
SQL Text
| Code Block | ||||
|---|---|---|---|---|
| ||||
select
'{sql-query:dataSource=exampleDataDS|output=wiki|rowOrientation=horizontal}
select
"Customer Number" as "Number"
, "Customer Name" as "Name"
, "Country"
, "Credit Limit"
, (select employees."First Name" || '' '' || employees."Last Name" from employees where customers."Sales Rep Employee Number" = employees."Employee Number") as "Sales Rep"
from customers
where customers."Customer Number" = ' || orders."Customer Number" || ' {sql-query}'
as "Customer"
, date("Order Date") as "Order Date"
, date("Required Date") as "Required Date"
, date("Shipped Date") as "Shipped Date"
, case
when "Status" = 'Cancelled' then '{status:color=red|title=' || "Status" || '}'
when "Status" = 'Shipped' then '{status:color=green|title=' || "Status" || '}'
else '{status:color=yellow|title=' || "Status" || '}'
end as "Status"
, "Order Number"
, '{sql-query:dataSource=exampleDataDS|autoTotal=true|autoNumber=true|columnTypes=S,I,S,F|columnAttributes=,,style=text-align: right}
select
"Product Code" as "Product"
, "Quantity Ordered" as "Quantity"
, "Unit Price"
, round("Quantity Ordered" * "Unit Price", 2) as "Total Price"
from orderdetails where orderdetails."Order Number" = ' || orders."Order Number" || ' order by orderdetails."Order Line Number"{sql-query}' as "Order Details"
from orders
order by "Order Date" desc
limit 75 |
...