How to add queries within queries - 10.x

Description

To improve how you show data to viewers, you can employ some advanced techniques by including queries within other queries. This also allows you to incorporate data from multiple data sources into a single table.

The example on this page demonstrates a number of advanced techniques using this approach. The example uses two inner SQL Query macros with queries that depends on the 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

SQL Query

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