CA Service Management

  • 1.  How do you join on subselected data in Jaspersoft?

    Posted Jan 25, 2019 03:49 PM

    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



  • 2.  Re: How do you join on subselected data in Jaspersoft?

    Posted Jan 27, 2019 11:39 PM

    Hello,

    This is an interesting one.  Why do you only want to get the SLA with the max time to violation?  From an operational point of view the min time is possibly more 'interesting', as it will be the first to be violated.

     

    It is also possible that there may be more than one SLA having the same TTV as the max... so you may get more than one row per ticket anyway.  But leaving those quibbles aside...

     

    I would be inclined to use something like this.  It seems to get a reasonable answer in SQL, but unfortunately I don't currently have a Jaspersoft instance to test it against.  Hope it helps:

    select cr.id as cr_id
          ,sl.id as sl_id
          ,sl.sla_viol_status
          ,sl.time_to_violation
      from call_req cr
      join attached_sla sl
        on cr.id = sl.ticket_id
     where sl.time_to_violation =
     (select max(sl1.time_to_violation)
        from call_req cr1
        join attached_sla sl1
          on cr1.id = sl1.ticket_id
       where cr1.id = cr.id)
    ;

    Regards,

    James



  • 3.  Re: How do you join on subselected data in Jaspersoft?

    Posted Jan 28, 2019 10:09 AM

    Thanks for replying James!  I tried the query you supplied.  I did have to modify it a little because in Jaspersoft it doesn't let you call the sql tables directly, you end up working through a data connector where most of the tables have a different alias, such as "call_req" becomes "cr".  It still doesn't want to play nice though.  This is a different error yet though.  

     

    select cr2.id,sl.sla_viol_status,sl.time_to_violation
    from cr cr2
          join attached_sla sl
             on cr2.id = sl.ticket_id
    where sl.time_to_violation = (select max(sl1.time_to_violation)
                                                    from cr cr1
                                                       join attached_sla sl1
                                                          on cr1.id = sl1.ticket_id
                                                    where cr1.id = cr2.id)

     

    java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]syntax error, unexpected JOIN, expecting $end or UNION at cr2 ... join

     

    It's almost as if Jaspersoft (or the ca data connector) doesn't support table aliasing...  I'm going to try a basic query with a table alias and see what that does.  I'll report back the results here.



  • 4.  Re: How do you join on subselected data in Jaspersoft?

    Posted Jan 28, 2019 10:11 AM

    I tried the super-simple query below and it worked, so table aliasing isn't the problem.


    select cr2.id  from cr cr2



  • 5.  Re: How do you join on subselected data in Jaspersoft?
    Best Answer

    Posted Jan 29, 2019 09:43 PM

    Hello again!

    After putting up a Jaspersoft report server instance, importing the SDM content, creating an SDM data source and installing Jaspersoft Studio, all guided by the docops documentation, I can get results in a query similar to that above if I specify 'inner join' rather than just 'join'.  I imagine this must be some limitation of the JDBC driver.

     

    Here is the successful query:

    SELECT TIK.id,
        TIK.ref_num,
        SLA.sla_viol_status,
        SLA.time_to_violation,
        SLA.ticket_id
    FROM cr as TIK
         inner join attached_sla SLA ON
         TIK.id = SLA.ticket_id
    where SLA.time_to_violation =
    (select max(SLA2.time_to_violation)
        from attached_sla SLA2
        inner join cr CR2
        on SLA2.ticket_id = CR2.id
     where SLA2.ticket_id = TIK.id
    )

    ...and in the Jaspersoft Studio 'Dataset and Query Dialog' the query preview returns the data that I am expecting - after waiting for the TTV daemon to catch up with the service types that I've applied through the ticket priority and an incident area:

    Hope that helps!

    Regards,

    James



  • 6.  Re: How do you join on subselected data in Jaspersoft?

    Posted Feb 05, 2019 12:36 PM

    This works for me.  I was able to derive our full solution out of this.  I think the table aliasing isn't quite as intuitive through this as SQL.  Thank you so much for your help!



  • 7.  Re: How do you join on subselected data in Jaspersoft?

    Posted Feb 05, 2019 09:00 PM

    I'm glad I was able to help.  You are most welcome.