błędy po UPGRADE do aktualnej wersji z GIT
.......?m=customersearch&search=1 wyszukiwanie klientów Wystąpiły błędy w obsłudze bazy danych! Zapytanie: SELECT c.id AS id, CONCAT(UPPER(lastname), ' ', c.name) AS customername, status, full_address, address, zip, city, countryid, countries.name AS country, cc.email, ccp.phone, ten, ssn, c.info AS info, extid, 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, (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 customerview c LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = c.id LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS phone FROM customercontacts WHERE (type & 5 > 0) GROUP BY customerid) ccp ON ccp.customerid = c.id LEFT JOIN countries ON (c.countryid = countries.id) LEFT JOIN (SELECT SUM(value) AS value, customerid FROM cash GROUP BY customerid ) 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.commited = 1 AND a.datefrom <= UNIX_TIMESTAMP() 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 AND ipaddr <> 0 GROUP BY ownerid ) s ON (s.ownerid = c.id) WHERE c.deleted = 0 AND (CONCAT(UPPER(c.lastname), ' ', UPPER(c.name)) LIKE UPPER('%a%') OR (UPPER(address) LIKE UPPER('%a%') OR UPPER(post_address) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.address) LIKE UPPER('%a%'))) OR (UPPER(zip) LIKE UPPER('%a%') OR UPPER(post_zip) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.zip) LIKE UPPER('%a%'))) OR (UPPER(city) LIKE UPPER('%a%') OR UPPER(post_city) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.city) LIKE UPPER('%a%'))) OR EXISTS (SELECT 1 FROM customercontacts WHERE customerid = c.id AND customercontacts.type & 8 = 8 AND contact LIKE '%a%')) ORDER BY customername asc Błąd: Unknown column 'c.id' in 'on clause'
podejrzewam, że w strukturze nie masz widoku customerview
2018-01-03 10:01 GMT+01:00 Marcin Świerkowski marcin@itcom.pl:
.......?m=customersearch&search=1 wyszukiwanie klientów
Wystąpiły błędy w obsłudze bazy danych! Zapytanie: SELECT c.id AS id, CONCAT(UPPER(lastname), ' ', c.name) AS customername, status, full_address, address, zip, city, countryid, countries.name AS country, cc.email, ccp.phone, ten, ssn, c.info AS info, extid, 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, (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 customerview c LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = c.id LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS phone FROM customercontacts WHERE (type & 5 > 0) GROUP BY customerid) ccp ON ccp.customerid = c.id LEFT JOIN countries ON (c.countryid = countries.id) LEFT JOIN (SELECT SUM(value) AS value, customerid FROM cash GROUP BY customerid ) 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.commited = 1 AND a.datefrom <= UNIX_TIMESTAMP() 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 AND ipaddr
<> 0 GROUP BY ownerid ) s ON (s.ownerid = c.id) WHERE c.deleted = 0 AND (CONCAT(UPPER(c.lastname), ' ', UPPER(c.name)) LIKE UPPER('%a%') OR (UPPER(address) LIKE UPPER('%a%') OR UPPER(post_address) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.address) LIKE UPPER('%a%'))) OR (UPPER(zip) LIKE UPPER('%a%') OR UPPER(post_zip) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.zip) LIKE UPPER('%a%'))) OR (UPPER(city) LIKE UPPER('%a%') OR UPPER(post_city) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE UPPER(va.city) LIKE UPPER('%a%'))) OR EXISTS (SELECT 1 FROM customercontacts WHERE customerid = c.id AND customercontacts.type & 8 = 8 AND contact LIKE '%a%')) ORDER BY customername asc Błąd: Unknown column 'c.id' in 'on clause'
lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
sprawdzilem i mam :(
----- Oryginalna wiadomość ----- Od: "Marcin" marcin@nicram.net Do: "lista użytkowników LMS" lms@lists.lms.org.pl Wysłane: środa, 3 stycznia, 2018 11:07:24 Temat: Re: [lms] błędy po UPGRADE do aktualnej wersji z GIT
podejrzewam, że w strukturze nie masz widoku customerview
2018-01-03 10:01 GMT+01:00 Marcin Świerkowski < [ mailto:marcin@itcom.pl | marcin@itcom.pl ] > :
.......?m=customersearch&search=1 wyszukiwanie klientów
Wystąpiły błędy w obsłudze bazy danych! Zapytanie: SELECT [ http://c.id/ | c.id ] AS id, CONCAT(UPPER(lastname), ' ', [ http://c.name/ | c.name ] ) AS customername, status, full_address, address, zip, city, countryid, [ http://countries.name/ | countries.name ] AS country, cc.email, ccp.phone, ten, ssn, [ http://c.info/ | c.info ] AS info, extid, 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, (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 customerview c LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = [ http://c.id/ | c.id ] LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS phone FROM customercontacts WHERE (type & 5 > 0) GROUP BY customerid) ccp ON ccp.customerid = [ http://c.id/ | c.id ] LEFT JOIN countries ON (c.countryid = [ http://countries.id/ | countries.id ] ) LEFT JOIN (SELECT SUM(value) AS value, customerid FROM cash GROUP BY customerid ) b ON (b.customerid = [ http://c.id/ | 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 ( [ http://t.id/ | t.id ] = a.tariffid) LEFT JOIN liabilities l ON ( [ http://l.id/ | l.id ] = a.liabilityid AND a.period != 0) WHERE a.commited = 1 AND a.datefrom <= UNIX_TIMESTAMP() AND (a.dateto > UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid ) t ON (t.customerid = [ http://c.id/ | 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 AND ipaddr <> 0 GROUP BY ownerid ) s ON (s.ownerid = [ http://c.id/ | c.id ] ) WHERE c.deleted = 0 AND (CONCAT(UPPER(c.lastname), ' ', UPPER( [ http://c.name/ | c.name ] )) LIKE UPPER('%a%') OR (UPPER(address) LIKE UPPER('%a%') OR UPPER(post_address) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.address) LIKE UPPER('%a%'))) OR (UPPER(zip) LIKE UPPER('%a%') OR UPPER(post_zip) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.zip) LIKE UPPER('%a%'))) OR (UPPER(city) LIKE UPPER('%a%') OR UPPER(post_city) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.city) LIKE UPPER('%a%'))) OR EXISTS (SELECT 1 FROM customercontacts WHERE customerid = [ http://c.id/ | c.id ] AND customercontacts.type & 8 = 8 AND contact LIKE '%a%')) ORDER BY customername asc Błąd: Unknown column ' [ http://c.id/ | c.id ] ' in 'on clause'
_______________________________________________ lms mailing list [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/mailman/listinfo/lms ]
wrzuciłem czystego nowego lms z GIT'a i to samo więc albo mam jakiegoś starego mysql ( na postgres wszystko działa ) albo jest jakiś mismasz w zapytaniu ( niekompatybilny z mysql )
Wersja LMS: 1.11-git Wersja LMSDB: 2018010500 Wersja MySQL: 5.5.54-0ubuntu0.12.04.1 Wersja PHP: 5.3.10-1ubuntu3.26 Wersja Smarty: 3.1.31
----- Oryginalna wiadomość ----- Od: "Marcin" marcin@nicram.net Do: "lista użytkowników LMS" lms@lists.lms.org.pl Wysłane: środa, 3 stycznia, 2018 11:07:24 Temat: Re: [lms] błędy po UPGRADE do aktualnej wersji z GIT
podejrzewam, że w strukturze nie masz widoku customerview
2018-01-03 10:01 GMT+01:00 Marcin Świerkowski < [ mailto:marcin@itcom.pl | marcin@itcom.pl ] > :
.......?m=customersearch&search=1 wyszukiwanie klientów
Wystąpiły błędy w obsłudze bazy danych! Zapytanie: SELECT [ http://c.id/ | c.id ] AS id, CONCAT(UPPER(lastname), ' ', [ http://c.name/ | c.name ] ) AS customername, status, full_address, address, zip, city, countryid, [ http://countries.name/ | countries.name ] AS country, cc.email, ccp.phone, ten, ssn, [ http://c.info/ | c.info ] AS info, extid, 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, (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 customerview c LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = [ http://c.id/ | c.id ] LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS phone FROM customercontacts WHERE (type & 5 > 0) GROUP BY customerid) ccp ON ccp.customerid = [ http://c.id/ | c.id ] LEFT JOIN countries ON (c.countryid = [ http://countries.id/ | countries.id ] ) LEFT JOIN (SELECT SUM(value) AS value, customerid FROM cash GROUP BY customerid ) b ON (b.customerid = [ http://c.id/ | 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 ( [ http://t.id/ | t.id ] = a.tariffid) LEFT JOIN liabilities l ON ( [ http://l.id/ | l.id ] = a.liabilityid AND a.period != 0) WHERE a.commited = 1 AND a.datefrom <= UNIX_TIMESTAMP() AND (a.dateto > UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid ) t ON (t.customerid = [ http://c.id/ | 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 AND ipaddr <> 0 GROUP BY ownerid ) s ON (s.ownerid = [ http://c.id/ | c.id ] ) WHERE c.deleted = 0 AND (CONCAT(UPPER(c.lastname), ' ', UPPER( [ http://c.name/ | c.name ] )) LIKE UPPER('%a%') OR (UPPER(address) LIKE UPPER('%a%') OR UPPER(post_address) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.address) LIKE UPPER('%a%'))) OR (UPPER(zip) LIKE UPPER('%a%') OR UPPER(post_zip) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.zip) LIKE UPPER('%a%'))) OR (UPPER(city) LIKE UPPER('%a%') OR UPPER(post_city) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.city) LIKE UPPER('%a%'))) OR EXISTS (SELECT 1 FROM customercontacts WHERE customerid = [ http://c.id/ | c.id ] AND customercontacts.type & 8 = 8 AND contact LIKE '%a%')) ORDER BY customername asc Błąd: Unknown column ' [ http://c.id/ | c.id ] ' in 'on clause'
_______________________________________________ lms mailing list [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/mailman/listinfo/lms ]
Błąd: Unknown column ' [ http://c.id/ | c.id ] ' in 'on clause'
To Cię nie nakierowało? Co Ci drukuje "DESCRIBE customerview"?
2018-01-07 16:56 GMT+01:00 Marcin Świerkowski marcin@itcom.pl:
wrzuciłem czystego nowego lms z GIT'a i to samo więc albo mam jakiegoś starego mysql ( na postgres wszystko działa ) albo jest jakiś mismasz w zapytaniu ( niekompatybilny z mysql )
Wersja LMS: 1.11-git Wersja LMSDB: 2018010500 Wersja MySQL: 5.5.54-0ubuntu0.12.04.1 Wersja PHP: 5.3.10-1ubuntu3.26 Wersja Smarty: 3.1.31
----- Oryginalna wiadomość ----- Od: "Marcin" marcin@nicram.net Do: "lista użytkowników LMS" lms@lists.lms.org.pl Wysłane: środa, 3 stycznia, 2018 11:07:24 Temat: Re: [lms] błędy po UPGRADE do aktualnej wersji z GIT
podejrzewam, że w strukturze nie masz widoku customerview
2018-01-03 10:01 GMT+01:00 Marcin Świerkowski < [ mailto:marcin@itcom.pl | marcin@itcom.pl ] > :
.......?m=customersearch&search=1 wyszukiwanie klientów
Wystąpiły błędy w obsłudze bazy danych! Zapytanie: SELECT [ http://c.id/ | c.id ] AS id, CONCAT(UPPER(lastname), ' ', [ http://c.name/ | c.name ] ) AS customername, status, full_address, address, zip, city, countryid, [ http://countries.name/ | countries.name ] AS country, cc.email, ccp.phone, ten, ssn, [ http://c.info/ | c.info ] AS info, extid, 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, (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 customerview c LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = [ http://c.id/ | c.id ] LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS phone FROM customercontacts WHERE (type & 5 > 0) GROUP BY customerid) ccp ON ccp.customerid = [ http://c.id/ | c.id ] LEFT JOIN countries ON (c.countryid = [ http://countries.id/ | countries.id ] ) LEFT JOIN (SELECT SUM(value) AS value, customerid FROM cash GROUP BY customerid ) b ON (b.customerid = [ http://c.id/ | 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 ( [ http://t.id/ | t.id ] = a.tariffid) LEFT JOIN liabilities l ON ( [ http://l.id/ | l.id ] = a.liabilityid AND a.period != 0) WHERE a.commited = 1 AND a.datefrom <= UNIX_TIMESTAMP() AND (a.dateto
UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid ) t ON
(t.customerid = [ http://c.id/ | 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 AND ipaddr
<> 0 GROUP BY ownerid ) s ON (s.ownerid = [ http://c.id/ | c.id ] ) WHERE c.deleted = 0 AND (CONCAT(UPPER(c.lastname), ' ', UPPER( [ http://c.name/ | c.name ] )) LIKE UPPER('%a%') OR (UPPER(address) LIKE UPPER('%a%') OR UPPER(post_address) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.address) LIKE UPPER('%a%'))) OR (UPPER(zip) LIKE UPPER('%a%') OR UPPER(post_zip) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.zip) LIKE UPPER('%a%'))) OR (UPPER(city) LIKE UPPER('%a%') OR UPPER(post_city) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.city) LIKE UPPER('%a%'))) OR EXISTS (SELECT 1 FROM customercontacts WHERE customerid = [ http://c.id/ | c.id ] AND customercontacts.type & 8 = 8 AND contact LIKE '%a%')) ORDER BY customername asc Błąd: Unknown column ' [ http://c.id/ | c.id ] ' in 'on clause'
lms mailing list [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/ mailman/listinfo/lms ]
-- Pozdrawiam Marcin / nicraM
lms mailing list 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
mam to samo.
2018-01-08 8:19 GMT+01:00 Marcin marcin@nicram.net:
Błąd: Unknown column ' [ http://c.id/ | c.id ] ' in 'on clause'
To Cię nie nakierowało? Co Ci drukuje "DESCRIBE customerview"?
2018-01-07 16:56 GMT+01:00 Marcin Świerkowski marcin@itcom.pl:
wrzuciłem czystego nowego lms z GIT'a i to samo więc albo mam jakiegoś starego mysql ( na postgres wszystko działa ) albo jest jakiś mismasz w zapytaniu ( niekompatybilny z mysql )
Wersja LMS: 1.11-git Wersja LMSDB: 2018010500 Wersja MySQL: 5.5.54-0ubuntu0.12.04.1 Wersja PHP: 5.3.10-1ubuntu3.26 Wersja Smarty: 3.1.31
----- Oryginalna wiadomość ----- Od: "Marcin" marcin@nicram.net Do: "lista użytkowników LMS" lms@lists.lms.org.pl Wysłane: środa, 3 stycznia, 2018 11:07:24 Temat: Re: [lms] błędy po UPGRADE do aktualnej wersji z GIT
podejrzewam, że w strukturze nie masz widoku customerview
2018-01-03 10:01 GMT+01:00 Marcin Świerkowski < [ mailto:marcin@itcom.pl | marcin@itcom.pl ] > :
.......?m=customersearch&search=1 wyszukiwanie klientów
Wystąpiły błędy w obsłudze bazy danych! Zapytanie: SELECT [ http://c.id/ | c.id ] AS id, CONCAT(UPPER(lastname), ' ', [ http://c.name/ | c.name ] ) AS customername, status, full_address, address, zip, city, countryid, [ http://countries.name/ | countries.name ] AS country, cc.email, ccp.phone, ten, ssn, [ http://c.info/ | c.info ] AS info, extid, 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, (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 customerview c LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = [ http://c.id/ | c.id ] LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS phone FROM customercontacts WHERE (type & 5 > 0) GROUP BY customerid) ccp ON ccp.customerid = [ http://c.id/ | c.id ] LEFT JOIN countries ON (c.countryid = [ http://countries.id/ | countries.id ] ) LEFT JOIN (SELECT SUM(value) AS value, customerid FROM cash GROUP BY customerid ) b ON (b.customerid = [ http://c.id/ | 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 ( [ http://t.id/ | t.id ] = a.tariffid) LEFT JOIN liabilities l ON ( [ http://l.id/ | l.id ] = a.liabilityid AND a.period != 0) WHERE a.commited = 1 AND a.datefrom <= UNIX_TIMESTAMP() AND (a.dateto
UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid ) t ON
(t.customerid = [ http://c.id/ | 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 AND ipaddr
<> 0 GROUP BY ownerid ) s ON (s.ownerid = [ http://c.id/ | c.id ] ) WHERE c.deleted = 0 AND (CONCAT(UPPER(c.lastname), ' ', UPPER( [ http://c.name/ | c.name ] )) LIKE UPPER('%a%') OR (UPPER(address) LIKE UPPER('%a%') OR UPPER(post_address) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.address) LIKE UPPER('%a%'))) OR (UPPER(zip) LIKE UPPER('%a%') OR UPPER(post_zip) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.zip) LIKE UPPER('%a%'))) OR (UPPER(city) LIKE UPPER('%a%') OR UPPER(post_city) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ http://va.id/ | va.id ] = ca.address_id AND ca.customer_id = [ http://c.id/ | c.id ] WHERE UPPER(va.city) LIKE UPPER('%a%'))) OR EXISTS (SELECT 1 FROM customercontacts WHERE customerid = [ http://c.id/ | c.id ] AND customercontacts.type & 8 = 8 AND contact LIKE '%a%')) ORDER BY customername asc Błąd: Unknown column ' [ http://c.id/ | c.id ] ' in 'on clause'
lms mailing list [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/mailman/listinfo/lms ]
-- Pozdrawiam Marcin / nicraM
lms mailing list 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
-- Pozdrawiam Marcin / nicraM
lms mailing list lms@lists.lms.org.pl http://lists.lms.org.pl/mailman/listinfo/lms
mysql> describe customerview; +-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | extid | varchar(32) | NO | | | | | lastname | varchar(128) | NO | | | | | name | varchar(128) | NO | | | | | status | smallint(6) | NO | | 0 | | | type | smallint(6) | NO | | 0 | | | ten | varchar(16) | NO | | | | | ssn | varchar(11) | NO | | | | | regon | varchar(255) | NO | | | | | rbe | varchar(255) | NO | | | | | rbename | varchar(255) | NO | | | | | icn | varchar(255) | NO | | | | | info | text | NO | | NULL | | | notes | text | NO | | NULL | | | creationdate | int(11) | NO | | 0 | | | moddate | int(11) | NO | | 0 | | | creatorid | int(11) | YES | | NULL | | | modid | int(11) | YES | | NULL | | | deleted | tinyint(1) | NO | | 0 | | | message | text | NO | | NULL | | | pin | varchar(255) | NO | | 0 | | | cutoffstop | int(11) | NO | | 0 | | | consentdate | int(11) | NO | | 0 | | | einvoice | tinyint(1) | YES | | NULL | | | invoicenotice | tinyint(1) | YES | | NULL | | | mailingnotice | tinyint(1) | YES | | NULL | | | divisionid | int(11) | YES | | NULL | | | paytime | smallint(6) | NO | | -1 | | | paytype | smallint(6) | YES | | NULL | | | countryid | int(11) | YES | | NULL | | | zip | varchar(10) | YES | | NULL | | | city | varchar(100) | YES | | NULL | | | street | varchar(255) | YES | | NULL | | | building | varchar(20) | YES | | NULL | | | apartment | varchar(20) | YES | | NULL | | | post_countryid | int(11) | YES | | NULL | | | post_zip | varchar(10) | YES | | NULL | | | post_city | varchar(100) | YES | | NULL | | | post_street | varchar(255) | YES | | NULL | | | post_building | varchar(20) | YES | | NULL | | | post_apartment | varchar(20) | YES | | NULL | | | post_name | text | YES | | NULL | | | address | varchar(297) | YES | | NULL | | | full_address | varchar(444) | YES | | NULL | | | postoffice | varchar(32) | YES | | NULL | | | post_address | varchar(297) | YES | | NULL | | | post_full_address | varchar(444) | YES | | NULL | | | post_postoffice | varchar(32) | YES | | NULL | | +-------------------+--------------+------+-----+---------+-------+ 48 rows in set (0.00 sec)
----- Oryginalna wiadomość ----- Od: "Marcin" marcin@nicram.net Do: "lista użytkowników LMS" lms@lists.lms.org.pl Wysłane: poniedziałek, 8 stycznia, 2018 8:19:08 Temat: Re: [lms] błędy po UPGRADE do aktualnej wersji z GIT
Błąd: Unknown column ' [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ' in 'on clause'
To Cię nie nakierowało? Co Ci drukuje " DESCRIBE customerview "?
2018-01-07 16:56 GMT+01:00 Marcin Świerkowski < [ mailto:marcin@itcom.pl | marcin@itcom.pl ] > :
wrzuciłem czystego nowego lms z GIT'a i to samo więc albo mam jakiegoś starego mysql ( na postgres wszystko działa ) albo jest jakiś mismasz w zapytaniu ( niekompatybilny z mysql )
Wersja LMS: 1.11-git Wersja LMSDB: [ tel:2018010500 | 2018010500 ] Wersja MySQL: 5.5.54-0ubuntu0.12.04.1 Wersja PHP: 5.3.10-1ubuntu3.26 Wersja Smarty: 3.1.31
----- Oryginalna wiadomość ----- Od: "Marcin" < [ mailto:marcin@nicram.net | marcin@nicram.net ] > Do: "lista użytkowników LMS" < [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] > Wysłane: środa, 3 stycznia, 2018 11:07:24 Temat: Re: [lms] błędy po UPGRADE do aktualnej wersji z GIT
podejrzewam, że w strukturze nie masz widoku customerview
2018-01-03 10:01 GMT+01:00 Marcin Świerkowski < [ mailto: [ mailto:marcin@itcom.pl | marcin@itcom.pl ] | [ mailto:marcin@itcom.pl | marcin@itcom.pl ] ] > :
.......?m=customersearch&search=1 wyszukiwanie klientów
Wystąpiły błędy w obsłudze bazy danych! Zapytanie: SELECT [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] AS id, CONCAT(UPPER(lastname), ' ', [ [ http://c.name/ | http://c.name/ ] | [ http://c.name/ | c.name ] ] ) AS customername, status, full_address, address, zip, city, countryid, [ [ http://countries.name/ | http://countries.name/ ] | [ http://countries.name/ | countries.name ] ] AS country, cc.email, ccp.phone, ten, ssn, [ [ http://c.info/ | http://c.info/ ] | [ http://c.info/ | c.info ] ] AS info, extid, 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, (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 customerview c LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS phone FROM customercontacts WHERE (type & 5 > 0) GROUP BY customerid) ccp ON ccp.customerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] LEFT JOIN countries ON (c.countryid = [ [ http://countries.id/ | http://countries.id/ ] | [ http://countries.id/ | countries.id ] ] ) LEFT JOIN (SELECT SUM(value) AS value, customerid FROM cash GROUP BY customerid ) b ON (b.customerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | 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 ( [ [ http://t.id/ | http://t.id/ ] | [ http://t.id/ | t.id ] ] = a.tariffid) LEFT JOIN liabilities l ON ( [ [ http://l.id/ | http://l.id/ ] | [ http://l.id/ | l.id ] ] = a.liabilityid AND a.period != 0) WHERE a.commited = 1 AND a.datefrom <= UNIX_TIMESTAMP() AND (a.dateto > UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid ) t ON (t.customerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | 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 AND ipaddr <> 0 GROUP BY ownerid ) s ON (s.ownerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ) WHERE c.deleted = 0 AND (CONCAT(UPPER(c.lastname), ' ', UPPER( [ [ http://c.name/ | http://c.name/ ] | [ http://c.name/ | c.name ] ] )) LIKE UPPER('%a%') OR (UPPER(address) LIKE UPPER('%a%') OR UPPER(post_address) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | va.id ] ] = ca.address_id AND ca.customer_id = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] WHERE UPPER(va.address) LIKE UPPER('%a%'))) OR (UPPER(zip) LIKE UPPER('%a%') OR UPPER(post_zip) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | va.id ] ] = ca.address_id AND ca.customer_id = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] WHERE UPPER(va.zip) LIKE UPPER('%a%'))) OR (UPPER(city) LIKE UPPER('%a%') OR UPPER(post_city) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | va.id ] ] = ca.address_id AND ca.customer_id = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] WHERE UPPER(va.city) LIKE UPPER('%a%'))) OR EXISTS (SELECT 1 FROM customercontacts WHERE customerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] AND customercontacts.type & 8 = 8 AND contact LIKE '%a%')) ORDER BY customername asc Błąd: Unknown column ' [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ' in 'on clause'
_______________________________________________ lms mailing list [ mailto: [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] | [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] ] [ [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/mailman/listinfo/lms ] | [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/mailman/listinfo/lms ] ]
sorki SHOW CREATE VIEW *view_name*
2018-01-09 18:37 GMT+01:00 Marcin Świerkowski marcin@itcom.pl:
mysql> describe customerview; +-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | extid | varchar(32) | NO | | | | | lastname | varchar(128) | NO | | | | | name | varchar(128) | NO | | | | | status | smallint(6) | NO | | 0 | | | type | smallint(6) | NO | | 0 | | | ten | varchar(16) | NO | | | | | ssn | varchar(11) | NO | | | | | regon | varchar(255) | NO | | | | | rbe | varchar(255) | NO | | | | | rbename | varchar(255) | NO | | | | | icn | varchar(255) | NO | | | | | info | text | NO | | NULL | | | notes | text | NO | | NULL | | | creationdate | int(11) | NO | | 0 | | | moddate | int(11) | NO | | 0 | | | creatorid | int(11) | YES | | NULL | | | modid | int(11) | YES | | NULL | | | deleted | tinyint(1) | NO | | 0 | | | message | text | NO | | NULL | | | pin | varchar(255) | NO | | 0 | | | cutoffstop | int(11) | NO | | 0 | | | consentdate | int(11) | NO | | 0 | | | einvoice | tinyint(1) | YES | | NULL | | | invoicenotice | tinyint(1) | YES | | NULL | | | mailingnotice | tinyint(1) | YES | | NULL | | | divisionid | int(11) | YES | | NULL | | | paytime | smallint(6) | NO | | -1 | | | paytype | smallint(6) | YES | | NULL | | | countryid | int(11) | YES | | NULL | | | zip | varchar(10) | YES | | NULL | | | city | varchar(100) | YES | | NULL | | | street | varchar(255) | YES | | NULL | | | building | varchar(20) | YES | | NULL | | | apartment | varchar(20) | YES | | NULL | | | post_countryid | int(11) | YES | | NULL | | | post_zip | varchar(10) | YES | | NULL | | | post_city | varchar(100) | YES | | NULL | | | post_street | varchar(255) | YES | | NULL | | | post_building | varchar(20) | YES | | NULL | | | post_apartment | varchar(20) | YES | | NULL | | | post_name | text | YES | | NULL | | | address | varchar(297) | YES | | NULL | | | full_address | varchar(444) | YES | | NULL | | | postoffice | varchar(32) | YES | | NULL | | | post_address | varchar(297) | YES | | NULL | | | post_full_address | varchar(444) | YES | | NULL | | | post_postoffice | varchar(32) | YES | | NULL | | +-------------------+--------------+------+-----+---------+-------+ 48 rows in set (0.00 sec)
----- Oryginalna wiadomość ----- Od: "Marcin" marcin@nicram.net Do: "lista użytkowników LMS" lms@lists.lms.org.pl Wysłane: poniedziałek, 8 stycznia, 2018 8:19:08 Temat: Re: [lms] błędy po UPGRADE do aktualnej wersji z GIT
Błąd: Unknown column ' [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ' in 'on clause'
To Cię nie nakierowało? Co Ci drukuje " DESCRIBE customerview "?
2018-01-07 16:56 GMT+01:00 Marcin Świerkowski < [ mailto:marcin@itcom.pl | marcin@itcom.pl ] > :
wrzuciłem czystego nowego lms z GIT'a i to samo więc albo mam jakiegoś starego mysql ( na postgres wszystko działa ) albo jest jakiś mismasz w zapytaniu ( niekompatybilny z mysql )
Wersja LMS: 1.11-git Wersja LMSDB: [ tel:2018010500 | 2018010500 ] Wersja MySQL: 5.5.54-0ubuntu0.12.04.1 Wersja PHP: 5.3.10-1ubuntu3.26 Wersja Smarty: 3.1.31
----- Oryginalna wiadomość ----- Od: "Marcin" < [ mailto:marcin@nicram.net | marcin@nicram.net ] > Do: "lista użytkowników LMS" < [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] > Wysłane: środa, 3 stycznia, 2018 11:07:24 Temat: Re: [lms] błędy po UPGRADE do aktualnej wersji z GIT
podejrzewam, że w strukturze nie masz widoku customerview
2018-01-03 10:01 GMT+01:00 Marcin Świerkowski < [ mailto: [ mailto: marcin@itcom.pl | marcin@itcom.pl ] | [ mailto:marcin@itcom.pl | marcin@itcom.pl ] ] > :
.......?m=customersearch&search=1 wyszukiwanie klientów
Wystąpiły błędy w obsłudze bazy danych! Zapytanie: SELECT [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] AS id, CONCAT(UPPER(lastname), ' ', [ [ http://c.name/ | http://c.name/ ] | [ http://c.name/ | c.name ] ] ) AS customername, status, full_address, address, zip, city, countryid, [ [ http://countries.name/ | http://countries.name/ ] | [ http://countries.name/ | countries.name ] ] AS country, cc.email, ccp.phone, ten, ssn, [ [ http://c.info/ | http://c.info/ ] | [ http://c.info/ | c.info ] ] AS info, extid, 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, (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 customerview c LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS phone FROM customercontacts WHERE (type & 5 > 0) GROUP BY customerid) ccp ON ccp.customerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] LEFT JOIN countries ON (c.countryid = [ [ http://countries.id/ | http://countries.id/ ] | [ http://countries.id/ | countries.id ] ] ) LEFT JOIN (SELECT SUM(value) AS value, customerid FROM cash GROUP BY customerid ) b ON (b.customerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | 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 ( [ [ http://t.id/ | http://t.id/ ] | [ http://t.id/ | t.id ] ] = a.tariffid) LEFT JOIN liabilities l ON ( [ [ http://l.id/ | http://l.id/ ] | [ http://l.id/ | l.id ] ] = a.liabilityid AND a.period != 0) WHERE a.commited = 1 AND a.datefrom <= UNIX_TIMESTAMP() AND (a.dateto > UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid ) t ON (t.customerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | 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 AND ipaddr <> 0 GROUP BY ownerid ) s ON (s.ownerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ) WHERE c.deleted = 0 AND (CONCAT(UPPER(c.lastname), ' ', UPPER( [ [ http://c.name/ | http://c.name/ ] | [ http://c.name/ | c.name ] ] )) LIKE UPPER('%a%') OR (UPPER(address) LIKE UPPER('%a%') OR UPPER(post_address) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | va.id ] ] = ca.address_id AND ca.customer_id = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] WHERE UPPER(va.address) LIKE UPPER('%a%'))) OR (UPPER(zip) LIKE UPPER('%a%') OR UPPER(post_zip) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | va.id ] ] = ca.address_id AND ca.customer_id = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] WHERE UPPER(va.zip) LIKE UPPER('%a%'))) OR (UPPER(city) LIKE UPPER('%a%') OR UPPER(post_city) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | va.id ] ] = ca.address_id AND ca.customer_id = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] WHERE UPPER(va.city) LIKE UPPER('%a%'))) OR EXISTS (SELECT 1 FROM customercontacts WHERE customerid = [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] AND customercontacts.type & 8 = 8 AND contact LIKE '%a%')) ORDER BY customername asc Błąd: Unknown column ' [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ' in 'on clause'
lms mailing list [ mailto: [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] | [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] ] [ [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/mailman/listinfo/lms ] | [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/ mailman/listinfo/lms ] ]
-- Pozdrawiam Marcin / nicraM
lms mailing list [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/ mailman/listinfo/lms ] _______________________________________________ lms mailing list [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/ mailman/listinfo/lms ]
-- Pozdrawiam Marcin / nicraM
lms mailing list 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
mysql> SHOW CREATE VIEW customerview; +--------------+---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------+----------------------+----- -----------------+ | View | Create View | character_set_client | collation_connection | +--------------+---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------+----------------------+----- -----------------+ | customerview | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECUR ITY DEFINER VIEW `customerview` AS select `c`.`id` AS `id`,`c`.`extid` AS `extid `,`c`.`lastname` AS `lastname`,`c`.`name` AS `name`,`c`.`status` AS `status`,`c` .`type` AS `type`,`c`.`ten` AS `ten`,`c`.`ssn` AS `ssn`,`c`.`regon` AS `regon`,` c`.`rbe` AS `rbe`,`c`.`rbename` AS `rbename`,`c`.`icn` AS `icn`,`c`.`info` AS `i nfo`,`c`.`notes` AS `notes`,`c`.`creationdate` AS `creationdate`,`c`.`moddate` A S `moddate`,`c`.`creatorid` AS `creatorid`,`c`.`modid` AS `modid`,`c`.`deleted` AS `deleted`,`c`.`message` AS `message`,`c`.`pin` AS `pin`,`c`.`cutoffstop` AS ` cutoffstop`,`c`.`consentdate` AS `consentdate`,`c`.`einvoice` AS `einvoice`,`c`. `invoicenotice` AS `invoicenotice`,`c`.`mailingnotice` AS `mailingnotice`,`c`.`d ivisionid` AS `divisionid`,`c`.`paytime` AS `paytime`,`c`.`paytype` AS `paytype` ,`a1`.`country_id` AS `countryid`,`a1`.`zip` AS `zip`,`a1`.`city` AS `city`,`a1` .`street` AS `street`,`a1`.`house` AS `building`,`a1`.`flat` AS `apartment`,`a2` .`country_id` AS `post_countryid`,`a2`.`zip` AS `post_zip`,`a2`.`city` AS `post_ city`,`a2`.`street` AS `post_street`,`a2`.`house` AS `post_building`,`a2`.`flat` AS `post_apartment`,`a2`.`name` AS `post_name`,`a1`.`address` AS `address`,`a1` .`location` AS `full_address`,`a1`.`postoffice` AS `postoffice`,`a2`.`address` A S `post_address`,`a2`.`location` AS `post_full_address`,`a2`.`postoffice` AS `po st_postoffice` from ((((`customers` `c` join `customer_addresses` `ca1` on(((`c` .`id` = `ca1`.`customer_id`) and (`ca1`.`type` = 1)))) left join `vaddresses` `a 1` on((`ca1`.`address_id` = `a1`.`id`))) left join `customer_addresses` `ca2` on (((`c`.`id` = `ca2`.`customer_id`) and (`ca2`.`type` = 0)))) left join `vaddress es` `a2` on((`ca2`.`address_id` = `a2`.`id`))) where ((not(exists(select 1 from (`customerassignments` `a` join `excludedgroups` `e` on((`a`.`customergroupid` = `e`.`customergroupid`))) where ((`e`.`userid` = `lms_current_user`()) and (`a`. `customerid` = `c`.`id`))))) and (`c`.`type` < 2)) | latin1 | lati n1_swedish_ci | +--------------+---------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------------------------------------------------+----------------------+----- -----------------+ 1 row in set (0.01 sec)
----- Oryginalna wiadomość ----- Od: "Marcin" marcin@nicram.net Do: "lista użytkowników LMS" lms@lists.lms.org.pl Wysłane: wtorek, 9 stycznia, 2018 19:49:00 Temat: Re: [lms] błędy po UPGRADE do aktualnej wersji z GIT
sorki SHOW CREATE VIEW view_name
2018-01-09 18:37 GMT+01:00 Marcin Świerkowski < [ mailto:marcin@itcom.pl | marcin@itcom.pl ] > :
mysql> describe customerview; +-------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | extid | varchar(32) | NO | | | | | lastname | varchar(128) | NO | | | | | name | varchar(128) | NO | | | | | status | smallint(6) | NO | | 0 | | | type | smallint(6) | NO | | 0 | | | ten | varchar(16) | NO | | | | | ssn | varchar(11) | NO | | | | | regon | varchar(255) | NO | | | | | rbe | varchar(255) | NO | | | | | rbename | varchar(255) | NO | | | | | icn | varchar(255) | NO | | | | | info | text | NO | | NULL | | | notes | text | NO | | NULL | | | creationdate | int(11) | NO | | 0 | | | moddate | int(11) | NO | | 0 | | | creatorid | int(11) | YES | | NULL | | | modid | int(11) | YES | | NULL | | | deleted | tinyint(1) | NO | | 0 | | | message | text | NO | | NULL | | | pin | varchar(255) | NO | | 0 | | | cutoffstop | int(11) | NO | | 0 | | | consentdate | int(11) | NO | | 0 | | | einvoice | tinyint(1) | YES | | NULL | | | invoicenotice | tinyint(1) | YES | | NULL | | | mailingnotice | tinyint(1) | YES | | NULL | | | divisionid | int(11) | YES | | NULL | | | paytime | smallint(6) | NO | | -1 | | | paytype | smallint(6) | YES | | NULL | | | countryid | int(11) | YES | | NULL | | | zip | varchar(10) | YES | | NULL | | | city | varchar(100) | YES | | NULL | | | street | varchar(255) | YES | | NULL | | | building | varchar(20) | YES | | NULL | | | apartment | varchar(20) | YES | | NULL | | | post_countryid | int(11) | YES | | NULL | | | post_zip | varchar(10) | YES | | NULL | | | post_city | varchar(100) | YES | | NULL | | | post_street | varchar(255) | YES | | NULL | | | post_building | varchar(20) | YES | | NULL | | | post_apartment | varchar(20) | YES | | NULL | | | post_name | text | YES | | NULL | | | address | varchar(297) | YES | | NULL | | | full_address | varchar(444) | YES | | NULL | | | postoffice | varchar(32) | YES | | NULL | | | post_address | varchar(297) | YES | | NULL | | | post_full_address | varchar(444) | YES | | NULL | | | post_postoffice | varchar(32) | YES | | NULL | | +-------------------+--------------+------+-----+---------+-------+ 48 rows in set (0.00 sec)
----- Oryginalna wiadomość ----- Od: "Marcin" < [ mailto:marcin@nicram.net | marcin@nicram.net ] > Do: "lista użytkowników LMS" < [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] > Wysłane: poniedziałek, 8 stycznia, 2018 8:19:08 Temat: Re: [lms] błędy po UPGRADE do aktualnej wersji z GIT
Błąd: Unknown column ' [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ] ' in 'on clause'
To Cię nie nakierowało? Co Ci drukuje " DESCRIBE customerview "?
2018-01-07 16:56 GMT+01:00 Marcin Świerkowski < [ mailto: [ mailto:marcin@itcom.pl | marcin@itcom.pl ] | [ mailto:marcin@itcom.pl | marcin@itcom.pl ] ] > :
wrzuciłem czystego nowego lms z GIT'a i to samo więc albo mam jakiegoś starego mysql ( na postgres wszystko działa ) albo jest jakiś mismasz w zapytaniu ( niekompatybilny z mysql )
Wersja LMS: 1.11-git Wersja LMSDB: [ tel: [ tel:2018010500 | 2018010500 ] | [ tel:2018010500 | 2018010500 ] ] Wersja MySQL: 5.5.54-0ubuntu0.12.04.1 Wersja PHP: 5.3.10-1ubuntu3.26 Wersja Smarty: 3.1.31
----- Oryginalna wiadomość ----- Od: "Marcin" < [ mailto: [ mailto:marcin@nicram.net | marcin@nicram.net ] | [ mailto:marcin@nicram.net | marcin@nicram.net ] ] > Do: "lista użytkowników LMS" < [ mailto: [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] | [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] ] > Wysłane: środa, 3 stycznia, 2018 11:07:24 Temat: Re: [lms] błędy po UPGRADE do aktualnej wersji z GIT
podejrzewam, że w strukturze nie masz widoku customerview
2018-01-03 10:01 GMT+01:00 Marcin Świerkowski < [ mailto: [ mailto: [ mailto:marcin@itcom.pl | marcin@itcom.pl ] | [ mailto:marcin@itcom.pl | marcin@itcom.pl ] ] | [ mailto: [ mailto:marcin@itcom.pl | marcin@itcom.pl ] | [ mailto:marcin@itcom.pl | marcin@itcom.pl ] ] ] > :
.......?m=customersearch&search=1 wyszukiwanie klientów
Wystąpiły błędy w obsłudze bazy danych! Zapytanie: SELECT [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ] AS id, CONCAT(UPPER(lastname), ' ', [ [ [ http://c.name/ | http://c.name/ ] | [ http://c.name/ | http://c.name/ ] ] | [ [ http://c.name/ | http://c.name/ ] | [ http://c.name/ | c.name ] ] ] ) AS customername, status, full_address, address, zip, city, countryid, [ [ [ http://countries.name/ | http://countries.name/ ] | [ http://countries.name/ | http://countries.name/ ] ] | [ [ http://countries.name/ | http://countries.name/ ] | [ http://countries.name/ | countries.name ] ] ] AS country, cc.email, ccp.phone, ten, ssn, [ [ [ http://c.info/ | http://c.info/ ] | [ http://c.info/ | http://c.info/ ] ] | [ [ http://c.info/ | http://c.info/ ] | [ http://c.info/ | c.info ] ] ] AS info, extid, 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, (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 customerview c LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS email FROM customercontacts WHERE (type & 8 > 0) GROUP BY customerid) cc ON cc.customerid = [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ] LEFT JOIN (SELECT customerid, (GROUP_CONCAT(contact SEPARATOR ',')) AS phone FROM customercontacts WHERE (type & 5 > 0) GROUP BY customerid) ccp ON ccp.customerid = [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ] LEFT JOIN countries ON (c.countryid = [ [ [ http://countries.id/ | http://countries.id/ ] | [ http://countries.id/ | http://countries.id/ ] ] | [ [ http://countries.id/ | http://countries.id/ ] | [ http://countries.id/ | countries.id ] ] ] ) LEFT JOIN (SELECT SUM(value) AS value, customerid FROM cash GROUP BY customerid ) b ON (b.customerid = [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | 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 ( [ [ [ http://t.id/ | http://t.id/ ] | [ http://t.id/ | http://t.id/ ] ] | [ [ http://t.id/ | http://t.id/ ] | [ http://t.id/ | t.id ] ] ] = a.tariffid) LEFT JOIN liabilities l ON ( [ [ [ http://l.id/ | http://l.id/ ] | [ http://l.id/ | http://l.id/ ] ] | [ [ http://l.id/ | http://l.id/ ] | [ http://l.id/ | l.id ] ] ] = a.liabilityid AND a.period != 0) WHERE a.commited = 1 AND a.datefrom <= UNIX_TIMESTAMP() AND (a.dateto > UNIX_TIMESTAMP() OR a.dateto = 0) GROUP BY a.customerid ) t ON (t.customerid = [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | 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 AND ipaddr <> 0 GROUP BY ownerid ) s ON (s.ownerid = [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ] ) WHERE c.deleted = 0 AND (CONCAT(UPPER(c.lastname), ' ', UPPER( [ [ [ http://c.name/ | http://c.name/ ] | [ http://c.name/ | http://c.name/ ] ] | [ [ http://c.name/ | http://c.name/ ] | [ http://c.name/ | c.name ] ] ] )) LIKE UPPER('%a%') OR (UPPER(address) LIKE UPPER('%a%') OR UPPER(post_address) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | http://va.id/ ] ] | [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | va.id ] ] ] = ca.address_id AND ca.customer_id = [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ] WHERE UPPER(va.address) LIKE UPPER('%a%'))) OR (UPPER(zip) LIKE UPPER('%a%') OR UPPER(post_zip) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | http://va.id/ ] ] | [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | va.id ] ] ] = ca.address_id AND ca.customer_id = [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ] WHERE UPPER(va.zip) LIKE UPPER('%a%'))) OR (UPPER(city) LIKE UPPER('%a%') OR UPPER(post_city) LIKE UPPER('%a%') OR EXISTS (SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON [ [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | http://va.id/ ] ] | [ [ http://va.id/ | http://va.id/ ] | [ http://va.id/ | va.id ] ] ] = ca.address_id AND ca.customer_id = [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ] WHERE UPPER(va.city) LIKE UPPER('%a%'))) OR EXISTS (SELECT 1 FROM customercontacts WHERE customerid = [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ] AND customercontacts.type & 8 = 8 AND contact LIKE '%a%')) ORDER BY customername asc Błąd: Unknown column ' [ [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | http://c.id/ ] ] | [ [ http://c.id/ | http://c.id/ ] | [ http://c.id/ | c.id ] ] ] ' in 'on clause'
_______________________________________________ lms mailing list [ mailto: [ mailto: [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] | [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] ] | [ mailto: [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] | [ mailto:lms@lists.lms.org.pl | lms@lists.lms.org.pl ] ] ] [ [ [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/mailman/listinfo/lms ] | [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/mailman/listinfo/lms ] ] | [ [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/mailman/listinfo/lms ] | [ http://lists.lms.org.pl/mailman/listinfo/lms | http://lists.lms.org.pl/mailman/listinfo/lms ] ] ]
W dniu 03.01.2018 o 10:01, Marcin Świerkowski pisze:
Błąd: Unknown column 'c.id' in 'on clause'
Zgaduję, że nie podoba mu się to (nowe) podzapytanie:
SELECT 1 FROM customer_addresses ca JOIN vaddresses va ON va.id = ca.address_id AND ca.customer_id = c.id WHERE ...
Może przeniesienie drugiego warunku złączenia do klauzuli WHERE pomoże. Przypuszczam, że wyszukiwanie po adresie możnaby też skonstruować bez użycia EXISTS.
uczestnicy (4)
-
A.L.E.C -
Marcin -
Marcin Świerkowski -
Robertus