nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Filtering Distinct Detail Rows (SQL Join)

drusso17

I have a database with 2 different tables. 
 
Table 1 is for workorders. 
WORKORDER TABLE: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Workorder# Location WorkorderDate
ABC123 BuildingA 01/17/2010
JKL456 BuildingJ 01/28/2010
XYZ789 BuildingX 02/03/2010
</pre>
 
Table 2 is for a 4-level failure tree associated with a workorder. 
(The 4 levels are FAILURE->PROBLEM->CAUSE->REMEDY) 
*NOTE: Not all workorders will have all 4-levels (don't ask why, it's just laziness on the part of the techs). Some workorders may not have ANY entries in the Failurecode table (shown below) 
FAILURECODE TABLE: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Workorder# FailureCode Description
ABC123 FAILURE Heating
ABC123 PROBLEM Too Hot
ABC123 CAUSE Distribution System
ABC123 REMEDY Patched hole in air duct
XYZ789 FAILURE Cooling
XYZ789 PROBLEM Too cold
</pre>
 
The report I'd like to generate would look like this: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Workorder: ABC123 Location: BuildingA Date: 01/17/2010
Failure: Heating
Problem: Too Hot
Cause: Distribution System
Remedy: Patched hole in air duct

Workorder: JKL456 Location: BuildingJ Date:01/28/2010
Failure:
Problem:
Cause:
Remedy:

Workorder: XYZ789 Location: BuildingX Date:02/03/2010
Failure: Cooling
Problem: Too Cold
Cause:
Remedy:

</pre>
 
Here's where I need help!! 
 
