SQL & PL/SQL :: Removing Duplicate In Hierarchy Level

Jul 18, 2013

I have requirement to suppress the duplicate nodes on same level in hierarchy query.
Below given is the script for it.

CREATE TABLE NODE_LVL (PARENT_NODE VARCHAR2(100), CHILD_NODE VARCHAR2(100));

INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AFL');
INSERT INTO NODE_LVL VALUES('TBL_APP','TBL_ACS');
INSERT INTO NODE_LVL VALUES('TBL_ADD','TBL_ADW');
INSERT INTO NODE_LVL VALUES('TBL_ADP','TBL_ADV');
INSERT INTO NODE_LVL VALUES('TBL_AOP','TBL_AOV');
[code]......

Table 'TBL_APP' is having 2 parent nodes i.e 'TBL_AOV' and 'TBL_ADV'
SELECT * FROM node_lvl WHERE child_node = 'TBL_APP';

At level 5 there is duplicate nodes i.e 'TBL_APP' and 'TBL_ACS' as parent_node and child_node respectively.

SELECT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;

I want to suppress such duplicates. So I added DISTINCT

SELECT DISTINCT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;

BUT requirement is to maintain the same order (of hierarchy) as it was before adding DISTINCT.

View 11 Replies


ADVERTISEMENT

PL/SQL :: Get Only Last Level In SQL Hierarchy Query

Jun 4, 2013

How to get only the last level in Oracle SQL Hierarchy Query?

View 2 Replies View Related

Removing Duplicate Keys

Jul 3, 2008

Trying to delete duplicate rows from a table. The problem is, they aren't exactly duplicate rows. Let me explain.

I am migrating data from a Oracle 8.1.7 db to a 10.2.1 db. In the older db, this certain table does not have a PK/Unique Index, but in the new db there is a unique index. The fields that the index is unique on are:

SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE.

In the old db, when I run this query I get 1229 rows. With a count of 2 each.

select SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE, count(*)
from customer_id_equip_map
group by SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE
having count(*)>1;

They are duplicates on those fields, but they are not totally duplicate rows because there is a field called is_current that has 0 in one row and has 1 in the other. What I need to do, is delete the 1229 rows with is_current=0.

View 4 Replies View Related

SQL & PL/SQL :: Removing Duplicate Rows When Condition Is Matched

Mar 17, 2010

My requirement if id, join_date, join_time, result of table1 is matched with table2 at least one time then if repeating rows associated with the id should not come.Here is the test case.

create table table1
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
create table table2
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));

insert into table1 values (01, '20010101', '0500', 'PASS');
insert into table1 values (01, '20010102', '0501', 'FAIL');
insert into table1 values (02, '20010103', '0502', 'PASS');
insert into table1 values (03, '20010104', '0503', 'FAIL');
insert into table1 values (04, '20010105', '0504', 'PASS');
insert into table1 values (05, '20010106', '0505', 'FAIL');
[code]...

I have tried the below mentioned query, whether any better query is there than this because in real-time data have 2 millions of record in table 1 and 60 thousand in table2.

select distinct a.id, a.join_date, a.join_time, a.status
from table1 a, table2 b
where a.id = b.id
and (a.id, a.join_date, a.join_time, a.status) not in (select b.id, b.join_date, b.join_time, b.status
from table2 b)
and a.id = (
select distinct a.id
[code]....

View 20 Replies View Related

Forms :: Avoid Duplicate At Entry Level

May 17, 2011

How can i avoid duplicate entry at entry level in form rather than when i pressed save button

View 2 Replies View Related

SQL & PL/SQL :: Row Level To Column Level Data Transposition?

May 14, 2010

I have a data like,

1) manual_temp_master

auto_idbatch_id sec_idsec_id_type crrncy_cdcreate_Dt price_dt
------------------------------------------------------------------
11234ABC1CUSIPUSD14/05/201014/05/2010
23456XYZ1SEDOLGBP13/05/201013/05/2010

2)manual_temp_detl

auto_idbatch_id Price_bkt_cdscreate_Dtprice_date
---------------------------------------------------------
11234PS114/05/201014/05/2010
11234PS214/05/201014/05/2010
11234PS314/05/201014/05/2010
11234PS414/05/201014/05/2010

[code]....

I want to write a sql query which will fetch the data from manual_temp_master and manual_temp_detl.But from manual_temp_detl table, Price_bkt_cds columns should be displayed as columns. Like the should look like as below:

