SQL-Fehler bei Checkbox-Kombinationen unter Stammdaten -> Berichte

[mike.constabel@…]

r4110

Stammdaten -> Berichte:

Kunden:

Wenn man in der Auswahl die Häkchen Rechnungen, Aufträge, Angebote kombiniert, also, Beispiel, Häkchen "Rechnungen UND Aufträge UND Angebote" setzt, gibts diesen Fehler:

SELECT ct.*, b.description AS business, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, 'is' AS module, 'invoice' AS formtype, (a.amount = a.paid) AS closed FROM customer ct JOIN ar a ON (a.customer_id = ct.id) LEFT JOIN business b ON (ct.business_id = b.id) WHERE 1 = 1 AND NOT obsolete AND (a.invoice = '1') UNION SELECT ct.*, b.description AS business, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, 'oe' AS module, 'order' AS formtype, o.closed FROM customer ct JOIN oe o ON (o.customer_id = ct.id) LEFT JOIN business b ON (ct.business_id = b.id) WHERE 1 = 1 AND NOT obsolete AND (o.quotation = '0') UNION SELECT ct.*, b.description AS business, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, 'oe' AS module, 'quotation' AS formtype, o.closed FROM customer ct JOIN oe o ON (o.customer_id = ct.id) LEFT JOIN business b ON (ct.business_id = b.id) WHERE 1 = 1 AND NOT obsolete AND (o.quotation = '1') ORDER BY lower(name) ASC
FEHLER: ungültige ORDER-BY-Klausel mit UNION/INTERSECT/EXCEPT
DETAIL: Es können nur Ergebnisspaltennamen verwendet werden, keine Ausdrücke oder Funktionen.
HINT: Fügen Sie den Ausdrück/die Funktion jedem SELECT hinzu oder verlegen Sie die UNION in eine FROM-Klausel.

Bei Lieferanten dasselbe mit Kombinationen von Rechnungen, Lieferantenaufträge, Preisanfragen:

SELECT ct.*, b.description AS business, a.invnumber, a.ordnumber, a.quonumber, a.id AS invid, 'ir' AS module, 'invoice' AS formtype, (a.amount = a.paid) AS closed FROM vendor ct JOIN ap a ON (a.vendor_id = ct.id) LEFT JOIN business b ON (ct.business_id = b.id) WHERE 1 = 1 AND NOT obsolete AND (a.invoice = '1') UNION SELECT ct.*, b.description AS business, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, 'oe' AS module, 'order' AS formtype, o.closed FROM vendor ct JOIN oe o ON (o.vendor_id = ct.id) LEFT JOIN business b ON (ct.business_id = b.id) WHERE 1 = 1 AND NOT obsolete AND (o.quotation = '0') UNION SELECT ct.*, b.description AS business, ' ' AS invnumber, o.ordnumber, o.quonumber, o.id AS invid, 'oe' AS module, 'quotation' AS formtype, o.closed FROM vendor ct JOIN oe o ON (o.vendor_id = ct.id) LEFT JOIN business b ON (ct.business_id = b.id) WHERE 1 = 1 AND NOT obsolete AND (o.quotation = '1') ORDER BY lower(name) ASC
FEHLER: ungültige ORDER-BY-Klausel mit UNION/INTERSECT/EXCEPT
DETAIL: Es können nur Ergebnisspaltennamen verwendet werden, keine Ausdrücke oder Funktionen.
HINT: Fügen Sie den Ausdrück/die Funktion jedem SELECT hinzu oder verlegen Sie die UNION in eine FROM-Klausel.

schau ich mit mal an.

Fix in -r4113