Witam serdecznie,
Przy próbie drukowania klientów przez KLIENCI/SZUKAJ, filtr "usunięci od
01/01/2015 do 31/01/2015" i wyborze grupy w której mam ponad 400
usuniętych klientów LMS wyrzuca błąd:
Napotkano błędy w bazie danych!
*Zapytanie:* SELECT c.id AS id, CONCAT(UPPER(lastname), ' ', c.name) AS
customername, status, address, zip, city, countryid, countries.name AS
country, email, ten, ssn, c.info AS info, message, c.divisionid,
c.paytime AS paytime, COALESCE(b.value, 0) AS balance, COALESCE(t.value,
0) AS tariffvalue, s.account, s.warncount, s.online, c.type AS
customertype, cutoffstop, (SELECT uid FROM imessengers WHERE
imessengers.customerid = c.id) AS uid, (SELECT uid FROM imessengers
WHERE imessengers.customerid = c.id) AS umowa, (SELECT max(cash.time)
FROM cash WHERE cash.customerid = c.id) AS lastcash, (SELECT max(dateto)
FROM assignments WHERE assignments.customerid = c.id) AS dateto, (CASE
WHEN s.account = s.acsum THEN 1 WHEN s.acsum > 0 THEN 2 ELSE 0 END) AS
nodeac, (CASE WHEN s.warncount = s.warnsum THEN 1 WHEN s.warnsum > 0
THEN 2 ELSE 0 END) AS nodewarn FROM customersview c LEFT JOIN countries
ON (c.countryid = countries.id) LEFT JOIN customerassignments ON (c.id =
customerassignments.customerid) JOIN customercash b ON (b.customerid =
c.id) LEFT JOIN (SELECT a.customerid, SUM((CASE a.suspended WHEN 0 THEN
(((100 - a.pdiscount) * (CASE WHEN t.value IS NULL THEN l.value ELSE
t.value END) / 100) - a.vdiscount) ELSE ((((100 - a.pdiscount) * (CASE
WHEN t.value IS NULL THEN l.value ELSE t.value END) / 100) -
a.vdiscount) * 0 / 100) END) * (CASE t.period WHEN 3 THEN 1 WHEN 5 THEN
1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 ELSE (CASE a.period WHEN 3
THEN 1 WHEN 5 THEN 1/12.0 WHEN 7 THEN 1/6.0 WHEN 4 THEN 1/3.0 ELSE 0
END) END) ) AS value FROM assignments a LEFT JOIN tariffs t ON (t.id =
a.tariffid) LEFT JOIN liabilities l ON (l.id = a.liabilityid AND
a.period != 0) WHERE (a.datefrom <= UNIX_TIMESTAMP() OR a.datefrom = 0)
AND (a.dateto > UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid
) t ON (t.customerid = c.id) LEFT JOIN (SELECT ownerid, SUM(access) AS
acsum, COUNT(access) AS account, SUM(warning) AS warnsum, COUNT(warning)
AS warncount, (CASE WHEN MAX(lastonline) > UNIX_TIMESTAMP() - 600 THEN 1
ELSE 0 END) AS online FROM nodes WHERE ownerid > 0 GROUP BY ownerid ) s
ON (s.ownerid = c.id) LEFT JOIN imessengers im ON im.customerid = c.id
WHERE c.deleted = 1 AND customergroupid=62 AND ((moddate >= 1420066800
AND moddate <= 1422745199)) ORDER BY customername asc
*Błąd:* Subquery returns more than 1 row
Problem występuje tylko z tą jedną grupą. Mam inne grupy z usuniętymi
klientami i na tamtych grupach to działa. Nie mogę dojść co jest
przyczyną. Macie jakieś pomysły ?
pozdrawiam
--
Jacek Brzozowski