sec_idsec_id_type crrncy_cd COL_PS1 COL_PS2 COL_PS3 COL_PS4 COL_PS5COL_PS6price_date
--------------------------------------------------------------------------------------
ABC1CUSIPUSDPS1PS2PS3PS4PS5PS614/05/2010
XYZ1SEDOLGBPPS1PS2PS3PS4PS5PS613/05/2010

View 8 Replies View Related

SQL & PL/SQL :: DBMS_STATS For Table Level Vs Partition Level

Nov 17, 2010

what is the difference on DBMS_STATS for table level and partition level , which will provide the best optimizer . If the table xxxx is partitioned from 1 to 10 ,then running gather stats on table xxxx as whole table level or partition level which will provide best result on the performance.

View 1 Replies View Related

Backup & Recovery :: Incremental Backup Level 1 Or Level 0 Without Archivelogs?

Oct 22, 2012

Can you take an incremental backup level 1 or level 0 without archivelogs?

syntax would bebackup as compressed backupset cummulative level 1 database.

The reason I ask is because when I run backup as compressed backupset cummulative level 1 database plus archivelogs # it runs fine, but when I run backup as compressed backupset cummulative level 1 database it just hangs.

View 20 Replies View Related

How To Display Hierarchy List

Oct 1, 2009

query in order to display a hierarchy list. Example:

Database has three tables

Heading --> id, status, order, title
Sub-heading --> id, status, order, title, heading_id
Item --> id, status, order, title, heading_id, sub-heading_id, actual_content

order = the sequence in which they are to be displayed a sub-heading's heading_id refers to the parent heading, likewise an item's sub-heading_id refers to the parent sub-heading.

I assumed there was no need to track child content.

Now, the output format should be something like this, where the sub-headings are not repeated, nor are the items. Sort of like chapters in a book, things in a catalogue, or even topics in a forum.

Heading 1
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Heading 2
Sub-heading 1
Item 1
Item 2
Item 3
Heading 3
Sub-heading 1
Item 1
Item 2
Item 3
Sub-heading 2
Item 1
Item 2
Item 3
Sub-heading 3
Item 1
Item 2
Item 3

Where I'm getting hung up is how to query the data so it's quick, efficient, clean and easy to read and maintain. I've only gotten to listing the sub-headings and already the query looks convoluted. So, how should I approach this? Should it be done all in one query, and always checking against the results to know when to print a new heading or sub heading. Or should I run multiple queries and save the results to a variable, and then run sub-queries off that? Is there a better way to do this, am I going about it the hard way or am I in the right direction and just haven't gone far enough with it yet?

View 11 Replies View Related

SQL & PL/SQL :: Sorting Hierarchy Query

Jun 10, 2013

Is it possible to sort records based on hierarchy in such a way that records sorts in sequence and records of same level comes in the last (in sorting order)

Eg: Default hierarchy level order: 0,1,2,2,3,3,3,4,5

Above given is the default sorting order of an query for an parent 'NODE_A1' having multiple same level i.e 2 and 3

Expected Hierarchy level order: 0,1,2,3,4,5,2,3,3

Please check attached file for an example.

View 8 Replies View Related

SQL & PL/SQL :: Parent-Child Hierarchy?

Dec 20, 2012

way to achieve the below logic.

SQL to pick up parent child relationship within same table with a certain logic.

Example:
mod_product_numberProduct_Hierarchy
H555888 PH05678
H888987 H555888
H8889 H555888

[code]...

Example: I expect the rows with H8889,H9955 & P6666 & P5555 to be sub-category values value for product hierarchy H555888.

If there are rows with H8888987 as Product_hierarchy, we will pull up those rows too for product hierarchy H555888. The extra condition is we drill down only on 7 character mod_prod_number not on 5 character mod_prod_number. We pull out all sub category mod_prod_number for all distinct Product hierarchy.

View 11 Replies View Related

SQL & PL/SQL :: Hierarchy Record Manipulation?

Jun 13, 2011

Ihave the following result set based on a hiearchy query

SELECT LEVEL ,
BM.ITEM item
BM.Part part
connect_by_isleaf leaf
FROM item_part_tab bm
START WITH bm.item_id='002PH'
CONNECT BY BM.ITEM_ID = prior BM.PART_id

LEVEL ITEM PART LEAF
-----------------------------------------------------------------
1 002IT XPART1 0
2 XPART1 SUBPART1 1

In the above resultset PART='SUBPART1' does'nt have any child as evident from the connect_by_isleaf (LEAF=1) .Is there any way to create a pseudo record if such values are returned by the query, i.e., if a part has no tree under it a dummy record should be created i.e. in this case an additional record should be returned by this query. i.e., as below. the LEVEL=3 record should be created.

