custom search show error Unknown column 'Z1.ticket_id' in 'field list

ichtus · Nov 13, 2019

osticket 1.12.3
custom search when using keyword as in the screenshot

error from debug notif email:
[SELECT COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.dept_id IN (13, 17) THEN A1.ticket_id END) AS q20, COUNT(DISTINCT CASE WHEN A1.team_id IN (42) AND A2.state = 'open' THEN A1.ticket_id END) AS q105, COUNT(DISTINCT CASE WHEN A1.team_id IN (27) AND A2.state = 'open' THEN A1.ticket_id END) AS q106, COUNT(DISTINCT CASE WHEN A1.team_id IN (43) AND A2.state = 'open' THEN A1.ticket_id END) AS q107, COUNT(DISTINCT CASE WHEN A2.state IN ('open', 'closed') AND A1.team_id IN (45) THEN A1.ticket_id END) AS q108, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A1.team_id IN (47) THEN A1.ticket_id END) AS q109, COUNT(DISTINCT CASE WHEN A2.state IN ('open', 'closed') AND A1.team_id IN (37) THEN A1.ticket_id END) AS q112, COUNT(DISTINCT CASE WHEN A2.state IN ('open', 'closed') AND A1.team_id IN (38) THEN A1.ticket_id END) AS q113, COUNT(DISTINCT CASE WHEN A2.id IN (15, 47, 16) AND A1.team_id IN (45, 43, 42, 27, 47) AND A1.ticket_id = Z1.ticket_id THEN A1.ticket_id END) AS q120, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' THEN A1.ticket_id END) AS q5, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) THEN A1.ticket_id END) AS q7, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND A1.created BETWEEN '2019-11-13 02:00:00' AND '2019-11-14 01:59:59' THEN A1.ticket_id END) AS q83, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND A1.created BETWEEN '2019-11-11 02:00:00' AND '2019-11-18 01:59:59' THEN A1.ticket_id END) AS q84, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND A1.created BETWEEN (NOW() - INTERVAL '1' WEEK) AND NOW() THEN A1.ticket_id END) AS q85, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND A1.created BETWEEN '2019-11-01 02:00:00' AND '2019-12-01 01:59:59' THEN A1.ticket_id END) AS q86, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND A1.created BETWEEN '2019-10-01 02:00:00' AND '2019-11-01 01:59:59' THEN A1.ticket_id END) AS q87, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND NOT A1.staff_id IN (1) AND A1.created BETWEEN '2019-10-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q88, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND A1.isoverdue = '1' THEN A1.ticket_id END) AS q54, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND A1.staff_id IN (1) THEN A1.ticket_id END) AS q6, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND A2.id IN (48, 47) THEN A1.ticket_id END) AS q119, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND A2.id IN (44, 45, 2) THEN A1.ticket_id END) AS q102, COUNT(DISTINCT CASE WHEN (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND (A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A1.staff_id IN (1)) AND A2.state = 'open' AND A2.id IN (6, 15) AND A1.dept_id = 8 THEN A1.ticket_id END) AS q104, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) THEN A1.ticket_id END) AS q24, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') THEN A1.ticket_id END) AS q38, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.created BETWEEN '2019-11-13 02:00:00' AND '2019-11-14 01:59:59' THEN A1.ticket_id END) AS q82, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.created BETWEEN '2019-11-11 02:00:00' AND '2019-11-18 01:59:59' THEN A1.ticket_id END) AS q80, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.created BETWEEN (NOW() - INTERVAL '1' WEEK) AND NOW() THEN A1.ticket_id END) AS q79, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.created BETWEEN '2019-11-01 02:00:00' AND '2019-12-01 01:59:59' THEN A1.ticket_id END) AS q78, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.created BETWEEN '2019-10-01 02:00:00' AND '2019-11-01 01:59:59' THEN A1.ticket_id END) AS q77, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.state IN ('open', 'closed', 'archived') AND A1.created BETWEEN '2019-10-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q81, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.isoverdue = '1' THEN A1.ticket_id END) AS q40, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (24, 25) THEN A1.ticket_id END) AS q26, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (39, 40) THEN A1.ticket_id END) AS q52, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (46) THEN A1.ticket_id END) AS q103, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) THEN A1.ticket_id END) AS q25, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.created BETWEEN '2019-11-13 02:00:00' AND '2019-11-14 01:59:59' THEN A1.ticket_id END) AS q59, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.created BETWEEN '2019-11-11 02:00:00' AND '2019-11-18 01:59:59' THEN A1.ticket_id END) AS q60, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.created BETWEEN (NOW() - INTERVAL '1' WEEK) AND NOW() THEN A1.ticket_id END) AS q61, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.created BETWEEN '2019-11-01 02:00:00' AND '2019-12-01 01:59:59' THEN A1.ticket_id END) AS q62, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.created BETWEEN '2019-10-01 02:00:00' AND '2019-11-01 01:59:59' THEN A1.ticket_id END) AS q67, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.created BETWEEN '2019-10-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q64, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id = 8 THEN A1.ticket_id END) AS q98, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id = 6 THEN A1.ticket_id END) AS q100, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id = 5 THEN A1.ticket_id END) AS q99, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND NOT A1.dept_id IN (6, 5, 8, 2, 15) THEN A1.ticket_id END) AS q101, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id = 2 THEN A1.ticket_id END) AS q96, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (6, 48, 39, 24, 15, 47, 27) AND A1.dept_id = 15 THEN A1.ticket_id END) AS q97, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (9, 38, 17, 41, 30) THEN A1.ticket_id END) AS q27, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (38, 41) THEN A1.ticket_id END) AS q51, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (32, 33) THEN A1.ticket_id END) AS q28, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (10, 18) THEN A1.ticket_id END) AS q42, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND (A1.team_id IN (25, 41, 32, 5, 9, 28, 14, 35, 23) OR A1.staff_id IN (40)) THEN A1.ticket_id END) AS q48, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND (A1.team_id IN (39, 10, 24, 14) OR A1.staff_id IN (51, 14, 44, 24)) THEN A1.ticket_id END) AS q29, COUNT(DISTINCT CASE WHEN A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A1.dept_id IN (1, 18, 6, 13, 20, 14, 16, 17, 7, 19, 12, 5, 8, 2, 15) AND A2.id IN (44, 6, 48, 39, 37, 9, 38, 32, 10, 24, 46, 15, 47, 36, 40, 17, 41, 33, 18, 30, 13, 22, 45, 1) AND A2.id IN (37, 36) AND A2.state = 'closed' THEN A1.ticket_id END) AS q46, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) THEN A1.ticket_id END) AS q23, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' THEN A1.ticket_id END) AS q37, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A1.est_duedate >= (NOW() + INTERVAL '3' DAY) THEN A1.ticket_id END) AS q36, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A1.est_duedate BETWEEN '2019-11-11 02:00:00' AND '2019-11-18 01:59:59' THEN A1.ticket_id END) AS q32, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A1.est_duedate BETWEEN '2019-11-01 02:00:00' AND '2019-12-01 01:59:59' THEN A1.ticket_id END) AS q33, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A1.est_duedate BETWEEN '2019-10-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q35, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A1.est_duedate BETWEEN '2019-01-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q34, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.state = 'open' AND A1.est_duedate BETWEEN '2018-01-01 02:00:00' AND '2019-01-01 01:59:59' THEN A1.ticket_id END) AS q49, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A1.isoverdue = '1' THEN A1.ticket_id END) AS q39, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.id IN (35, 34, 27) THEN A1.ticket_id END) AS q43, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) THEN A1.ticket_id END) AS q75, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A1.reopened < NOW() THEN A1.ticket_id END) AS q74, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A1.reopened BETWEEN '2019-11-13 02:00:00' AND '2019-11-14 01:59:59' THEN A1.ticket_id END) AS q72, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A1.reopened BETWEEN '2019-11-11 02:00:00' AND '2019-11-18 01:59:59' THEN A1.ticket_id END) AS q71, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A1.reopened BETWEEN (NOW() - INTERVAL '1' WEEK) AND NOW() THEN A1.ticket_id END) AS q89, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A1.reopened BETWEEN '2019-11-01 02:00:00' AND '2019-12-01 01:59:59' THEN A1.ticket_id END) AS q70, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A1.reopened BETWEEN '2019-10-01 02:00:00' AND '2019-11-01 01:59:59' THEN A1.ticket_id END) AS q90, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A1.reopened BETWEEN '2019-10-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q69, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND NOT A2.id IN (27, 29) AND A1.reopened BETWEEN '2019-01-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q73, COUNT(DISTINCT CASE WHEN A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.id IN (8, 35, 34, 16, 27, 29) AND A1.dept_id IN (6, 13, 17, 7, 12, 5, 2, 15) AND A2.id IN (27, 29) THEN A1.ticket_id END) AS q50, COUNT(DISTINCT CASE WHEN A2.state = 'open' THEN A1.ticket_id END) AS q1, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' THEN A1.ticket_id END) AS q65, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.created BETWEEN '2019-11-13 02:00:00' AND '2019-11-14 01:59:59' THEN A1.ticket_id END) AS q56, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.created BETWEEN '2019-11-12 02:00:00' AND '2019-11-13 01:59:59' THEN A1.ticket_id END) AS q57, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.created BETWEEN '2019-11-11 02:00:00' AND '2019-11-18 01:59:59' THEN A1.ticket_id END) AS q55, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.created BETWEEN (NOW() - INTERVAL '1' WEEK) AND NOW() THEN A1.ticket_id END) AS q58, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.created BETWEEN '2019-11-01 02:00:00' AND '2019-12-01 01:59:59' THEN A1.ticket_id END) AS q63, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.created BETWEEN '2019-10-01 02:00:00' AND '2019-11-01 01:59:59' THEN A1.ticket_id END) AS q68, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A2.state = 'open' AND A1.created BETWEEN '2019-10-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q91, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isoverdue = '1' THEN A1.ticket_id END) AS q4, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '0' THEN A1.ticket_id END) AS q2, COUNT(DISTINCT CASE WHEN A2.state = 'open' AND A2.state = 'open' AND A1.isanswered = '1' THEN A1.ticket_id END) AS q3, COUNT(DISTINCT CASE WHEN A2.state = 'closed' THEN A1.ticket_id END) AS q8, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' THEN A1.ticket_id END) AS q66, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-11-13 02:00:00' AND '2019-11-14 01:59:59' THEN A1.ticket_id END) AS q9, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-11-12 02:00:00' AND '2019-11-13 01:59:59' THEN A1.ticket_id END) AS q10, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-11-11 02:00:00' AND '2019-11-18 01:59:59' THEN A1.ticket_id END) AS q11, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN (NOW() - INTERVAL '1' WEEK) AND NOW() THEN A1.ticket_id END) AS q92, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-11-01 02:00:00' AND '2019-12-01 01:59:59' THEN A1.ticket_id END) AS q12, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-10-01 02:00:00' AND '2019-11-01 01:59:59' THEN A1.ticket_id END) AS q93, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-10-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q13, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-07-01 02:00:00' AND '2019-10-01 01:59:59' THEN A1.ticket_id END) AS q94, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2019-01-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q14, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A2.state = 'closed' AND A1.closed BETWEEN '2018-01-01 02:00:00' AND '2019-01-01 01:59:59' THEN A1.ticket_id END) AS q76, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.id IN (11, 19) AND A1.closed BETWEEN '2019-01-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q45, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.id IN (7, 20) AND A1.closed BETWEEN '2019-01-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q30, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.id IN (26, 21, 28) AND A1.closed BETWEEN '2019-01-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q31, COUNT(DISTINCT CASE WHEN A2.state = 'closed' AND A2.state = 'closed' AND A2.id IN (42, 43) AND A1.closed BETWEEN '2019-01-01 02:00:00' AND '2020-01-01 01:59:59' THEN A1.ticket_id END) AS q53 FROM vis_ticket A1 JOIN vis_ticket_status A2 ON (A1.status_id = A2.id) LEFT JOIN vis_thread A3 ON (A3.object_type = 'T' AND A1.ticket_id = A3.object_id) LEFT JOIN vis_thread_referral A4 ON (A3.id = A4.thread_id) LEFT JOIN vis_staff A5 ON (A4.object_type = 'S' AND A4.object_id = A5.staff_id) LEFT JOIN vis_team A6 ON (A4.object_type = 'E' AND A4.object_id = A6.team_id) LEFT JOIN vis_department A7 ON (A4.object_type = 'D' AND A4.object_id = A7.id) WHERE (A2.state IN ('open', 'closed') AND (A1.staff_id = 1 OR A5.staff_id = 1 OR A1.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48) OR A6.team_id IN (11, 16, 27, 10, 43, 23, 4, 2, 14, 8, 28, 3, 9, 12, 32, 13, 7, 5, 30, 24, 25, 6, 31, 1, 45, 42, 35, 37, 38, 39, 40, 41, 44, 46, 47, 48))) OR (A2.state IN ('open', 'closed') AND (A1.dept_id IN (1, 2, 5, 6, 7, 8, 11, 12, 13, 14, 15, 16, 18, 17, 19, 20) OR A7.id IN (1, 2, 5, 6, 7, 8, 11, 12, 13, 14, 15, 16, 18, 17, 19, 20)))]

