I am building a Jaspersoft report to show open service desk tickets and their aging properties. One of these properties is the time to violation. My base query is below. I originally added a basic inner join to the attached_sla table on the cr.id = attached_sla.ticket_id. This produces more rows than the base query because attached_sla can have more than one row per ticket. In sql server, I could use a subselect to get around this issue and just get the top result for each ticket and join on that subquery.
SELECT cr.type_symbol, cr.ref_num, cr.requestor_combo_name, cr.affected_end_user_combo_name, cr.category_symbol, cr.status_symbol, cr.urgency_symbol, cr.open_date, cr.sla_violation, cr.priority_symbol, cr.reported_by_combo_name, cr.group_name, cr.assignee_combo_name AS Assignee, cr.configuration_item_name, cr.rootcause_symbol, attached_sla.time_to_violation, attached_sla.request_sla_violation
FROM cr
WHERE cr.type IN ('I','R') AND cr.status_active = 1 AND cr.status_resolved = 0
ORDER BY cr.sla_violation DESC
Trying to add the subselect I get a couple different errors, depending on how I alias the subquery. I will post the query, then the error for each.
Perhaps someone could show me the error of my ways, or point me toward the proper syntax here. Thanks!!!!
SELECT
cr.type_symbol, cr.ref_num, cr.requestor_combo_name, cr.affected_end_user_combo_name, cr.category_symbol, cr.status_symbol, cr.urgency_symbol, cr.open_date, cr.sla_violation, cr.priority_symbol, cr.reported_by_combo_name, cr.group_name, cr.assignee_combo_name AS Assignee, cr.configuration_item_name, cr.rootcause_symbol, attached_sla.time_to_violation, attached_sla.request_sla_violation
FROM cr
INNER JOIN (select attached_sla.ticket_id, MAX(attached_sla.time_to_violation), attached_sla.request_sla_violation
from attached_sla
group by attached_sla.ticket_id, attached_sla.request_sla_violation
) t
ON cr.id = t.ticket_id
WHERE cr.type IN ('I','R') AND cr.status_active = 1 AND cr.status_resolved = 0
ORDER BY cr.sla_violation DESC
java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]AHD12008:Attribute time_to_violation referenced by Majic SQL query not found in object(s) T at . time_to_violation
SELECT
cr.type_symbol, cr.ref_num, cr.requestor_combo_name, cr.affected_end_user_combo_name, cr.category_symbol, cr.status_symbol, cr.urgency_symbol, cr.open_date, cr.sla_violation, cr.priority_symbol, cr.reported_by_combo_name, cr.group_name, cr.assignee_combo_name AS Assignee, cr.configuration_item_name, cr.rootcause_symbol, attached_sla.time_to_violation, attached_sla.request_sla_violation
FROM cr
INNER JOIN (select attached_sla.ticket_id, MAX(attached_sla.time_to_violation), attached_sla.request_sla_violation
from attached_sla
group by attached_sla.ticket_id, attached_sla.request_sla_violation
) as t
ON cr.id = t.ticket_id
WHERE cr.type IN ('I','R') AND cr.status_active = 1 AND cr.status_resolved = 0
ORDER BY cr.sla_violation DESC
Notice the only thing is that I added the word "as". This produces a different error.
java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]syntax error, unexpected AS, expecting NAME or QSTRING at ) ... as
Now I try to wrap the table alias 't' in double quotes, and I get the same error as before.
SELECT
cr.type_symbol, cr.ref_num, cr.requestor_combo_name, cr.affected_end_user_combo_name, cr.category_symbol, cr.status_symbol, cr.urgency_symbol, cr.open_date, cr.sla_violation, cr.priority_symbol, cr.reported_by_combo_name, cr.group_name, cr.assignee_combo_name AS Assignee, cr.configuration_item_name, cr.rootcause_symbol, attached_sla.time_to_violation, attached_sla.request_sla_violation
FROM cr
INNER JOIN (select attached_sla.ticket_id, MAX(attached_sla.time_to_violation), attached_sla.request_sla_violation
from attached_sla
group by attached_sla.ticket_id, attached_sla.request_sla_violation
) as "t"
ON cr.id = t.ticket_id
WHERE cr.type IN ('I','R') AND cr.status_active = 1 AND cr.status_resolved = 0
ORDER BY cr.sla_violation DESC
java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]syntax error, unexpected AS, expecting NAME or QSTRING at ) ... as