CA Service Management

 View Only
  • 1.  Jasper Request and Target time Join does not work

    Posted Aug 14, 2019 02:15 AM
    Edited by Radek Mihalik Aug 14, 2019 02:19 AM
    #Hello,

    I would like to create a simply report of request (DB table: call_req / casd object: cr) and ticket related service target(s) (DB table:usp_target_time / casd object: tgt_time) using Jasper Studio.

    I used this SQL query directly on SQL DB server:
    SELECT
    cr.id as Req, cr.ref_num as RefNum, cr.summary as Summary, cr.status as Status,
    tim1.sym as SymTmpl1, tim1.time_left as TimeLeftTmpl1, tim1.target_duration as DurationTmpl1,
    tim2.sym as SymTmpl2, tim2.time_left as TimeLeftTmpl2, tim2.target_duration as DurationTmpl2
    FROM
    [mdb].[dbo].[call_req] cr
    left outer join
    [mdb].[dbo].[usp_target_time] tim1 on tim1.object_id = cr.id and tim1.target_tpl = '400002'
    left outer join
    [mdb].[dbo].[usp_target_time] tim2 on tim2.object_id = cr.id and tim2.target_tpl = '400003'

    If I try to do the same (similar) in Jasper Studio I got an error: The multi-part identifier "tgt_time_mapped_cr.ref_num" could not be bound
    The SELECT I used in Jasper Studio is:

    SELECT casd.tgt_time.request_ref_num, casd.cr.ref_num
    FROM casd.cr
    LEFT OUTER JOIN casd.tgt_time ON casd.tgt_time.request_ref_num = casd.cr.ref_num

    Complete error message:
    java.sql.SQLException: [DataDirect][OpenAccess SDK JDBC Driver][OpenAccess SDK SQL Engine]AHD12002:Error in QueryHandler Servicedesk-1729890684-1 in method fetch_response: AHD04199:An unexpected error occurred. Contact your administrator. [Microsoft SQL Server Native Client 11.0] [ SQL Code=4104 SQL State=42S22] The multi-part identifier "tgt_time_mapped_cr.ref_num" could not be bound.

    Any idea how to change the select to get the requested result?

    Current system: SDM 17.0 + Jasper server 6.4.3



    ------------------------------
    Radek Mihalik
    CZ
    -----------------------------

    ​​#jasper #jasperreportserver #caservicedesk17.0 ​​


  • 2.  RE: Jasper Request and Target time Join does not work
    Best Answer

    Broadcom Employee
    Posted Aug 19, 2019 01:04 AM
    Hello Radek,

    As Jasper is a third party product it is always worth a general search on the internet for advice as well. I tried this search and got some good looking hits (I think!):

    • jasper studio "The multi-part identifier" "could not be bound"

    This one here basically reckons it is around the join statement. But as I'm not a Jasper specialist, I'll leave it to you and others to work out the exact kink:

    https://radacad.com/fixing-the-error-the-multi-part-identifier-could-not-be-bound-in-join-statements

    - - - - <Snip!>
    the reason is that SalesOrderHeader (SH) table is hidden from the third part of the join. and if you want to apply joining condition it will throw the error.

    Resolution is:

    put join condition of each join statement exactly after it. query below is showing fixed version of that statement:
    - - - -</Snip!>

    Thanks, Kyle_R.