CODEWITHSUNDEEP
sqloracle-database
jax
jax

Reputation: 840

Oracle SQL, getting repeated result on a hierarchical query

I have the following query against two tables workorder and classstructure. I am trying to get a workorder that it's id equals 000000004140094 along with it's classification path. (the parent of that path is 1688). 

SELECT
W.WONUM,
W.STATUS,
W.ASSETNUM,
LPAD (' ', 2 * LEVEL - 1) || SYS_CONNECT_BY_PATH (c.CLASSIFICATIONID, '/') PATH
FROM MAXIMO.WORKORDER W
LEFT  JOIN CLASSSTRUCTURE C ON W.CLASSSTRUCTUREID = C.CLASSSTRUCTUREID
WHERE WONUM ='000000004140094'
START WITH c.parent = '1688'
CONNECT BY PRIOR TO_CHAR (c.CLASSSTRUCTUREID) = c.PARENT

how ever I get the repeated result like below whereas I only need one result (the WONUM is unique):

WONUM       STATUS      ASSETNUM       PATH
000000004140094 COMP        51110          /CM/POSTFAILCM
000000004140094 COMP        51110          /CM/POSTFAILCM
000000004140094 COMP        51110          /CM/POSTFAILCM
000000004140094 COMP        51110          /CM/POSTFAILCM
000000004140094 COMP        51110          /CM/POSTFAILCM
000000004140094 COMP        51110          /CM/POSTFAILCM
000000004140094 COMP        51110          /CM/POSTFAILCM
000000004140094 COMP        51110          /CM/POSTFAILCM

I just wanted to know what I am doing wrong or maybe I have to rewrite my query in general.

edit: structure of two tables

Workorder table:

WONUM       STATUS  ASSETNUM    CLASSSTRUCTUREID
000000000108085 CLOSE   00199928    1000
000000000108247 CLOSE   00202763    1061
000000000108248 CLOSE   00202763    1061
000000000108273 CLOSE   00199790    1000

Classstructure table:

CLASSSTRUCTUREID  PARENT    CLASSIFCATIONID
1688                             FLT
1689              1688           ASSET
1690              1688           PMFLT
1691              1688           CM
1692              1691           POSTFAILCM

Upvotes: 0

Views: 66

Answers (2)

Matthew Hart
Matthew Hart

Reputation: 359

I think your duplication issue is where you specify the condition of your root

START WITH c.parent = '1688'

This isn't specifying the root record, this condition would match all the records one level down (or is it up?) from your root.

To specify your root record it should be

START WITH c.classstructureid = '1688'

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

Do this step by step:

  • Get the path hierarchy (FLT, FLT/ASSET, FLT/PMFLT, FLT/CM, FLT/CM/POSTFAILCM) starting with all root paths (i.e. no parent; only FLT in your sample data)
  • Join to the '000000004140094' record

Using standard SQL's recursive WITH clause:

with paths(classstructureid, path) as
(
  select classstructureid, classifcationid as path
  from classstructure 
  where parent is null
  union all
  select c.classstructureid, p.path || '/' || c.classifcationid as path
  from classstructure c
  join paths p on p.classstructureid = c.parent
)
select
  w.wonum,
  w.status,
  w.assetnum,
  p.path
from workorder w
join paths p on p.classstructureid = w.classstructureid
where w.wnum = '000000004140094';

Upvotes: 1

Related Questions