LEVEL ITEM PART LEAF
---------------------------------------------------------
1 002IT XPART1 0
2 XPART1SUBPART1 1
3 SUBPART1 1

View 16 Replies View Related

SQL & PL/SQL :: Row Level And Statement Level

Apr 11, 2013

between statement level or row level trigger, which trigger will execute first.We have BEFORE_UPDATE_ROWLEVEL_TRIGGER and BEFORE_ UPDATE_ STATEMENT LEVEL_TRIGGER triggers on table product.

which will execute first on update DML event ?

View 2 Replies View Related

SQL & PL/SQL :: Find Locked Tables In Hierarchy

Feb 1, 2012

Is it possible to find the locked objects in hierarchical order. Consider the below example

--Connect to scott schema
create table block_session(a NUMBER, b VARCHAR2(100))
/
insert into block_session select rownum,rownum*10 from dual connect by level<=10
/
COMMIT
/
GRANT ALL ON block_session to HR
/
update scott.block_session
SET a=10
where b=10
[code]......

Is it possible to get all locked table details in hierarchical order?

View 4 Replies View Related

SQL & PL/SQL :: Creating Materialized View For Getting Hierarchy?

Oct 3, 2013

I want to create a materialized view for the with same logic as below view

CREATE OR REPLACE FORCE VIEW GFL_MCP_CORE.VW_CLIENT_HIERARCHY
(KEY_INDENTED, CLIENT_LEVEL, ID, NAME, CLIENT_TYPE_CODE,
L2_CLIENT_ID, L2_CLIENT_NAME, TOP_CLIENT_ID, TOP_CLIENT_NAME, CLIENT_MCP_FLAG,
CLIENT_GROUP_NAME, ORG_REGION_NAME, COUNTRY_NAME, PROCESSOR_CLIENT_CODE)

[code]....

Here is the sample data for the client table and other tables are generic.

Row#IDNAMECOUNTRY_CODECLIENT_TYPE_CODECLIENT_GROUP_IDPARENT_CLIENT_IDSTATUS_FLAG
134801ZhuanshenguangchangCNBR10048232909A
212515229ConsolidatedFCUUSCL54812515A
314821286 Golden Mile BranchCABR53112712A
42050014513Bay CityUSBR56612009A
53864712-44-125 LeicesterGBBR56038630A
63869114736 Freestar BrickyardUSBR10020238686A
731141Walsall StoreGBL254631103A

View 1 Replies View Related

SQL & PL/SQL :: Hierarchy Query And Tree Structure

Jun 26, 2012

How to create one complex query. here what I have in my table.

DELIVERABLESPRECURSORS
------------ -----------
4 15
15 332
15 26
26 332
29 4
29 15
224 26
224 274
224 259
259 29
274 15
288 259
288 26
288 274
916 27
917 27
918 27

I need a query which run against this table and bring me the following result.

4 > 15 > 332
15 > 332
15 > 26 > 332
26 > 332
29 > 4 > 15 > 332
..... and so on

In theory, I need query which will track DELIVERABLES'S PRECURSOR and PRECURSOR's of there PRECURSOR'S.

View 16 Replies View Related

SQL & PL/SQL :: To Build A Hierarchy And Output It Into A Sub-report

Feb 26, 2010

I want to build a hierarchy and output it into a sub-report. Since the data in few hierarchy fields exceeds 4000 characters and therefore m getting the ORA-01489 error and the entire report was failing.

So i have used user aggregate function to print the out put. I have also used row_number() to print sequence in query output.

the query that i am using is

SELECT pr_id,
REPLACE (REPLACE (twadmin.STRAGG2(distinct RPAD (to_number(curr), 2, ' ') || ')' || NVL (DECODE (medical, NULL, clinical, medical), 'UNKNOWN') || '###' ||
RPAD (' Causality as per rport ', 50, ' ') || ': ' || reporter || '###' ||
RPAD (' Causality as per report2 ', 50, ' ') || ': ' || hospital || '###' ||
RPAD (' Dechallenge', 50, ' ') || ': '|| dechallenge || '###' ||
RPAD (' Rechallenge', 50, ' ') || ': '|| rechallenge), ',', CHR(10)), '###', CHR (10)) causality
[code]......

the output of the query is

ID Causality
-- ------
3755101 )Blisters (Blister)
Causality as per report1: Probable
Causality as per report2: Possible
Dechallenge : Not Applicable
Rechallenge : Not Applicable
2 )Mental status changes (Mental status changes)
[code].......

