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