Jump to content

BIRT Developer Center Download


Photo
- - - - -

Failure Codes (Problem, Cause, Remedy) order issue

Failure code sql problem cause remedy order workorder report codes

  • Please log in to reply
3 replies to this topic

#1 Dave4444

Dave4444

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 20 April 2017 - 12:29 PM

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.



#2 wwilliams

wwilliams

    Junior Member

  • Members
  • 331 posts

Posted 21 April 2017 - 07:12 AM

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



#3 Dave4444

Dave4444

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 25 April 2017 - 12:56 AM

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' }) "



#4 Vineet Joshi

Vineet Joshi

    Advanced Member

  • Members
  • PipPipPip
  • 92 posts

Posted 25 April 2017 - 11:37 AM

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'

  • Dave4444 likes this





Also tagged with one or more of these keywords: Failure, code, sql, problem, cause, remedy, order, workorder, report, codes