?
Solved

Connect By statement returning duplicate rows

Posted on 2013-05-13
9
Medium Priority
?
2,118 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 49

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 49

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 49

Accepted Solution

by:
PortletPaul earned 1600 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 400 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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…
Suggested Courses

752 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