Unknown column 'Z1.ticket_id' in 'field list' 
 
---- Backtrace ---- 
#0 (root)/include/mysqli.php(199): osTicket->logDBError('DB Error #1054', '[SELECT COUNT(D...') 
#1 (root)/include/class.orm.php(3455): db_query('SELECT COUNT(DI...', true, true) 
#2 (root)/include/class.orm.php(3502): MySqlExecutor->execute() 
#3 (root)/include/class.orm.php(2085): MySqlExecutor->getArray() 
#4 (root)/include/class.orm.php(2035): HashArrayIterator->{closure}() 
#5 (root)/include/class.orm.php(2014): CallbackSimpleIterator->next() 
#6 (root)/include/class.orm.php(2023): CallbackSimpleIterator->rewind() 
#7 (root)/include/class.orm.php(1695): CallbackSimpleIterator->valid() 
#8 (root)/include/class.orm.php(1705): CachedResultSet->fillTo(9223372036854775807) 
#9 (root)/include/class.orm.php(1328): CachedResultSet->asArray() 
#10 (root)/include/class.orm.php(1351): QuerySet->all() 
#11 (root)/include/class.search.php(925): QuerySet->one() 
#12 (root)/include/class.search.php(865): SavedQueue::counts(Object(StaffSession), true) 
#13 (root)/include/staff/templates/queue-tickets.tmpl.php(102): SavedQueue->getCount(Object(StaffSession)) 
#14 (root)/scp/tickets.php(531): require_once('/root/...')

ntozier · Nov 13, 2019

error:

ichtus Unknown column 'Z1.ticket_id' in 'field list'

reference in the query:
A1.ticket_id = Z1.ticket_id THEN A1.ticket_id END) AS q120,

There appears to be something wrong with your custom queue # 120. You would want to make note of what it is, and then delete it and re-create it.

jnaranjo · Nov 13, 2024

I'd like to know if this is bug with the version v1141 or other:
DB Error 1054 Unknown column Z1.ticket_id in field list

I have read the forums but all of them say that we have to eliminate the custom queue. But they dont say if that's a problem wtih the version or it's a bug. I'like to know how or why that ocurrs

Thanks

KevinTheJedi · Nov 13, 2024

jnaranjo

If you cannot replicate it using the latest stable release of osTicket then it's safe to assume it was a bug that has since been addressed. It's been too long for me to remember so other than simply testing it on the latest release you'd have to go back through the commit history to see.

Cheers.