W dniu 08.07.2015 14:57, Maciej Lew napisał(a):
Wykonaj wewnętrzne zapytania SELECT i zobacz o które z nich chodzi. Podejrzanie wygląda to do tabeli imessengers. 08-07-2015 08:44, "Jacek Brzozowski" j.brzozowski@marton.pl napisał(a):
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 [1] AS id, CONCAT(UPPER(lastname), ' ', c.name [2]) AS customername, status, address, zip, city, countryid, countries.name [3] AS country, email, ten, ssn, c.info [4] 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 [1]) AS uid, (SELECT uid FROM imessengers WHERE imessengers.customerid = c.id [1]) AS umowa, (SELECT max(cash.time) FROM cash WHERE cash.customerid = c.id [1]) AS lastcash, (SELECT max(dateto) FROM assignments WHERE assignments.customerid = c.id [1]) 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 [5]) LEFT JOIN customerassignments ON (c.id [1] = customerassignments.customerid) JOIN customercash b ON (b.customerid = c.id [1]) 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 [6] = a.tariffid) LEFT JOIN liabilities l ON (l.id [7] = 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 [1]) 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 [1]) LEFT JOIN imessengers im ON im.customerid = c.id [1] 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 ?
Może napisałbyś jakiej wersji lms używasz i czy to nie jest czasem jakaś lokalnie modyfikowana?
pozdrawiam
-- Jacek Brzozowski