Błąd w bazie danych - Klienci/Szukaj
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
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 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
lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
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
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
uczestnicy (3)
-
Jacek Brzozowski -
Maciej Lew -
Tomasz Chiliński