but the expected out put is

ID Causality
-- ------
3755101 )Blisters (Blister)
Causality as per report1: Probable
Causality as per report2: Possible
Dechallenge : Not Applicable
Rechallenge : Not Applicable
[code]........

but it should be as

1)
2)
3)
..
..
..
10)
11)
12) for the values in the causality field

View 25 Replies View Related

JDeveloper, Java & XML :: Recursive Hierarchy Query To XML?

Mar 5, 2013

To use dynatree (URL] I want the result to be in the xml form.but the result is not what I want.

SELECT
XMLELEMENT("div",xmlattributes('tree' AS "id"),
(SELECT DBMS_XMLGEN.getXMLType(
DBMS_XMLGEN.newContextFromHierarchy('
SELECT LEVEL,
case
when CONNECT_BY_ISLEAF = 0 then

[code]....

View 6 Replies View Related

Forms :: Tree Hierarchy - Not Working Properly?

Apr 8, 2009

i have created a tree hierarchy....when the user clicks on any node,that particular form will open....But there is some problem...i click on form A,it opens.,...then i exit that form via a exit button on the toolbar....and then I click on form B on the tree....but first the form A only opens...and then again i exit form A, then form B will open..

i have written the following code in when_tree_node_selected trigger :

if condition then
call_form(path,no_hide,do_replace);

View 3 Replies View Related

SQL & PL/SQL :: Oracle 10.2.0.4 On Solaris 10 - Recursive Query Using Hierarchy

Nov 9, 2011

We are on oracle 10.2.0.4 on solaris 10. My question is on a sql query. Is it possible to rewrite a query to avoid the connect by and prior constructs and use joins? For example i have the following query:

SELECT empno,
ename,
job,
mgr,
hiredate
FROM emp
START WITH empno in (7566,7698)
CONNECT BY PRIOR empno = mgr

How can it be rewritten using a two table join (self join)? I am not sure if it can be done and whether it is possible.

View 4 Replies View Related

SQL & PL/SQL :: Passing Hierarchy As Input To Function That Returns XML?

Aug 3, 2012

I have an requirement to create an function which takes table or hierarchy of tables as input and returns xml output in hierarchy. Below given is the Tables hierarchy.

AAAA
----AAA
----BBB
----CCC
-------CC1
-------CC2
-------CC3

[code]....

Requirement: Initially input was table name and using table as the root node output should generate xml of all the records of child tables.But now requirement is to give the flexibility to user to select what hierarchy he needs i.e he may select AAAA, CCC and in the nodes C1,C2,C3 and C4 if he doesn't want C3 then that node should not be shown in output.

I have created Hierarchy table having 3 columns SI.No, ParentNode and ChildNode and entered the above hierarchy relation.

1. What is the best to way (design)to pass input parameter for the function.

2. How to generate hierarchy in xml using DBMS_XMLGEN

View 12 Replies View Related

SQL & PL/SQL :: Find All Data Items In Hierarchy Recursion?

Jan 21, 2003

I need to find all "report to" supervisors in a hierarchical chain. A binary search won't work, as each level may contain > 2 supervisors. Example:

1

2 3 4

5 6 7 8 9

10

10 reports to 9
9 reports to 4
5 and 6 report to 2
7 and 8 report to 3
2, 3 and 4 report to 1

Result for all reporting to 1 would be a table containing 2, 3, 4, 5, 6, 7, 8, 9, 10 (order is unimportant!).

I think I need to use recursion. I can do this in C; need code for PL/SQL??? (I can't get the "tree" to display well!)

View 4 Replies View Related

PL/SQL :: How To Query A Hierarchy Structure (Manager-Employee)

Apr 29, 2013

I am using Oracle database 10g R2.

suppose I have this structure

EMP_ID               EMP_NAME          MANAGER_ID
1                    KING               
2                    STEVE               1
3                    FRANK               2

What I want is when I query for any EMP_ID, I want to get all his manager first, and the last record is the employee in leaf of the hierarchy in the query.

For example, when I query for EMP_ID=2 ,I want to get this result

EMP_ID               EMP_NAME          MANAGER_ID
1                    KING               
2                    STEVE               1

and when I query for EMP_ID=3 ,I want to get this result:

EMP_ID               EMP_NAME          MANAGER_ID
1                    KING               
2                    STEVE               1
3                    FRANK               2

How to write this query?

View 3 Replies View Related

SQL & PL/SQL :: Hierarchy Query - Calculate Total Miles

Jul 30, 2013

I've below tables.

create table tst_link (parent_pid number(5),
child_pid number(5)
);

insert into tst_link (1, 2);

[Code]....

For each parent pid i need to calculate total miles (including the child_pid also).

Required Output

PARENT_PID TOTAL_MILES
----------- ------------
1 48
4 100
8 125

View 4 Replies View Related

RAC & Failsafe :: Hierarchy To Stop And Start Oracle RAC Instance

Apr 20, 2013

What is the hierarchy to stop and start oracle RAC instance ?

View 10 Replies View Related

SQL & PL/SQL :: View Of Item Hierarchy By Eliminating NULL Levels

Aug 22, 2013

Here is the table structure I will be speaking of below:

SELECT lvl_1_id, lvl_1_dsc,
lvl_2_id, lvl_2_dsc,
lvl_3_id, lvl_3_dsc,
lvl_4_id, lvl_4_dsc,
lvl_5_id, lvl_5_dsc,
lvl_6_id, lvl_6_dsc,
lvl_7_id, lvl_7_dsc,
lvl_8_id, lvl_8_dsc
FROM item_hier_tbl;

I have a table that mimics an hierarchy of items (parent-child relationship). The top level (level 1) never has a NULL value, but it is possible for any of the lower values to have a NULL value. For example, levels 1, 2, 5, 7, & 8 may have values, but 3, 4, & 6 may all be NULL.

I need a View that can return the same data, but with out NULL values. The View would move the NOT NULL values up the levels, so there is consecutive data on each level starting at level. So using the example, the View would move value in 5 up to 3, 7 up to 4, and 8 up to 5. Then level 6 to 8 would have NULL.

I have done this already by creating a 2nd table and using a stored proc to copy the data over to the new table they I want it. But I was told a View or Materialized view "may" work, and perform quicker during the queries. I looked at the Oracle functions LAG and LEAD, but by definition they work by rows, I need to work by columns on the same row.

LAG (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

Is what I am asking possible in a View or Materialized View?

View 2 Replies View Related

Forms :: Hierarchy Tree Menu - Call Reports Through Node

Jun 12, 2012

I have a hierarchy tree menu look like this

+GL
Accounts
Inform
+Export
Export Entry Form
+Security
New User Entry

when i click Gl==>Accounts, accounts form opens and i want call my forms and reports through nodes.

View 1 Replies View Related

PL/SQL :: Removing Duplicates Except One

Sep 19, 2013

db and dev 10g rel2 ,suppose that i have a table with a lot of duplicate rows ,what i need is to delete the duplicates and retain one row of these duplicates . likecolumn -- with those values...how to delete two (hi's) and retain the third , ?it is all applied to all the duplicate values in the column.

View 5 Replies View Related

SQL & PL/SQL :: Removing Table Fragmentation?

Aug 5, 2012

To remove fragmentation which is the best method.

First one :
-----------------
1)Created a backup table from the Fragmented table (This table is a partitioned one).
2)Analyzed this table.
3)DROP the Fragmented table
4)Inserted the backuped up data from backup table to the Re-created table.
5)Analyze this table.

Second method
-------------------
1) Create a backup table newly, with PCTFREE =0
2) Inserted the data from Fragmented table ( This is a partitioned table) to backup table
3) Analyzed this table.
4) Truncate Fragmented table
4) Did Exchange partition of Fragmented table with Backup table.

The second method is not found to be removing the Fragmentation. Before the fragmentation was 28% after Second method the fragmentation is still the same. While the first method the fragmentation reduced to 16%.

Query used to find Fragmentation.
select table_name,
round((blocks*8),2) "table size kb",
round((num_rows*avg_row_len/1024),2) "actual data in table kb",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",

[code]...

View 9 Replies View Related

SQL & PL/SQL :: Removing Array Index

Sep 10, 2010

TYPE CashRecord IS RECORD(client_id VARCHAR2(100),
account_letter VARCHAR2(100),
cash_amount VARCHAR2(100),
cash_amount_ccy VARCHAR2(100) );

TYPE CashRecordTable IS TABLE OF CashRecord INDEX BY VARCHAR2(100); -- Indexed by client_id~account_letter~ccy

So if I did something like this;

l_cash_records CashRecordTable;

-- say for example that l_cash_rec/l_cash_rec2 has been defined..

l_cash_records('some index') := l_cash_rec;
l_cash_records('some index 2') := l_cash_rec2;
l_cash_records.COUNT would give me 2

How can I somehow remove 'some index 2' so that l_cash_records.COUNT is 1 ?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved