Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Div
idProductTOC

On this page

Table of Contents
maxLevel2
excludeOn this page
typeflat
separatorpipe

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.

...

Info

To know more about SQL Query macros, click the relevant version:

Example

Result

...

view

Edit

...

view

SQL

...

text

Code Block
languagesql
titleSQL 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

...