Re: [lms] Błąd w bazie danych - Klienci/Szukaj

Wyrzuciłem to zapytanie z imessengers i poszło. Dziękuje za podpowiedź. Mam tu małą lokalną modyfikację i to było to.

pozdrawiam Jacek

W dniu 2015-07-08 o 14:57, Maciej Lew pisze:

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 mailto: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 <http://c.id> AS id,
CONCAT(UPPER(lastname), ' ', c.name <http://c.name>) AS
customername, status, address, zip, city, countryid,
countries.name <http://countries.name> AS country, email, ten,
ssn, c.info <http://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 <http://c.id>) AS
uid, (SELECT uid FROM imessengers WHERE imessengers.customerid =
c.id <http://c.id>) AS umowa, (SELECT max(cash.time) FROM cash
WHERE cash.customerid = c.id <http://c.id>) AS lastcash, (SELECT
max(dateto) FROM assignments WHERE assignments.customerid = c.id
<http://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 <http://countries.id>) LEFT JOIN
customerassignments ON (c.id <http://c.id> =
customerassignments.customerid) JOIN customercash b ON
(b.customerid = c.id <http://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 <http://t.id> = a.tariffid) LEFT JOIN
liabilities l ON (l.id <http://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 <http://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 <http://c.id>) LEFT JOIN imessengers im ON
im.customerid = c.id <http://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


_______________________________________________
lms mailing list
lms@lists.lms.org.pl <mailto:lms@lists.lms.org.pl>
http://lists.lms.org.pl/mailman/listinfo/lms

---

lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms