Skip to end of banner
Go to start of banner

Add queries within queries - 10.x

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 3 Next »

On this page

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 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

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
  • No labels