Failure Codes (Problem, Cause, Remedy) order issue

Hello,

 

I'm still getting to grips with eclipse and SQL, but I would really appreciate some advice on how to correctly order the problem, cause & remedy data within a table element.

 

Within the SQL im brining back workorder.problemcode in the select staetment. This will bring back the problem, cause and remedy fields, but unfortunately, on the report it will list them in the wrong order; for example:

 
cause
problem
remedy

 

or

 
remedy
cause
problem

 

Ideally, I want to show the order as

 
Problem
Cause
Remedy

 

Appreciate any help with this matter.


Comments

  • wwilliamswwilliams Junior Member
    edited April 21

    post your SQL, if you are using Oracle use a decode in the order by.


  • Thanks for looking; as above, I'm sure it's basic a sql function that I haven't got to grips with.


     


     


    SELECT workorder.actfinish, FAILUREREPORT.failurecode, locations.description, workorder.status "


    + " FROM workorder "


    + " JOIN locations on WORKORDER.LOCATION=LOCATIONS.LOCATION "


    + " JOIN FAILUREREPORT on workorder.wonum=FAILUREREPORT.wonum "


    + " where (workorder.reportdate <= { ts '2017-06-01 23:59:59.000' }) and (workorder.status not in ('WAPPR', 'CAN')) and (actfinish >= { ts '2015-04-01 00:00:00.000' }) "

  • Select   workorder.wonum , problem.failurecode problemcode, Cause.failurecode Cause,

    remedy.failurecode as remedy from 

    workorder

    left outer join failurereport problem on problem.wonum = workorder.wonum and problem.siteid = workorder.siteid 

    and problem.type='PROBLEM'

    left outer join failurereport cause on cause.wonum = workorder.wonum and cause.siteid = workorder.siteid and cause.type='CAUSE'

    left outer join failurereport remedy on remedy.wonum = workorder.wonum and remedy.siteid = workorder.siteid and remedy.type='REMEDY'

Sign In or Register to comment.