Knowledge Category Mapping

[Arunava Saha]

Hello Experts,

 

I am querying table O_INDEXES to get the mapping of relational_id field. In the SQL display(output) I need to convert relational_id with the corresponding "caption" of each segment.

 

For example an entry under relational_id field (Eg. 1-400168-400564-400197) should be displayed like : Top-My Service-Child Service-Grand Child Service.

I tried with several self-join queries but eventually fails. Did anyone try it before? Any leads/helps will be appreciated.

 

Thanks

ArunavaS

[Arunava Saha]

Folks any leads here?

[Michael Mueller]

relational_id in O_INDEXES contains the  tree traversal from the actual node to the top node category.

So in your case ( 1-400168-400564-400197):

category 400197 is a child of 400564 (see parent_id=400564)

category 400564 is a child of 400168 (see parent_id=400168)

category 400168 is a child of 1          (see parent_id=1).

You should be able to get what you are looking for, by using a hierarchical query and the caption column of each row in this tree traversal.

I might be able to write such a query for oracle, but I am not familiar with hierarchical queries in MSSQL.

 

Hope that helps

Regards

..........Michael

[Arunava Saha]

hello Mueller,

 

If you help me with the Oracle query I can certainly convert that in MSSQL as needed.

 

Thanks

ArunavaS

[Michael Mueller]

Hi ArunavaS

The following query might point you in the right direction.

It might not fit exactly to your needs, but it demonstrate the principal.

 

select     id,

              parent_id,

              caption,

              LEVEL,

              SYS_CONNECT_BY_PATH(caption, '-') "Path"
      from O_INDEXES

start with id=1163

connect by  prior parent_id = id ;
 
 where 1163 is the id of the bottom category
and Path is similar to the value you are looking for.

It produces the following output:

id	parent_id	caption	LEVEL	Path
1163	1134	printer	1	-printer
1134	1130	pc	2	-printer-pc
1130	3	sw	3	-printer-pc-sw
3	2	os	4	-printer-pc-sw-os
2	1	area	5	-printer-pc-sw-os-area
1	0	TOP	6	-printer-pc-sw-os-area-TOP

 

 

Hope that helps. Let us know your solution for MS SQL.

Regards

.....Michael

[Arunava Saha]

Hello Mueller,

 

Thanks for the Oracle concept however later I discovered SQL process is entirely different and it lead me to learn PL/SQL in actual . Here we go for the SQL. A function need o be created and use the same function for data display :

 

create or replace FUNCTION [dbo].[Split1](@String varchar(MAX), @Delimiter char(1))       
returns @temptable TABLE (items varchar(MAX))       
as       
begin      
    declare @idx int       
    declare @slice varchar(8000)       

 

    select @idx = 1       
        if len(@String)<1 or @String is null  return       

 

    while @idx!= 0       
    begin       
        set @idx = charindex(@Delimiter,@String)       
        if @idx!=0       
            set @slice = left(@String,@idx - 1)       
        else       
            set @slice = @String       

 

        if(len(@slice)>0)  
            insert into @temptable(Items) values(@slice)       

 

        set @String = right(@String,len(@String) - @idx)       
        if len(@String) = 0 break       
    end   
return
end;

 

use mdb
go
select id,
caption,
relational_id,
xx=STUFF((
        select '-'+ caption
        from o_indexes t outer apply [Split1](e.relational_id,'-') s where t.id = s.items
        FOR XML PATH('')
        )
        ,1,1,'')
from o_indexes   e