Solved

Connect By statement returning duplicate rows

Posted on 2013-05-13
9
1,633 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 34

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

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.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

746 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

8 Experts available now in Live!

Get 1:1 Help Now