Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
Failure Codes (Problem, Cause, Remedy) order issue
Dave4444
<p>Hello,<br>
<br>
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.<br>
<br>
Within the SQL im brining back <em>workorder.problemcode </em>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:<br>
<br><strong>cause</strong><br><strong>problem</strong><br><strong>remedy</strong><br>
<br>
or<br>
<br><strong>remedy</strong><br><strong>cause</strong><br><strong>problem</strong><br>
<br>
Ideally, I want to show the order as<br>
<br><strong>Problem</strong><br><strong>Cause</strong><br><strong>Remedy</strong><br>
<br>
Appreciate any help with this matter.</p>
Find more posts tagged with
Comments
wwilliams
<p>post your SQL, if you are using Oracle use a decode in the order by.</p>
Dave4444
<p>Thanks for looking; as above, I'm sure it's basic a sql function that I haven't got to grips with.</p>
<p> </p>
<p> </p>
<p><span style="color:#2a00ff;"><span style="font-family:consolas;"><span style="font-size:10pt;">SELECT workorder.actfinish, FAILUREREPORT.failurecode, locations.description, workorder.status "</span></span></span></p>
<p><span style="color:#000000;"><span style="font-family:consolas;"><span style="font-size:10pt;">+ </span></span></span><span style="color:#2a00ff;"><span style="font-family:consolas;"><span style="font-size:10pt;">" FROM workorder "</span></span></span></p>
<p><span style="color:#000000;"><span style="font-family:consolas;"><span style="font-size:10pt;">+ </span></span></span><span style="color:#2a00ff;"><span style="font-family:consolas;"><span style="font-size:10pt;">" JOIN locations on WORKORDER.LOCATION=LOCATIONS.LOCATION "</span></span></span></p>
<p><span style="color:#000000;"><span style="font-family:consolas;"><span style="font-size:10pt;">+ </span></span></span><span style="color:#2a00ff;"><span style="font-family:consolas;"><span style="font-size:10pt;">" JOIN FAILUREREPORT on workorder.wonum=FAILUREREPORT.wonum "</span></span></span></p>
<p><span style="color:#000000;"><span style="font-family:consolas;"><span style="font-size:10pt;">+ </span></span></span><span style="color:#2a00ff;"><span style="font-family:consolas;"><span style="font-size:10pt;">" 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' }) "</span></span></span></p>
Vineet Joshi
<div>Select workorder.wonum , problem.failurecode problemcode, Cause.failurecode Cause,</div>
<div>remedy.failurecode as remedy from </div>
<div>workorder</div>
<div>left outer join failurereport problem on problem.wonum = workorder.wonum and problem.siteid = workorder.siteid </div>
<div>and problem.type='PROBLEM'</div>
<div>left outer join failurereport cause on cause.wonum = workorder.wonum and cause.siteid = workorder.siteid and cause.type='CAUSE'</div>
<div>left outer join failurereport remedy on remedy.wonum = workorder.wonum and remedy.siteid = workorder.siteid and remedy.type='REMEDY'</div>
