| CrossTab |
|
SELECT |
✔ |
Details |
SELECT
CASE WHEN "Country" IS NULL THEN '' ELSE "Country" END As "Country",
CASE WHEN "Name" IS NULL THEN '' ELSE "Name" END As "Name",
SUM(
CASE "Year" WHEN '1996' THEN "Count" ELSE 0 END
) As "1996",
SUM(
CASE "Year" WHEN '1997' THEN "Count" ELSE 0 END
) As "1997",
SUM(
CASE "Year" WHEN '1998' THEN "Count" ELSE 0 END
) As "1998",
SUM("Count") As "All"
FROM
(
SELECT
"Country",
"FirstName" || ' ' || "LastName" As "Name",
YEAR("OrderDate") As "Year",
COUNT(*) As "Count"
FROM
"Employees"
INNER JOIN "Orders" ON (
"Employees"."EmployeeID" = "Orders"."EmployeeID"
)
GROUP BY
"Country",
"Name",
"Year")
GROUP BY
"Country",
"Name"
ORDER BY
"All" DESC |
| Customers union All |
CreateRecordsetFromQuery (Procedure) |
UNION |
✔ |
Details |
SELECT
"CompanyName"
FROM
"Customers"
UNION
SELECT
'(All)' as "Bogus"
From
"Customers"
ORDER BY
"CompanyName"; |
| EmployeesList |
Main (Procedure) |
SELECT |
|
Details |
SELECT
'<a id="' || "Employees"."EmployeeID" || '">' || "Employees"."LastName" || '' AS "Last name",
"Employees"."FirstName" AS "First name",
"Employees"."BirthDate" AS "Birth",
"Employees"."Extension" AS "Phone",
"Employees"."HomePhone" AS "Home phone",
"Employees"."Title",
'<a href="#' || "ReportingTo"."EmployeeID" || '">' || "ReportingTo"."LastName" || '' AS "Manager"
FROM
{ OJ "Employees" AS "ReportingTo"
RIGHT OUTER JOIN "Employees" ON "ReportingTo"."EmployeeID" = "Employees"."ReportsTo" } |
| Orders_CalculatedField |
|
SELECT |
|
Details |
SELECT
"Orders"."OrderID",
"Orders"."OrderDate",
"Orders"."ShipAddress",
"Orders"."ShipCity",
SUM(
"Order Details"."UnitPrice" * "Order Details"."Quantity" * (
1 - "Order Details"."Discount"
)) AS "SumOfDetails"
FROM
"Order Details",
"Orders"
WHERE
"Order Details"."OrderID" = "Orders"."OrderID"
GROUP BY
"Orders"."OrderID",
"Orders"."OrderDate",
"Orders"."ShipAddress",
"Orders"."ShipCity" |
| Query1 |
Main (Procedure) |
SELECT |
✔ |
Details |
SELECT
"Customer",
"Name",
SUM(
CASE "Quarter" WHEN '1997Q1' THEN "Data" ELSE 0 END
) As "1997Q1",
SUM(
CASE "Quarter" WHEN '1997Q2' THEN "Data" ELSE 0 END
) As "1997Q2",
SUM(
CASE "Quarter" WHEN '1997Q3' THEN "Data" ELSE 0 END
) As "1997Q3",
SUM(
CASE "Quarter" WHEN '1997Q4' THEN "Data" ELSE 0 END
) As "1997Q4",
SUM("Data") As "All"
FROM
(
SELECT
"Customers"."CompanyName" As "Customer",
"Products"."ProductName" AS "Name",
YEAR("OrderDate") || 'Q' || QUARTER("OrderDate") As "Quarter",
SUM(
"Order Details"."UnitPrice" * "Quantity" *(1 - "Discount")) AS "Data"
FROM
"Order Details",
"Products",
"Orders",
"Customers"
WHERE
"Order Details"."ProductID" = "Products"."ProductID"
AND "Order Details"."OrderID" = "Orders"."OrderID"
AND "Customers"."CustomerID" = "Orders"."CustomerID"
AND YEAR("Orders"."OrderDate") = 1997
GROUP BY
"Customer",
"Name",
"Quarter")
GROUP BY
"Customer",
"Name"
ORDER BY
"Customer" |
