Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    3

    Unanswered: Start With Connect By Prior (ora-01437)

    Hi there,

    I just recently discovered this great bit of code but have now come across a problem. I'm working with Maximo 4.1.1 with a Oracle 8.1.7 database version and I'm using TOAD for my database queries. Here's my problem.

    just for a simple example, I have the following script.

    select parent, pmnum, description, null jpnum
    from pm
    where frequnit<>'DAYS'
    start with haschildren='Y'
    connect by parent = prior pmnum

    It produces (nulls were added just to preserve correct column placements)

    PARENT PMNUM DESCRIPTION
    null 1079 Grey Ship Unloading Inspection
    1079 1081 NORTH & SOUTH UNIT INSPECTION
    1079 1127 #7 Conveyor Belt Inspection
    1079 1129 1000 ton Elevator PM
    1079 1082 DOCK TEST RUN
    null 1136 Slag Ship Inspection
    1136 1137 #4 Belt And Hopper Inspection
    1136 1138 #5 Belt and Hopper Inspection
    1136 1139 # 6 Belt and Hopper Inspection
    1136 1140 #7 Conveyor Belt Inspection
    1136 1141 # 8 Conveyor Belt Inspection
    1136 1142 #1 Stick Conveyor Inspection
    1136 1143 #2 Stick Conveyor
    1136 1144 Thor Stacker Inspection
    Null 1156 SLAG DRIER P.M.INSPECTION
    1156 1157 SLAG HOPPER & BELT INSPECTION
    1156 1158 SLAG DRUM INSPECTION
    1156 1159 SLAG DRAG

    Which is so fantastic beyond my wildest dreams. It's exactly how I want this set of data to look. Now, however, I need to add information from another table (that has a PMNUM column that can be used for reference), and it has a JPNUM column that I want included into my select.

    This table also can have duplicate PMNUM with different JPNUM and I want that to be shown in my select as well. I tried simply adding another table reference to my script only to get an ORA-01437: cannot have join with CONNECT BY error. I tried unions and sub selects but only to have the order as above ruined.

    Is there a solution?

    Thanks for your time!

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Can you add a rownum to your subselect (ie rownum as ordercol) and use that in your outer select order clause to maintain your ordering?

    select ....
    from (
    select rownum as ordercol,
    othercols....
    from
    connect by.....
    ) sub,
    othertable
    where othertable.col = sub.col
    order by sub.ordercol, sub......

    Hth
    Bill

  3. #3
    Join Date
    Jul 2003
    Posts
    3
    Hey! that's an idea!...

    I think definitely need a way to be able to temporarily make some sort of index that can be queried/sorted by....

    thanks for your response, any more ideas would be greatly appreciated.

  4. #4
    Join Date
    Jul 2003
    Posts
    3
    Billm, thanks for clearing up my brain fart for me...

    everything is perfect now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •