OraFAQ Forum: SQL & PL/SQL » How to retrieve parent and child in different columns when they exist in a single column.

Home » SQL & PL/SQL » SQL & PL/SQL » How to retrieve parent and child in different columns when they exist in a single column. (Db2 9.7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to retrieve parent and child in different columns when they exist in a single column. [message #639784]

Thu, 16 July 2015 06:19

ankita.anand
Messages: 9
Registered: July 2015

Junior Member

My table contains data in one column as follows.

COLUMN_DATA
-------------------------
A00 0 ALIMENTARY
A01 0 STOMATOLOGIC
A02 0 PLASMA
A01A0 PLAIN
A02A0 CANAL
A01A1 ANTACIDS
A01A2 ANTACIDS+Napthalone
A01A3 ANTACIDS+Olive
A02A1 ACIDS
A02A2 PLASMAACIDS

Now there is a relationship between codes. Four level of Hierarchy is maintained. Eg - A01A1 --> A01A0 --> A01 0 --> A00 0

I want to receive data as in the below format:

T4_CODE T4_DESC T3_CODE T3_DESC T2_CODE T2_DESC T1_CODE T1_DESC
--------------------------------------------------------------------------------------
A01A1 ANTACIDS A01A PLAIN A01 STOMATOLOGIC A ALIMENTARY
A01A2 ANTACIDS+Napthalone A01A PLAIN A01 STOMATOLOGIC A ALIMENTARY
A01A3 ANTACIDS+Olive A01A PLAIN A01 STOMATOLOGIC A ALIMENTARY
A02A1 ACIDS A02A CANAL A02 PLASMA A ALIMENTARY
A02A2 PLASMAACIDS A02A CANAL A02 PLASMA A ALIMENTARY

So we need to derive the four level of codes along with their descriptions in different columns. I have also attached sample data for better understanding.
Any help is really appreciated.

Thanks in advance.

Attachment: Scenario.txt
(Size: 0.84KB, Downloaded 1077 times)

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639785 is a reply to message #639784]

Thu, 16 July 2015 06:24

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Hi,

Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags.

And please do not PM to ask the questions, the forum is meant for it.

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639786 is a reply to message #639785]

Thu, 16 July 2015 06:26

ankita.anand
Messages: 9
Registered: July 2015

Junior Member

Ok. Sorry abt that.

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639788 is a reply to message #639786]

Thu, 16 July 2015 06:50

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

What are the rules behind transforming the string?

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639789 is a reply to message #639788]

Thu, 16 July 2015 07:20

ankita.anand
Messages: 9
Registered: July 2015

Junior Member

If u pick record

A01A1 ANTACIDS, here A01A is parent whose description is PLAIN. So TC4_Code = A01A1 and TC4_Desc = ANTACIDS. TC3_Code = A01A and TC3_desc = PLAIN
Now for A01A , Parent is A01 whose description is STOMATOLOGIC. So TC2_Code = A01 and TC2_Desc = STOMATOLOGIC
Similarly, for A01 , parent is A (A00 0 is considered as A) whose description is ALIMENTARY. So TC1_Code = A and TC1_Desc = ALIMENTARY

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639790 is a reply to message #639789]

Thu, 16 July 2015 07:22

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Your title mentioned DBZ, this is an Oracle forum, are you sure your are in the correct forum?

Please post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before read How to use [code] tags and make your code easier to read.

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639791 is a reply to message #639789]

Thu, 16 July 2015 07:25

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

ankita.anand wrote on Thu, 16 July 2015 13:20

A01A1 ANTACIDS, here A01A is parent whose description is PLAIN.

Why? Why is A01A the parent. Explain please.
Quote:

So TC4_Code = A01A1 and TC4_Desc = ANTACIDS.

This is an incomplete sentence, what are you trying to say here?
Quote:

TC3_Code = A01A and TC3_desc = PLAIN

Why? Explain the logic
Quote:

Now for A01A , Parent is A01 whose description is STOMATOLOGIC.

Why, what is the logic that dictates this?
Quote:

So TC2_Code = A01 and TC2_Desc = STOMATOLOGIC

Based on what logic?
etc etc. You get the idea.

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639792 is a reply to message #639791]

Thu, 16 July 2015 07:32

ankita.anand
Messages: 9
Registered: July 2015

Junior Member

It is oracle. The parent is determined by reducing the current ID by a character class (numbers or letters) and any (trailing) number resolving to 0 is removed for parend ID

CREATE TABLE TMP.LC_LOAD (
TEXT VARCHAR(170)
);

INSERT INTO TMP.LC_LOAD (TEXT) VALUES('A00 0 ALIMENTARY');
INSERT INTO TMP.LC_LOAD (TEXT) VALUES('A01 0 STOMATOLOGIC');
INSERT INTO TMP.LC_LOAD (TEXT) VALUES('A02 0 PLASMA');
INSERT INTO TMP.LC_LOAD (TEXT) VALUES('A01A0 PLAIN');
INSERT INTO TMP.LC_LOAD (TEXT) VALUES('A02A0 CANAL');
INSERT INTO TMP.LC_LOAD (TEXT) VALUES('A01A1 ANTACIDS');
INSERT INTO TMP.LC_LOAD (TEXT) VALUES('A01A2 ANTACIDS+Napthalone');
INSERT INTO TMP.LC_LOAD (TEXT) VALUES('A01A3 ANTACIDS+Olive');
INSERT INTO TMP.LC_LOAD (TEXT) VALUES('A02A1 ACIDS');
INSERT INTO TMP.LC_LOAD (TEXT) VALUES('A02A2 PLASMAACIDS');

Hope this helps.

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639793 is a reply to message #639791]

Thu, 16 July 2015 07:33

ankita.anand
Messages: 9
Registered: July 2015

Junior Member

the parent is determined by reducing the current ID by a character class (numbers or letters) and any (trailing) number resolving to 0 is removed for parend ID

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639796 is a reply to message #639793]

Thu, 16 July 2015 07:54

ankita.anand
Messages: 9
Registered: July 2015

Junior Member

Anyone can help me with this.

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639797 is a reply to message #639793]

Thu, 16 July 2015 08:04

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

ankita.anand wrote on Thu, 16 July 2015 13:33

the parent is determined by reducing the current ID by a character class (numbers or letters) and any (trailing) number resolving to 0 is removed for parend ID

OK, define a character class please.

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639798 is a reply to message #639796]

Thu, 16 July 2015 08:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post complete results from SQL below

SELECT * FROM V$VERSION;

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639799 is a reply to message #639798]

Thu, 16 July 2015 08:11

ankita.anand
Messages: 9
Registered: July 2015

Junior Member

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639800 is a reply to message #639797]

Thu, 16 July 2015 08:12

ankita.anand
Messages: 9
Registered: July 2015

Junior Member

It means.. If A01A1 is child, A01A will be parent.
Similarly if A01A is child, A01 will be parent and so on.

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639802 is a reply to message #639797]

Thu, 16 July 2015 08:17

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

There is probably an easier/ more efficient/ tidier/ nicer way of doing this but:

with t as
        (select text
              , case
                        when substr(text, 4, 1)= ' '
                        then rtrim(substr(text, 1,3), '0')
                        else rtrim(substr(text, 1,5), '0')
                end code
              , substr(text, 7) descript
        from    lc_load
        )
      , t2 as
        (select
                case length(t.code)
                        when 1
                        then 1
                        when 3
                        then 2
                        when 4
                        then 3
                        when 5
                        then 4
                end lvl
              , t.*
        from    t
        )
select  t2_4.code
      , t2_4.descript
      , t2_3.code
      , t2_3.descript
      , t2_2.code
      , t2_2.descript
      , t2_1.code
      , t2_1.descript
from    t2 t2_1
join t2 t2_2
on      t2_1.code = substr(t2_2.code, 1, 1)
join t2 t2_3
on      t2_2.code = substr(t2_3.code, 1, 3)
join t2 t2_4
on      t2_3.code = substr(t2_4.code, 1, 4)
where   t2_1.lvl = 1
    and t2_2.lvl = 2
    and t2_3.lvl = 3
    and t2_4.lvl = 4

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639803 is a reply to message #639800]

Thu, 16 July 2015 08:19

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

ankita.anand wrote on Thu, 16 July 2015 14:12

It means.. If A01A1 is child, A01A will be parent.
Similarly if A01A is child, A01 will be parent and so on.

That does not define a character class. That is simply a couple of examples of the content of your 'character class'

[Updated on: Thu, 16 July 2015 08:20]

Report message to a moderator

Re: How to retrieve parent and child in different columns when they exist in a single column. [message #639806 is a reply to message #639803]

Thu, 16 July 2015 08:26

ankita.anand
Messages: 9
Registered: July 2015

Junior Member

This worked. Thaks a ton. Smile

Smile

Report message to a moderator

Previous Topic:	Extraction from BLOB data type
Next Topic:	Wrong results with combination of Boolean, Left Join and To_Char

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 20:28:58 CDT 2024