Solved

Connect By statement returning duplicate rows

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

 
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
 

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 74

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MULTIPLE DATE QUERY 15 91
Oracle DB monitor SW 21 60
Database Design Dilemma 6 58
oracle date format checking 7 26
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

856 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