Solved

Connect By statement returning duplicate rows

Posted on 2013-05-13
9
1,701 Views
Last Modified: 2013-05-20
I'm trying to use a connect by statement to order a table in a hierarchical format.
Each row has an EntityKey and a ParentEK.  Below is just example data to explain what I'm trying to do.

TABLE:
Objkey      ObjId      ParKey
1      Object1      0
2      Object2      1
3      Object3      1
4      Object4      1
5      Object5      2
6      Object6      2
7      Object7      2
8      Object8      3
9      Object9      3

E.g. query:

SELECT   *   FROM objTable
 START WITH ParKey = 0      
  CONNECT BY PRIOR ObjKey= ParKey  

RESULT:
Objkey      ObjId      ParKey
1      Object1      0
2      Object2      1
5      Object5      2
5      Object5      2
5      Object5      2
6      Object6      2
6      Object6      2
6      Object6      2
7      Object7      2
7      Object7      2
7      Object7      2
3      Object3      1
8      Object8      3
8      Object8      3
8      Object8      3
9      Object9      3
9      Object9      3
9      Object9      3
4      Object4      1

These extra rows are not in the database.  I've no idea why the query is repeating them.  I have used a distinct in the query to get the correct results:

SELECT  DISTINCT ObjKey, ObjId, Parkey FROM objTable
 START WITH ParKey = 0      
  CONNECT BY PRIOR ObjKey= ParKey  

Objkey      ObjId      ParKey
1      Object1      0
2      Object2      1
5      Object5      2
6      Object6      2
7      Object7      2
3      Object3      1
8      Object8      3
9      Object9      3
4      Object4      1


But this is not the way I want to approach it.  Mainly because I have a CLOB column that I need to return occasionally and DISTINCT doesnt work with CLOBS, but also because I would like to know what is the right way to do this.
Obviously my table and query are not as simple as above.  The actual query is:
         select * from (
         SELECT Fullwf, Bftype, fType, Bflevel,EntityKey,ParentEK,Customid,Description
                FROM mbrcalcbreakdown  
         START WITH ParentEK = 0  
         CONNECT BY PRIOR EntityKey = ParentEK         
         ORDER BY fullWF
        ) where bftype IN ('Calculation','Reference')

Open in new window


Really appreciate help on this, has been bugging me for ages.
0
Comment
Question by:obrienjimmy
9 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39161039
I don't have anything to add really except to say I cannot reproduce the symptoms.

e.g. see you sample in Ora 11g at: http://sqlfiddle.com/#!4/02ae1/3

what is your Oracle version?
0
 

Author Comment

by:obrienjimmy
ID: 39161157
Wow, that's a really handy tool.  Must remember to use that.

Sorry, I should have clarified..... I didnt think there would be any problem with the example I gave. That was just to show what I was trying to do and the type or result I was getting.

The table in question is a global temporary table that has about 15 columns... I didnt want to post up all that info.  
I was hoping there may be some glaringly obvious issue with the way I was querying that could be fixed.  


I'll see if I can get a working model into the sqlfiddle site with the problem occuring
0
 

Author Comment

by:obrienjimmy
ID: 39161226
Okay, this is very difficult. That site only allows very small samples unfortunately.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39161390
yes, 8000 chars only for the 'schema' I'm afraid, but shouldn't need all 15 columns perhaps.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:obrienjimmy
ID: 39161432
And I can't repeat the problem with small sample size.  I removed a load of columns and only brought in a small subset of the data.  But when I do this, the query works fine.  

These duplicate rows have to be coming from somewhere though.  I know 100% that they are not in the database as duplicate rows

Is there any other way to ensure the rows are distinct, but will allow me to work with CLOB fields too?
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 400 total points
ID: 39161504
>>And I can't repeat the problem with small sample size.  
frustrating, but it's a clue perhaps.
can you reduce the columns to the bare minimum needed to traverse the hierarchy? is the problem still there?

>>Is there any other way to ensure the rows are distinct, but will allow me to work with CLOB fields too?

do 'the distinct' as a subquery within the from clause then join back to the source?

folks here are happy to look at your full query if you post it
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39161571
It sounds to me like one way or another, your global temporary table has some "duplicate" rows that are not expected.  Because this is a global temporary table, it is a bit harder to verify than with a permanent table, since the contents of it change every time it is loaded.  I suspect if you carefully review the rows in the global temporary table, you will find the duplicates there.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 100 total points
ID: 39161622
you can perform a "distinct" by using the non-clob columns in an analytic

I took a guess a the significant columns here, you may need to tweak the partition and ordering clauses to include different or additional columns

SELECT   *
    FROM (SELECT     fullwf,
                     bftype,
                     ftype,
                     bflevel,
                     entitykey,
                     parentek,
                     customid,
                     description
                FROM (SELECT *
                        FROM (SELECT fullwf,
                                     bftype,
                                     ftype,
                                     bflevel,
                                     entitykey,
                                     parentek,
                                     customid,
                                     description,
                                     ROW_NUMBER() OVER (PARTITION BY customid ORDER BY entitykey) rn
                                FROM mbrcalcbreakdown)
                       WHERE rn = 1)
          START WITH parentek = 0
          CONNECT BY PRIOR entitykey = parentek)
   WHERE bftype IN ('Calculation', 'Reference')
ORDER BY fullwf
0
 

Author Comment

by:obrienjimmy
ID: 39162001
Thanks for all the replies.  I wont get back to this issue till the morning, but I'll look into doing the distinct as a sub query, then joining.   It will solve the problem for now.  When I've more time I'll take a closer look to see if there are issues in the table itself somewhere.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now