[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Connect By statement returning duplicate rows

Posted on 2013-05-13
9
Medium Priority
?
2,233 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

656 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