#1. 
What type of SQL join should I use to pull the correct information from each table (I'm going to assume Left Outer Join since there is ALWAYS a workorder, but not always Failurecodes associated with it. ??). 
#2. 
How do I get it to display like I've shown above? I have tried using a table (with a left outer join) but I get output such as: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Workorder: ABC123 Location: BuildingA Date: 01/17/2010
Failure: Heating
Problem:
Cause:
Remedy:

Workorder: ABC123 Location: BuildingA Date: 01/17/2010
Failure: Too Hot
Problem:
Cause:
Remedy:

Workorder: ABC123 Location: BuildingA Date: 01/17/2010
Failure: Distribution System
Problem:
Cause:
Remedy:

Workorder: ABC123 Location: BuildingA Date: 01/17/2010
Failure: Patched hole in air duct
Problem:
Cause:
Remedy:

Workorder: JKL456 Location: BuildingJ Date:01/28/2010
Failure:
Problem:
Cause:
Remedy:

Workorder: XYZ789 Location: BuildingX Date:02/03/2010
Failure: Cooling
Problem:
Cause:
Remedy:

Workorder: XYZ789 Location: BuildingX Date:02/03/2010
Failure: Too Cold
Problem:
Cause:
Remedy:

</pre>
 
I hope the examples are good enough for you to grasp what I am attempting to do. Unfortunately I have no way to change the stupid way this database (Maximo 7.1) lays these fields out. Would be nicer if it could just be included under the workorder, but oh well! 
Thanks in advance for any and all help!

Find more posts tagged with

Comments

Hans_vd

#1: You are absolutely right!

#2: You will need 4 left outer joins to the same table (FAILURECODE) in your query, one for each level, all 4 will have a different line in the WHERE clause (first one has LEVEL = 'FAILURE', second one has LEVEL = 'PROBLEM', ...)

In case you don't get what I mean, please post your query and I'll help you work it out

drusso17

Thanks! I totally did not even think of doing 4 joins (DUH!) I'll try it out at work tomorrow and let you know how it goes!

Thanks again!

drusso17

OK, after going back and looking at the database, I realized that I made some errors in my original post. That's what I get for trying to sketch it out from memory. There are actually 3 tables. The main WorkOrder table includes the top-level FAILURE. The FailureReport table (which I incorretly called FailureCode in my first post) has all of the sub-levels (problem, cause, remedy). The FailureCode table (the real one) contains the descriptions of each failurecode as seen below. 
 
Here's the updated version: 
 
WORKORDER TABLE: (Notice the FAILURE part is included in the workorder) 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Workorder# Location WorkorderDate Failurecode etc...
ABC123 BuildingA 01/17/2010 HEATING
JKL456 BuildingJ 01/28/2010
XYZ789 BuildingX 02/03/2010 COOLING
</pre>
 
FAILUREREPORT TABLE: (previously called FailureCode in my first post) 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Workorder# Type FailureCode
ABC123 PROBLEM TOO HOT
ABC123 CAUSE DIST
ABC123 REMEDY REPAIR
XYZ789 PROBLEM TOO COLD
</pre>
 
FAILURECODE TABLE: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
FailureCode Description
COOLING All Cooling Issues
HEATING All Heating Issues
TOO COLD Room is too cold
TOO HOT Room is too hot
DIST Distribution System
REPAIR Repaired Duct
</pre>
 
 
The updated report should look like: 
<pre class='_prettyXprint _lang-auto _linenums:0'>
Workorder: ABC123 Location: BuildingA Date: 01/17/2010
Failure: HEATING All Heating Issues
Problem: TOO HOT Room is too hot
Cause: DIST Distribution System
Remedy: REPAIR Repaired Duct

Workorder: JKL456 Location: BuildingJ Date:01/28/2010
Failure:
Problem:
Cause:
Remedy:

Workorder: XYZ789 Location: BuildingX Date:02/03/2010
Failure: COOLING All Cooling Issues
Problem: TOO COLD Room is too cold
Cause:
Remedy:

</pre>
 
Here's the SQL I have so far: (Waiting for them to get the Terminal Server back up so I can actually test and see if this works) 
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT
dbo.workorder.wonum,
dbo.workorder.wo3,
dbo.workorder.description,
dbo.workorder.worktype,
dbo.workorder.location,
dbo.workorder.actfinish,
dbo.workorder.wopriority,
dbo.workorder.reportdate,
dbo.workorder.failurecode,
fail.description,
problem.failurecode,
failproblem.description,
cause.failurecode,
failcause.description,
remedy.failurecode,
failremedy.description

FROM
dbo.workorder

//Get Failure: description(Failure: is already part of dbo.workorder)
LEFT OUTER JOIN
dbo.failurecode as fail on dbo.workorder.failurecode=fail.failurecode

//Get Problem:
LEFT OUTER JOIN
dbo.failurereport as problem on dbo.workorder.wonum=problem.wonum
//Get Problem: description:
LEFT OUTER JOIN
dbo.failurecode as failproblem on problem.failurecode=failproblem.failurecode

//Get Cause:
LEFT OUTER JOIN
dbo.failurereport as cause on dbo.workorder.wonum=cause.wonum
//Get Cause: description:
LEFT OUTER JOIN
dbo.failurecode as failcause on cause.failurecode=failcause.failurecode

//Get Remedy:
LEFT OUTER JOIN
dbo.failurereport as remedy on dbo.workorder.wonum=remedy.wonum
//Get Remedy: description:
LEFT OUTER JOIN
dbo.failurecode as failremedy on remedy.failurecode=failremedy.failurecode

WHERE
problem.type='PROBLEM'
AND
cause.type='CAUSE'
AND
remedy.type='REMEDY'
AND
(some other miscellaneous criteria)
</pre>
 
I know there are lots of FailureCode fields and they of course relate to different things on different tables so I know it's a little confusing, but hopefully I've cleared it up a little bit!

Hans_vd

Well, seems like you've got it all worked out yourself now!

Let me know if you need any further help on this

drusso17

OK, so I finally got it uploaded and "working". The only problem with the current code is that it does not pull the workorder information if it does not have all 4 failure levels. (But the ones that DO have all 4 look marvelous!) 
 
*Side note: There is a filter that selects only work orders that were completed in the last 24 hours. It should not affect this at all, but I thought I'd throw it out there for completeness. 
 
Here is the SQL statement in its entirety: (I changed a few names to make it easier) 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select
dbo.workorder.wonum,
dbo.workorder.wo3,
dbo.workorder.description,
dbo.workorder.worktype,
dbo.workorder.status,
dbo.workorder.location,
dbo.workorder.actfinish,
dbo.workorder.wopriority,
dbo.workorder.reportdate,
dbo.workorder.failurecode,
locs.description,
fail.description,
problemcode.description,
causecode.description,
remedycode.description

from
dbo.workorder
--Bring in Address of Location:
left outer join dbo.locations as locs on dbo.workorder.location=locs.location

--Bring in Description of Failure Class:
left outer join dbo.failurecode as fail on dbo.workorder.failurecode=fail.failurecode

--Bring in Problem:
left outer join dbo.failurereport as problem on dbo.workorder.wonum=problem.wonum
--Bring in Problem Description:
left outer join dbo.failurecode as problemcode on problem.failurecode=problemcode.failurecode

--Bring in Cause:
left outer join dbo.failurereport as cause on dbo.workorder.wonum=cause.wonum
--Bring in Cause Description:
left outer join dbo.failurecode as causecode on cause.failurecode=causecode.failurecode

--Bring in Remedy:
left outer join dbo.failurereport as remedy on dbo.workorder.wonum=remedy.wonum
--Bring in Remedy Description:
left outer join dbo.failurecode as remedycode on remedy.failurecode=remedycode.failurecode

where
dbo.workorder.status='COMP'
and
problem.type='PROBLEM'
and
cause.type='CAUSE'
and
remedy.type='REMEDY'
</pre>

Hans_vd

Drusso, 
 
I'm not used to this syntax, but I think you have to add the where clauses on the outer joined tables to the join clause itself, instead of to the where clause, like this: 
 
<pre class='_prettyXprint _lang-auto _linenums:0'>
select
dbo.workorder.wonum,
dbo.workorder.wo3,
dbo.workorder.description,
dbo.workorder.worktype,
dbo.workorder.status,
dbo.workorder.location,
dbo.workorder.actfinish,
dbo.workorder.wopriority,
dbo.workorder.reportdate,
dbo.workorder.failurecode,
locs.description,
fail.description,
problemcode.description,
causecode.description,
remedycode.description

from
dbo.workorder
--Bring in Address of Location:
left outer join dbo.locations as locs on dbo.workorder.location=locs.location

--Bring in Description of Failure Class:
left outer join dbo.failurecode as fail on dbo.workorder.failurecode=fail.failurecode

--Bring in Problem:
left outer join dbo.failurereport as problem on dbo.workorder.wonum=problem.wonum and problem.type='PROBLEM'
--Bring in Problem Description:
left outer join dbo.failurecode as problemcode on problem.failurecode=problemcode.failurecode

--Bring in Cause:
left outer join dbo.failurereport as cause on dbo.workorder.wonum=cause.wonum and cause.type='CAUSE'
--Bring in Cause Description:
left outer join dbo.failurecode as causecode on cause.failurecode=causecode.failurecode

--Bring in Remedy:
left outer join dbo.failurereport as remedy on dbo.workorder.wonum=remedy.wonum and remedy.type='REMEDY'
--Bring in Remedy Description:
left outer join dbo.failurecode as remedycode on remedy.failurecode=remedycode.failurecode

where
dbo.workorder.status='COMP'
</pre>

drusso17

Awesome! Worked perfectly! Thanks for all your help!

SOLVED!

Hans_vd

Glad I could help!