Ok, so I've been working on this query for a week or two now. I've been posting in the Oracle forums to get help with the SQL, and now, I should have SQL that works, but when I try to run it in my code, I get the 'FROM keyword not found where expected' error, and for the life of me, I can't figure out what's wrong. I suspect it's a problem from converting my sql to vba (i.e. sticking my SQL in a string variable).

Here's my SQL statement:
Code:
SELECT
   MAX(b2.top_assy_1) OVER (PARTITION BY grp_num) AS top_assy,
   b2.bill_lvl,
   b2.comp_part_nbr,
   cp.part_desc AS c_part_desc,
   cp.part_type AS c_part_type,
   b2.qty_per AS qty_c_per_p,
   b2.qty_per_type AS c_qty_type,
   cp.qty_on_hand AS c_qty_on_hand,
   b2.bom_doc_nbr AS parent_part_nbr,
   pp.part_desc AS p_part_desc,
   pp.qty_on_hand AS p_qty_on_hand
FROM (
         SELECT
            b1.*,
            COUNT (top_assy_1) OVER (ORDER BY rnum) AS grp_num
         FROM (
                  SELECT 
                     c_bill.*,
                     CASE
                        WHEN LEVEL = 1
                        THEN bom_doc_nbr
                     END   AS top_assy_1,
                     LEVEL AS bill_lvl
                     ROWNUM AS rnum
                  FROM (
                           SELECT 
                              c_bill.comp_part_nbr, 
                              c_bill.qty_per, 
                              c_bill.qty_per_type, 
                              c_bill.oper_nbr, 
                              c_bill.comp_off_adj, 
                              c_bill.bom_doc_nbr
                           FROM bill 
                           WHERE (
                                    (bill.status = 'RL') AND
                                    (bill.view_code<>'E' AND
                                    (bill.qty_per_type<>0) AND
                                    (bill.end_eff_dt>SYSDATE) AND
                                    (bill.begn_eff_dt<=SYSDATE)
                                 )
                        ) c_bill
                  CONNECT BY PRIOR comp_part_nbr=bom_doc_nbr
               ) b1
      ) b2,
      part cp,
      part pp
WHERE
   b2.comp_part_nbr=cp.part_nbr AND
   b2.bom_doc_nbr=pp.part_nbr
and here's my converted SQL:

Code:
strSQL="SELECT"
strSQL=strSQL & "   MAX(b2.top_assy_1) OVER (PARTITION BY grp_num) AS top_assy,"
strSQL=strSQL & "   b2.bill_lvl,"
strSQL=strSQL & "   b2.comp_part_nbr,"
strSQL=strSQL & "   cp.part_desc AS c_part_desc,"
strSQL=strSQL & "   cp.part_type AS c_part_type,"
strSQL=strSQL & "   b2.qty_per AS qty_c_per_p,"
strSQL=strSQL & "   b2.qty_per_type AS c_qty_type,"
strSQL=strSQL & "   cp.qty_on_hand AS c_qty_on_hand,"
strSQL=strSQL & "   b2.bom_doc_nbr AS parent_part_nbr,"
strSQL=strSQL & "   pp.part_desc AS p_part_desc,"
strSQL=strSQL & "   pp.qty_on_hand AS p_qty_on_hand"
strSQL=strSQL & "FROM ("
strSQL=strSQL & "         SELECT"
strSQL=strSQL & "            b1.*,"
strSQL=strSQL & "            COUNT (top_assy_1) OVER (ORDER BY rnum) AS grp_num"
strSQL=strSQL & "         FROM ("
strSQL=strSQL & "                  SELECT "
strSQL=strSQL & "                     c_bill.*,"
strSQL=strSQL & "                     CASE"
strSQL=strSQL & "                        WHEN LEVEL = 1"
strSQL=strSQL & "                        THEN bom_doc_nbr"
strSQL=strSQL & "                     END   AS top_assy_1,"
strSQL=strSQL & "                     LEVEL AS bill_lvl"
strSQL=strSQL & "                     ROWNUM AS rnum"
strSQL=strSQL & "                  FROM ("
strSQL=strSQL & "                           SELECT "
strSQL=strSQL & "                              c_bill.comp_part_nbr, "
strSQL=strSQL & "                              c_bill.qty_per, "
strSQL=strSQL & "                              c_bill.qty_per_type, "
strSQL=strSQL & "                              c_bill.oper_nbr, "
strSQL=strSQL & "                              c_bill.comp_off_adj, "
strSQL=strSQL & "                              c_bill.bom_doc_nbr"
strSQL=strSQL & "                           FROM bill"
strSQL=strSQL & "                           WHERE ("
strSQL=strSQL & "                                    (bill.status = 'RL') AND"
strSQL=strSQL & "                                    (bill.view_code<>'E' AND"
strSQL=strSQL & "                                    (bill.qty_per_type<>0) AND"
strSQL=strSQL & "                                    (bill.end_eff_dt>SYSDATE) AND"
strSQL=strSQL & "                                    (bill.begn_eff_dt<=SYSDATE)"
strSQL=strSQL & "                                 )"
strSQL=strSQL & "                        ) c_bill"
strSQL=strSQL & "                  CONNECT BY PRIOR comp_part_nbr=bom_doc_nbr"
strSQL=strSQL & "               ) b1"
strSQL=strSQL & "      ) b2,"
strSQL=strSQL & "      part cp,"
strSQL=strSQL & "      part pp"
strSQL=strSQL & "WHERE"
strSQL=strSQL & "   b2.comp_part_nbr=cp.part_nbr AND"
strSQL=strSQL & "   b2.bom_doc_nbr=pp.part_nbr"