LibreOffice logo
BASEDOCUMENTER
The software tool for documenting your LibreOffice Base applications
 
Database file/home/jean-pierre/Documents/BaseDocumenter/www/databases/NorthWind/TT NorthWind.odb
File actual save date2018-08-01 13:54:50
Scanning done on2018-08-21 17:50:26
Documentation generated on2018-08-21 17:50:37
Table of contents
NorthWind
List of queries
Query name Used by Query type Direct SQL List of fields SQL
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"