• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

Oracle / MySQL - Peculiar View

Please consider the following:

There are two tables: 1) DATA Table and PREFIX Table

The "DATA" Table is a one with Recursive Relationship (parent child relation exists). The PREFIX Table is linked to "DATA" Table through the Prefix ID

PREFIX Table Contents:

Prefix ID  ||  Prefix
1               ||  Hon'ble
2               ||  Justice
3                ||  Dr.

DATA Table Contents:

Data ID    |   Data Parent ID  |  Prefix ID  (FK)  |   Data
1               |  Null                      |       1                   |     AAAA
2               |  1                           |       2                   |    BBBB
3                |  2                           |       3                    |    CCCC

Here is the tricky part...

I desire to have a view of the DATA Table in the following manner:

Data ID   |  Data Value
1              |    Hon'ble AAAA
2              |    Hon'ble AAAA (BBBB)
3               |    Hon'ble AAAA (BBBB)(CCCC)


Note 1: The Prefix used... is ALWAYS of the TOP MOST PARENT

Note 2: The Parent Record (Where Parent ID is null) - is not enclosed in Round Brackets -- AAAA instead of (AAAA)

Note 3: The children - are always encapsulated in Round Bracket

Note 4 - Related to Note 1 - Children Prefixes are NEVER USED.



Any Solutions?

Thanks in Advance.

0
Adwait Chitaley
Asked:
Adwait Chitaley
  • 5
  • 2
1 Solution
 
Shaju KumbalathCommented:


SELECT DATA_ID,PREFIX,substr(SYS_CONNECT_BY_PATH(DATA, ','),2)
FROM DATA A,PREFIX B WHERE A.PREFIX_ID=B.PREFIX_ID
START WITH DATA_ID=1 CONNECT BY PRIOR DATA_ID=DATA_PARENT_ID
0
 
Shaju KumbalathCommented:


SELECT DATA_ID,PREFIX ||' '||substr(SYS_CONNECT_BY_PATH(DATA, ','),2)
FROM DATA A,PREFIX B WHERE A.PREFIX_ID=B.PREFIX_ID
START WITH DATA_ID=1 CONNECT BY PRIOR DATA_ID=DATA_PARENT_ID
0
 
Shaju KumbalathCommented:
please ignore earlier post try this
 

SELECT DATA_ID,PREFIX ||' '||substr(SYS_CONNECT_BY_PATH(DECODE(LEVEL,1,'','(')||DATA||DECODE(LEVEL,1,'',')'), ' '),2)
FROM DATA A,PREFIX B WHERE A.PREFIX_ID=B.PREFIX_ID
START WITH DATA_ID=1 CONNECT BY PRIOR DATA_ID=DATA_PARENT_ID
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
shru_0409Commented:
SELECT DATA_ID,PREFIX,LPAD(' ', 2*level-1)||SYS_CONNECT_BY_PATH(data, '/') data_val
FROM DATA d,PREFIX p
WHERE d.PREFIX_ID=p.PREFIX_ID
START WITH DATA_PARENT_ID is null
CONNECT BY PRIOR DATA_ID=DATA_PARENT_ID

try this
0
 
Shaju KumbalathCommented:
oops i didn't see the 3 rd point
try this




SELECT DATA_ID,
substr(SYS_CONNECT_BY_PATH(PREFIX,',')||',',2, instr(SYS_CONNECT_BY_PATH(PREFIX,',')||',' ,',',2)-2)
||' '||substr(SYS_CONNECT_BY_PATH(DECODE(LEVEL,1,'','(')||DATA||DECODE(LEVEL,1,'',')'), ' '),2)
FROM DATA A,PREFIX B WHERE A.PREFIX_ID=B.PREFIX_ID
START WITH DATA_ID=1 CONNECT BY PRIOR DATA_ID=DATA_PARENT_ID

 
0
 
Shaju KumbalathCommented:
Scripts For tables
 

CREATE TABLE PREFIX
(
PREFIX_ID NUMBER(2),
PREFIX VARCHAR2(10 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
 
 
Insert into PREFIX
(PREFIX_ID, PREFIX)
Values
(1, 'Honarable');
Insert into PREFIX
(PREFIX_ID, PREFIX)
Values
(2, 'Justice');
Insert into PREFIX
(PREFIX_ID, PREFIX)
Values
(3, 'Doctor');
COMMIT;
 

CREATE TABLE DATA
(
DATA_ID NUMBER(2),
DATA_PARENT_ID NUMBER(2),
PREFIX_ID NUMBER(2),
DATA VARCHAR2(10 BYTE)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
 
 
Insert into DATA
(DATA_ID, DATA_PARENT_ID, PREFIX_ID, DATA)
Values
(1, NULL, 1, 'AAAA');
Insert into DATA
(DATA_ID, DATA_PARENT_ID, PREFIX_ID, DATA)
Values
(2, 1, 2, 'BBBB');
Insert into DATA
(DATA_ID, DATA_PARENT_ID, PREFIX_ID, DATA)
Values
(3, 2, 3, 'CCCC');
COMMIT;
0
 
shru_0409Commented:
u can try like this
1 create function
-----------------------
CREATE OR REPLACE FUNCTION parse_string (
pv_str_i IN VARCHAR2 DEFAULT NULL,
pv_first_occ IN NUMBER DEFAULT NULL,
pv_last_occ IN NUMBER DEFAULT NULL
)
RETURN VARCHAR2
IS
lv_retval VARCHAR2 (4000);
lv_str VARCHAR2 (4000);
lv_tester VARCHAR2 (4000);
lv_substr_pos1 NUMBER;
lv_substr_pos2 NUMBER;
BEGIN
SELECT INSTR (pv_str_i, '/', 1, pv_first_occ)
INTO lv_substr_pos1
FROM DUAL;

SELECT INSTR (pv_str_i, '/', 1, pv_last_occ)
INTO lv_substr_pos2
FROM DUAL;

lv_tester :=SUBSTR (pv_str_i, lv_substr_pos1 + 1,(lv_substr_pos2 - lv_substr_pos1));

lv_str := REPLACE (lv_tester, '/', NULL);
IF pv_first_occ <> 1 THEN
   lv_str := ' ('||lv_str||')';
   lv_str := REPLACE (lv_str, '()', NULL);
END IF;  


RETURN lv_str;
END parse_string;
-----------------------------

try this query

SELECT DATA_ID,PREFIX ,NVL (parse_string (data_val, 1, 2), ' ') || NVL (parse_string (data_val, 2, 3), ' ')||NVL (parse_string (data_val, 3, 4), ' ') level1
FROM (
SELECT DATA_ID,PREFIX ,SYS_CONNECT_BY_PATH (data, '/') || '/' data_val
FROM DATA d,PREFIX p
WHERE d.PREFIX_ID=p.PREFIX_ID
START WITH DATA_PARENT_ID is null
CONNECT BY PRIOR DATA_ID=DATA_PARENT_ID)

------------------------

if you want to concat that try this

SELECT DATA_ID,PREFIX ||' ' || NVL (parse_string (data_val, 1, 2), ' ') || NVL (parse_string (data_val, 2, 3), ' ')||NVL (parse_string (data_val, 3, 4), ' ') level1
FROM (
SELECT DATA_ID,PREFIX ,SYS_CONNECT_BY_PATH (data, '/') || '/' data_val
FROM DATA d,PREFIX p
WHERE d.PREFIX_ID=p.PREFIX_ID
START WITH DATA_PARENT_ID is null
CONNECT BY PRIOR DATA_ID=DATA_PARENT_ID)






0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now