1. Basic Building Block

A SQL report consists of an SQL code with the following content:

SELECT

samples.name AS 'Sample',

samples.description AS 'Description'

FROM samples

WHERE samples.visible_to != 'deleted'

ORDER BY samples.name DESC;





2. Elements

The following elements are commonly used:



SELECT: Columns

AS: Column-Description (optional)

FROM: Data source table

WHERE: Conditions

ORDER BY: Sorting



3. Query Building Blocks

You can use the following query building blocks in your SQL reports:



WHERE samples.visible_to != 'deleted' Excludes deleted samples.



WHERE samples.date BETWEEN FROMDATE AND TILLDATE Allows the entire evaluation to be filtered by date ranges (laboratory database internal variable, see instructions on Variablen in Auswertungen). The filter is displayed above the SQL Report.









ORDER BY samples.date DESC Sorts in descending (DESC) order by sample date (or ASC for ascending).



For more examples of evaluations, refer to: Evaluations -> Examples



4. JOIN

To join multiple tables together, you need a JOIN.

code>SELECT FROM oldTabs

…

JOIN newTabs ON ordTabs.id = newTabs.ordTab_id;



Note: In the LAB DATABASE, all table names are in plural (Tables), while column names are mostly in singular (Table). This makes it easier to distinguish between table names and column names.



Here is a small overview of which tables are linked for the areas: Order-Sample-Result-Parameter-Specification.









5. Link

To obtain links in the result table, use the laboratory database's internal linking system. For example, to link to the sample details view, use:



CONCAT('link==../samples/view/', samples.id, '==', samples.name) as 'Samples'

../samples/view/' = LDB-internal Link

samples.id=Part of the link

samples.name = Display value

'Proben' =Column header



6. Linked Master Data Fields

For example, if a master data field in samples (DDF_CUSTOMER) is linked to customer numbers, use the following SELECT/JOIN:

customers.name as 'customer'

FROM samples

JOIN customers ON samples.DDF_KUNDE = customers.id

7. Functions