[RESOLVED] [Excel 2003 VBA & Oracle 8i db] Need help: From keyword not found where expected...

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"

---

Add a space between the quote and the F or after AS p_qty_on_hand

strSQL=strSQL & " pp.qty_on_hand AS p_qty_on_hand"
strSQL=strSQL & "FROM ("

The current code will generate this segment

pp.qty_on_hand AS p_qty_on_handFROM (

The error occurs because the word From is part of Alias name and not separated from the select list by a space.

Yup, caught that one. I also double-checked my SQL via MS Query, and discovered I'd made a few dumb errors in the SQL as well, which I have now fixed, and it should work in vba now.

I'm going to post my fixed code a little while.

Thanks!

Here's my final code, if anyone cares:

Code:

---


    strSQL = "SELECT MAX(b2.top_assy_1) OVER (PARTITION BY grp_num)  AS top_assy "
    strSQL = strSQL & ", b2.bill_lvl    AS bill_lvl "
    strSQL = strSQL & ", b2.comp_part_nbr    AS 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 b1.* "
    strSQL = strSQL & " , COUNT (top_assy_1) OVER (ORDER BY rnum) AS grp_num "
    strSQL = strSQL & " FROM ( "
    strSQL = strSQL & "  SELECT  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 bill.comp_part_nbr  "
    strSQL = strSQL & "  , bill.qty_per  "
    strSQL = strSQL & "  , bill.qty_per_type  "
    strSQL = strSQL & "  , bill.oper_nbr  "
    strSQL = strSQL & "  , bill.comp_off_adj  "
    strSQL = strSQL & "  , bill.bom_doc_nbr "
    strSQL = strSQL & "  FROM  BILL 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 b2.comp_part_nbr =cp.part_nbr AND "
    strSQL = strSQL & " b2.bom_doc_nbr  =pp.part_nbr "

---