SQL & PL/SQL :: Distinct Values In A Hierarchical Result?
Nov 7, 2011
I have a table with 4 columns. The data is stored in an hierarchical format where L1 being the parent and L4 being the lowest child.
L1 L2 L3 L4
1 11 111 1111
2 21 211 2111
2 22 222 2222
[code]...
So each Level(L1 ..L4) has zero or many child levels which further has more levels.With out using PL/SQL how can we write a Select query to give me a distinct of all children, all the way to the lowest level (L4).Example: give me all the children where L1 = 3.Result: 31, 32, 33, 311, 322, 333, 3111, 3222, 3333Is it possible to write such a query or am I asking too much logic out of a select and should go with PL/SQL.
View 12 Replies
ADVERTISEMENT
May 27, 2013
i have a table with a clob column and i have 150 records i want retrieve distinct values from the clob using distinct operator on clob will not work
View 1 Replies
View Related
Jul 18, 2013
I have a table with two columns, like:
123 xxx
456 xxx
789 yyy
987 yyy
And in the output I would like to have:
123 xxx
789 yyy
I tried with distinct and unique on the second column, but it doesn't work.
View 12 Replies
View Related
Apr 19, 2010
My Form consists two Hierarchical trees. When I select a node from first tree and I press Move Right (>>) Button the selected node should move to the second tree. Similarly when I select a node from second tree and press move Left button(<<) it should move to the first tree.
I also want to know want to insert values Into New Table using node values from The Displayed Hierarchical Tree. How to retrieve values using populate_Group_from_tree and insert into table.
View 2 Replies
View Related
Jun 3, 2013
CREATE TABLE TEST_TAB
(
A NUMBER(5),
B VARCHAR2(20)
) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Manoj' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Arun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Varun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Suresh' ) ;
[code].....
Query Output :
1Arun,Arun,Manoj,Manoj,Manoj,Suresh,Varun
2Kamlesh,Manoj,Manoj,Manoj,Suresh,Suresh
Expected Output :
1Arun,Manoj,Suresh,Varun
2Kamlesh,Manoj,Suresh
Expectation here is duplicate values should not be repeated.
View 1 Replies
View Related
Aug 11, 2010
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have a cursor, whose sql is returning seven rows with these values:
9
4
4
9
7
9
4
i open cursor and fetch these values into variable as shown
OPEN id_search FOR l_sql_stmt;
LOOP
FETCH id_search INTO l_eve_id;
if l_eve_id != l_eve_id_prev then
l_eve_id_str := l_eve_id_str || ' , ' ||l_eve_id ;
[code].......
but i want only the distinct values in l_eve_id_str , i.e.,
l_eve_id_str := 9,4,7
What this code is doing is creating a string with all the values
l_eve_id_str := 9,4,4,9,7,9,4
How to remove duplicates from this string?
View 8 Replies
View Related
Jul 13, 2010
I have table as below :-
Table ABC(
ID Number,
startDate date,
City varchar2(10)
)
I need to write query which will get me all the CityNames for which there are no Startdate differs,
i.e. To get all the city name records for which all of the Startdate are same across all the records.
I dont want to go after Self Join due to performance issues, do we have any better way?
View 2 Replies
View Related
May 12, 2013
What is the fastest way to fetch DISTINCT values from partitioned table?
1) DISTINCT/UNIQUE
2) GROUP BY
3) PARTITION BY OVER()
4) MAX(ROWID)
Table Definition
CREATE TABLE STG_SOS_SALES_FACT_STUDY
(
CNTRY_KEY NUMBER,
STUDY_ID NUMBER,
PRD_KEY_YEAR NUMBER,
PRD_KEY_WEEK NUMBER,
DATE_FROM DATE,
[Code]...
-> PARTITION BY RANGE (PRD_KEY_YEAR, PRD_KEY_WEEK)
-> SUBPARTITION BY LIST (CNTRY_KEY)
** Local Partition Indexes
1) CN_SD_CTG_PRD_PRDC_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, CTG_ID, PRDC_KEY)
2) CN_SD_PRD_STR_CTG_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, STR_KEY)#Query:
SELECT DISTINCT PRD_KEY_WEEK, PRD_KEY_YEAR
[Code]...
** Explain Plan:
Plan
SELECT STATEMENT ALL_ROWSCost: 6,235 Bytes: 629 Cardinality: 37
8 HASH UNIQUE Cost: 6,235 Bytes: 629 Cardinality: 37
7 CONCATENATION
3 PARTITION RANGE ITERATOR Cost: 1,985 Bytes: 1,031,900 Cardinality: 60,700 Partition #: 3 Partitions accessed #194 - #207
[Code]...
Partition #: 7 Partitions determined by Key ValuesThe above query is taking around 6-7 minutes to fetch the data.
View 12 Replies
View Related
May 3, 2012
As per NULL values concept
One NULL value is not equal to other NULL value.
But how DISTINCT and UNION eliminating NULL values.
Then how UNIQUE key constraint accepting more than one NULL value..
View 1 Replies
View Related
Sep 25, 2012
I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.
eg:
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
7 lmn 10
.
.
.
so on... (till some count)
Result should be
Query 1 should result --->
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
query 2 should result -->
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
query 3 should result -->
7 lmn 10
.
.
9 .. ..
so on..
How to write a query for this inside a loop.
View 5 Replies
View Related
Aug 24, 2010
with t as
( select 1 id, 101 book_id, 'MICROBIOLOGY' book_type, 1 category, 'sCIENCE AND TECH' category_name
from dual
union all
select 1 , 101 , 'MICROBIOLOGY', 2 , 'HEALTHCARE' from dual
union all
[Code]....
id book_id BOOK_TYPE category category_name
1 101 MICROBIOLOGY 1 SCIENCE AND TECH
1 101 MICROBIOLOGY 2 HEALTHCARE
1 102 CHEMISTRY 5 CHEMICAL ENGINEERING
2 105 COMP SC 1 SCIENCE AND TECH
The above is the output for a query after joining multiple tables. I have just put here the output I am getting after joining the tables.Now I want to achieve the below result.
Expected output: it should be | delimited
1|101|MICROBIOLOGY|102|CHEMISTRY|1|sCIENCE AND TECH|2|HEALTHCARE
2|105|COMP SC|1|SCIENCE AND TECH
Is there any alternative way other than SYS_CONNECT_BY_PATH? I also tried to use CONCAT_ALL but its not working.
View 13 Replies
View Related
Nov 8, 2012
I have two tables A and B.
A
--
variable value
-------- ------
a 10
b 20
B
--
Exp
---
b-a
b*a
b/a
How can the variables be replaced with values(10,20) using a single query...?
View 2 Replies
View Related
May 6, 2010
i am doing a data transformation using the following function
FLOOR(ONSET_TPQ-VAX_DATE)
Where ONSET_TPQ and VAX_DATE are both dates. Now in a situation where the ONSET_TPQ precedes VAX_DATE I want the result to be null for those records. As an example if ONSET_TPQ= 2000 and VAX_DATE=2010
I want the result to be NULL as I dont want negative values. Any exact SQl syntax on how to do that. DO i write a case statement?
View 6 Replies
View Related
Jul 31, 2013
>select level ,empno,ename,mgr
from emp
connect by prior empno=mgr
start with mgr is null;
Output:-
>
LEVEL EMPNO ENAME MGR
----- ---------- ---------- ----------
1 7839 KING
2 7566 JONES 7839
3 7788 SCOTT 7566
4 7876 ADAMS 7788
3 7902 FORD 7566
2 7698 BLAKE 7839
3 7499 ALLEN 7698
3 7521 WARD 7698
3 7654 MARTIN 7698
3 7844 TURNER 7698
3 7900 JAMES 7698
LEVEL EMPNO ENAME MGR
----- ---------- ---------- ----------
2 7782 CLARK 7839
3 7934 MILLER 7782
Note:- I got only this that this query is alternative of self join and it is giving manager name along with mgr id for each employee but when it gives output i couldn't able to understand how to identify who is manager of whom. Tell and explain with clause in oracle , i only know it is alternative of inline view but i want to know how does it work and how to use 'WITH' in oracle query if possible.
View 4 Replies
View Related
Jan 27, 2009
I'm trying to get different level using hierarchical query in sql.my table is
item_id child_item_id
------------------------------
p21 p25
p21 p22
p22 p23
p22 p24
p25 p27
p25 p26
p27 p28
p27 p29
p30 p31
p30 p32
I want to display result with respective levels.
for example p21 ,p30 are coming under first level .
p22,p25 ,P31,P32are 2nd level.
p23,p24,p26,p27 are 3rd level
p28,p29 are FOURTH level item_id's.
Already I 'VE tried using CONNECT BY PRIOR clause.BUT STILL I COULDN'T GET THE RESULT.
View 2 Replies
View Related
Sep 29, 2011
first of all sample data;
create table test_circular_data(c1 varchar2(10),c2 varchar2(10));
insert into test_circular_data values ('c1','l2');
insert into test_circular_data values ('c1','l3');
insert into test_circular_data values ('c3','l3');
insert into test_circular_data values ('c4','l3');
commit;
There is a circular relation between columns c1 and c2, so what I'm trying to retrieve is something like that :
c1--> l2 --> l3 --> c3 --> c4
The steps to get that result is :
1.- c1 related to l2 : c1-->l2
2.- c1 related to l3 : c1-->l2-->l3
3.- l3 in the list and related to c3 : c1-->l2-->l3-->c3
4.- l3 in the list and related to c4 : c1-->l2-->l3-->c3-->c4
View -1 Replies
View Related
Apr 22, 2010
I have a hierarchical data structure where a child can have many parents, and a parent can have many childs.See the attached file hierarchy_iliustration.jpg. This example has 4 hierarchy levels, in real problem there can be unlimited number of levels.I want to write a SQL query that lists all indirectly dependent child nodes for a given parent node.
Test structure for example attached:
CREATE TABLE TEST.NODE_T
(
ID NUMBER PRIMARY KEY,
TEXTAS VARCHAR2(254)
);
[code]....
View 5 Replies
View Related
Dec 24, 2012
Hierarchical Tree in oracle form 6i .i am trying to build tree in form 6i but it give error
SELECT 1,
level,
job
,
'',
ename
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
order by level
i want to show job wise employees ...
View 4 Replies
View Related
Nov 13, 2011
I have a table which contains the connectivity details. It is telecom data. The table contains the relation between the point features(Ex: Terminal) and linear features(Ex: Cable)
Below is some sample data
drop table connectivity ;
create table connectivity
(
ftr_no number(5),
ftr_id number(5),
[Code]...
-- 2nd linear ftr
insert into connectivity values (30,10359,33, 9655, 2,NULL,NULL);
insert into connectivity values (30,10359,33, 9656, 1,NULL,NULL);
insert into connectivity values (30,10359,30, 10359,3,NULL,NULL);
[Code]...
In the connectivity table PARENT_FTR_NO and PARENT_FTR_ID needs to be updated based on these conditions.
1. Correspponding to each feature there are 3 entries in this table.
2. Each feature(Point orLinear) has 2 sides INcoming and OUTgoing
3. The possible values for the RELATION column are 1, 2 and 3 where
-> 1 indicates feature conncted at the OUTgoing end
->2 indicates features connected at the INcoming end
->3 indicates self connected
4. Always a linear feature is the parent of a point feature which are connected to the OUTgoing end of the linear feature (i.e where RELATION = 1)
There is another table which contains FEATURE_NO and FEATURE_TYPE.
create table ftr_catgry
(
ftr_no number(5),
ftr_type char(1)
);
[Code]...
FTR_NO F
---------- -
33 P
31 L
30 L
Where
'L' represents linear feature
'P' represents point feature
The relation should be updated as
Step 1. Start with a linear feature, for example FTR_NO = 31
SQL> select * from connectivity where ftr_no = 31 and ftr_id = 10354 and relation = 1;
FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
31 10354 33 9651 1
The feature connected to the OUTend of the 10354 i 9651 with FTR_NO = 33 and it is a point feature so the parent for 9651 is 10354
Step 2.
SQL> select * from connectivity where ftr_no = 33 and ftr_id = 9651 and relation = 1;
FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
33 9651 33 9652 1
9652 is connected to 9651 at the OUTend, since 9651 is point feature so the parent of 9651 which is a linear feature is also the parent for 9652
Likewise the all relations have to be updated
Expected result after update
SQL> select * from connectivity;
FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
31 10354 33 9650 2
31 10354 33 9651 13110354
31 10354 33 10354 3
33 9651 31 10354 2
33 9651 33 9652 13110354
33 9651 33 9651 3
33 9652 33 9651 2
[Code]...
24 rows selected.
View 2 Replies
View Related
Oct 20, 2010
Any links on hierarchical retrieval concepts?
View 3 Replies
View Related
Aug 27, 2013
I have a table (Parent - Child).There is a requirement to maintain this table, thats the hierarchy of the oraganisation.So, every quater they will be updating the table. They will be importing the data through an excel and in that excel there are 3 action items,
=> Insert, Update and Delete (logical delete).
CREATE TABLE PARENT_CHILD_TBL ( "ID" VARCHAR2(6 BYTE) NOT NULL ENABLE, "ID_DESC" VARCHAR2(200 BYTE), "ID_LEVEL" VARCHAR2(200 BYTE), "PARENT_ID" VARCHAR2(200 BYTE) )
For Update:What all validation can come for an updation of an hierarchical data in general.Like = how to derive the level value at database side when the id is updated to some other level.= How to maintain the relation. A -> B -> D ( A is the grand parent here).A -> Ceg: if B is updated as parent node of A, then we should throw error (cyclic data). Any more validations for hierarchical data
View 6 Replies
View Related
Sep 24, 2012
How to count siblings in a hierarchical query? I'm trying to get a listing of employees
SELECT LEVEL, last_name||', '||first_name AS Manager, count(employee_id)
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
GROUP BY level
This returns 4 levels. I'm wanting to add up the number of siblings under the level 2 instead of listing them all.
View 1 Replies
View Related
Aug 19, 2013
I want Hierarchical Query..I have Table of chart of account
CREATE TABLE COA
(
ACCOUNT_CODE CHAR(19),
ACCOUNT_TITLE VARCHAR2(70),
ACC_TYPE Char(1),
PARENT_CODE CHAR(19)
)
[code]....
View 2 Replies
View Related
Nov 10, 2011
Look into the below table:
TABLE :- EMPLOYEE
________________________
| ID | SUPERVISOR |
|_______________________|
| A101 | B102 |
|________|______________|
| | |
| B102 | C104 |
|________|______________|
| | |
| C104 | D108 |
|________|______________|
| | |
| D108 | E104 |
|________|______________|
Here B102 is supervisor of A101 and C104 is supervisor of B102 and so on. I want to get this data into new table in below format
TABLE :- Hierarchy
________________________________________________________________
| ID |SUPERVISOR_1 |SUPERVISOR_2 |SUPERVISOR_3 |SUPERVISOR_4|
|______________________|_____________|_____________|____________|
| A101 | B102 | C104 | D108 | E104 |
|________|_____________|_____________|_____________|____________|
| | | | | |
| B102 | C104 | D108 | E104 | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| C104 | D108 | E104 | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| D108 | E104 | NULL | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | |
| E104 | NULL | NULL NULL NULL
|________|_____________|_____________|_____________|____________|
I want to insert 1st two rows into Hierarchy table, then I would like to update Supervisor_2 to Supervisor_4. Here I don't want to use 'CONNECT BY PRIOR', as it take more time to execute (there are millions of records).SQL code for same.
View 7 Replies
View Related
Mar 5, 2010
i'm trying to display the heirarichal relationship between the tables (parents-child-subchild).
[b]table structure[/b]
DEPT
|PK-DEPT_ID
|
EMP
|pk-EMP_ID
[code]....
Expected output
table_name path
DEPT DEPT
EMP DEPT/EMP
EMP_AUTHORIZATION DEPT/EMP/EMP_AUTHORIZATION
EMP_AUTHRIZATION_CARD DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD
EMP_AUTHRIZATION_DUP DEPT/EMP/EMP_AUTHORIZATION/EMP_AUTHRIZATION_CARD/EMP_AUTHRIZATION_DUP
but by using below query i am getting complete heirarichy.
SELECT LEVEL,
Table_Name,
Constraint_Name,
R_Constraint_Name ,
SYS_CONNECT_BY_PATH(Table_Name, '/') Path
[code]....
View 6 Replies
View Related
Mar 5, 2010
i'm trying to display the hierarchical relationship between the tables (parents-child-subchild).
[b]table structure[/b]
DEPT
|PK-DEPT_ID
|
EMP
|pk-EMP_ID
|fK emp_DEPT_fK((FK column is dept_id references dept table dept_id column))
[code]....
but by using below query i am not getting complete heirarichy.
SELECT LEVEL,
Table_Name,
Constraint_Name,
R_Constraint_Name ,
SYS_CONNECT_BY_PATH(Table_Name, '/') Path
[code]....
View 2 Replies
View Related
Apr 24, 2010
Below query. Below is the DDL , DML and expected output.
create table tab_1 (col1 varchar2(10), col2 varchar2(10));
select * from tab_1
Output of query
col1 col2
12
23
34
[code]...
The above query is not giving below expected output.
Output:
4 4/3/2/1
3 3/2/1
7 7/6/5
8 8/7/6/5
11 11/10/9
View 6 Replies
View Related
Nov 30, 2010
I need complete source from hartical tree menu using by calling reports and calling forms.
i need related Table ,triggers and Examples.
View 4 Replies
View Related
Apr 19, 2013
I have a table that has hierarchical data within it. I need to select this data in a specific way, showing the hierarchy.
E.g. Data in table (Key is unique)
Lvl KeyParKey HasChild
1k101
1k200
1k301
1k401
2k34k10
2k22k11
2k24k10
2k13k30
2k52k30
2k35k30
2k13k30
2k11k40
3k56k220
3k109k221
3k67k220
4k61 k1090
Etc etc....
That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.
The format I need to get out is as follows:
Lvl KeyParKey hasCh
1k101
2k34k10
2k22k11
3k56k220
3k109k221
4k61 k1090
3k67k220
2k24k10
1k200
1k301
2k13k30
2k52k30
2k35k30
2k13k30
1k401
2k11k40
View 1 Replies
View Related
Jun 10, 2011
I am using oracle forms 9i. I have to create a Hierarchical Tree and attach checkbox at its node,so that user can select which tree he want in its further processing.
View 4 Replies
View Related
