Unanswered: Start With Connect By Prior (ora-01437)
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
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.