1

I have this requirement that I need to attach a new column in select statement. It holds a string series of the disp_order of its children.

I currently have the SQL statement only for the immediate parent only.

SELECT t.*,
       ( SELECT MAX( disp_order )
         FROM   tbl_pattern p
         WHERE  p.order_no   = t.order_no
         AND    p.lvl        = t.lvl - 1
         AND    p.disp_order < t.disp_order ) AS parent
FROM   tbl_pattern t

Here is the table from the said SQL statement query:

+----------+------------+-----+-------------+--------+
| order_no | disp_order | lvl | description | parent |
+----------+------------+-----+-------------+--------+
| RM001-01 |     0      |  1  | HK140904-1A |        |
| RM001-01 |     1      |  2  | HK140904-1B |    0   |
| RM001-01 |     2      |  3  | HK140904-1C |    1   |
| RM001-01 |     3      |  4  | HK140904-1D |    2   |
| RM001-01 |     4      |  5  | HK140904-1E |    3   |
| RM001-01 |     5      |  2  | HK140904-1F |    0   |
| RM001-01 |     6      |  3  | HK140904-1G |    5   |
| RM001-01 |     7      |  3  | HK140904-1H |    5   |
| RM001-01 |     8      |  4  | HK140904-1I |    7   |
| RM001-01 |     9      |  5  | HK140904-1J |    8   |
+----------+------------+-----+-------------+--------+

From the example, the result for the children column should be like this:

+----------+------------+-----+-------------+--------+------------------------+
| order_no | disp_order | lvl | description | parent |        children        |
+----------+------------+-----+-------------+--------+------------------------+
| RM001-01 |     0      |  1  | HK140904-1A |        |    1,2,3,4,5,6,7,8,9   |
| RM001-01 |     1      |  2  | HK140904-1B |    0   |          2,3,4         |
| RM001-01 |     2      |  3  | HK140904-1C |    1   |           3,4          |
| RM001-01 |     3      |  4  | HK140904-1D |    2   |            4           |
| RM001-01 |     4      |  5  | HK140904-1E |    3   |                        |
| RM001-01 |     5      |  2  | HK140904-1F |    0   |         6,7,8,9        |
| RM001-01 |     6      |  3  | HK140904-1G |    5   |                        |
| RM001-01 |     7      |  3  | HK140904-1H |    5   |           8,9          |
| RM001-01 |     8      |  4  | HK140904-1I |    7   |            9           |
| RM001-01 |     9      |  5  | HK140904-1J |    8   |                        |
+----------+------------+-----+-------------+--------+------------------------+

Data representation through hierarchy of sample provided:

1 - HK140904-1A
  └2 - HK140904-1B
    └3 - HK140904-1C
      └4 - HK140904-1D
        └5 - HK140904-1E
  └2 - HK140904-1F
    └3 - HK140904-1G
    └3 - HK140904-1H
      └4 - HK140904-1I
        └5 - HK140904-1J
2
  • If you want people to work on this for you (interesting problem BTW), you could at least provide DDL (CREATE TABLE blah (field_1 type_1...);) and DML (INSERT INTO blah VALUES (val_1....);). Or, you could provide an SQL fiddle (db-fiddle.com, dbfiddle.uk).
    – Vérace
    Oct 12, 2018 at 12:35
  • Again, BTW, there's a ton of stuff out there about SQL for hierarchies!
    – Vérace
    Oct 12, 2018 at 12:43

1 Answer 1

0

For those who encountered the same problem. I did not answer this but someone provided the answer, I'll leave the link here: https://stackoverflow.com/questions/52773824/recursive-query-in-oracle-to-find-children-and-sibling/52776124#52776124

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.