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

[elleg]

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:

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:

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"

[brucevde]

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.

[elleg]

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!

[elleg]

Here's my final code, if anyone cares:

 
    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 "