oracle - SQL 查询上的 Excel VBA 自动化错误

Question

我是数据库新手，从我们的数据库人员那里得到了这个查询。不幸的是，他的最后一天是昨天。当我在 Oracle SQL Developer 中运行查询时，它可以在不到一秒的时间内返回约 20 条记录，但是当我在 Excel VBA 中尝试时，它在“rst.打开 StrQuery，cnn”。

这是我的代码。

'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

'Setup the connection string for accessing MS SQL database
ConnectionString = "Provider=OraOLEDB.Oracle;Password=XXXXX;User ID=XXXXX;Data Source=XXXXX;"

'Opens connection to the database
cnn.Open ConnectionString
'Timeout error in seconds for executing the entire query
cnn.CommandTimeout = 90

'Query String
StrQuery = "SELECT "
StrQuery = StrQuery & "distinct dbo.mfg_order.mfg_order_name, "
StrQuery = StrQuery & "dbo.mfg_order.mfg_bill_letter, "
StrQuery = StrQuery & "dbo.mfg_order.mfg_order_name || '-' || dbo.mfg_order.mfg_bill_letter as mfg_order_bill, "
StrQuery = StrQuery & "dbo.pcb_header.position_seq_nbr || '00', "
StrQuery = StrQuery & "D2.mfg_catg as catg,D2.mfg_catg_value as catgvalue "
StrQuery = StrQuery & "FROM "
StrQuery = StrQuery & "dbo.sales_order, "
StrQuery = StrQuery & "dbo.planned_shipment, "
StrQuery = StrQuery & "dbo.eco_include_exclude, "
StrQuery = StrQuery & "dbo.mfg_note_header, "
StrQuery = StrQuery & "dbo.pcb_subset, "
StrQuery = StrQuery & "dbo.pcb_detail D2, "
StrQuery = StrQuery & "dbo.pcb_header, "
StrQuery = StrQuery & "dbo.mfg_order "
StrQuery = StrQuery & "WHERE "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_name = dbo.pcb_header.mfg_order_name ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_bill_letter = dbo.pcb_header.mfg_bill_letter ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_revision = dbo.pcb_header.mfg_order_revision ) and "
StrQuery = StrQuery & "( dbo.mfg_order.planned_shipment_nbr = dbo.planned_shipment.planned_shipment_nbr (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.sales_ord_id = dbo.planned_shipment.sales_ord_id (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.sales_ord_id = dbo.sales_order.sales_ord_id (+) ) and "
StrQuery = StrQuery & "( dbo.pcb_header.pcb_header_id = dbo.pcb_subset.pcb_header_id (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_name = dbo.eco_include_exclude.mfg_order_name (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_bill_letter = dbo.eco_include_exclude.mfg_bill_letter (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_revision = dbo.eco_include_exclude.mfg_order_revision (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_name = dbo.mfg_note_header.mfg_order_name (+) ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_bill_letter = dbo.mfg_note_header.mfg_bill_letter (+)  ) and "
StrQuery = StrQuery & "( dbo.mfg_order.mfg_order_revision = dbo.mfg_note_header.mfg_order_revision (+) ) and "
StrQuery = StrQuery & "(dbo.pcb_header.pcb_header_id = D2.pcb_header_id ) and "
StrQuery = StrQuery & "dbo.mfg_order.bu_mfg_loc_id = 5 AND "
StrQuery = StrQuery & "dbo.pcb_header.prod_code='0050' AND "
StrQuery = StrQuery & "dbo.mfg_order.mfg_order_type = 'SO' and "
StrQuery = StrQuery & "dbo.pcb_header.mfg_prod_grp   = 'FN05' AND "
StrQuery = StrQuery & "(D2.mfg_catg = 'FSIZ') AND "
StrQuery = StrQuery & "D2.MFG_CATG_VALUE='28FB' AND "
StrQuery = StrQuery & "dbo.mfg_order.MFG_BILL_LETTER='A' AND "
StrQuery = StrQuery & "LENGTH(dbo.mfg_order.MFG_ORDER_NAME)=7;"

'Check Query
Debug.Print StrQuery

'Performs the actual query    
rst.Open StrQuery, cnn

'Dumps all the results from the StrQuery into cell A2 of the first sheet in the active workbook
'Sheets(1).Range("A2").CopyFromRecordset rst
End Sub

调试给了我查询:

SELECT distinct dbo.mfg_order.mfg_order_name, dbo.mfg_order.mfg_bill_letter, dbo.mfg_order.mfg_order_name || '-' || dbo.mfg_order.mfg_bill_letter as mfg_order_bill, dbo.pcb_header.position_seq_nbr || '00', D2.mfg_catg as catg,D2.mfg_catg_value as catgvalue FROM dbo.sales_order, dbo.planned_shipment, dbo.eco_include_exclude, dbo.mfg_note_header, dbo.pcb_subset, dbo.pcb_detail D2, dbo.pcb_header, dbo.mfg_order WHERE ( dbo.mfg_order.mfg_order_name = dbo.pcb_header.mfg_order_name ) and ( dbo.mfg_order.mfg_bill_letter = dbo.pcb_header.mfg_bill_letter ) and ( dbo.mfg_order.mfg_order_revision = dbo.pcb_header.mfg_order_revision ) and ( dbo.mfg_order.planned_shipment_nbr = dbo.planned_shipment.planned_shipment_nbr (+) ) and ( dbo.mfg_order.sales_ord_id = dbo.planned_shipment.sales_ord_id (+) ) and ( dbo.mfg_order.sales_ord_id = dbo.sales_order.sales_ord_id (+) ) and ( dbo.pcb_header.pcb_header_id = dbo.pcb_subset.pcb_header_id (+) ) and ( dbo.mfg_order.mfg_order_name = dbo.eco_include_exclude.mfg_order_name (+) ) and ( dbo.mfg_order.mfg_bill_letter = dbo.eco_include_exclude.mfg_bill_letter (+) ) and ( dbo.mfg_order.mfg_order_revision = dbo.eco_include_exclude.mfg_order_revision (+) ) and ( dbo.mfg_order.mfg_order_name = dbo.mfg_note_header.mfg_order_name (+) ) and ( dbo.mfg_order.mfg_bill_letter = dbo.mfg_note_header.mfg_bill_letter (+)  ) and ( dbo.mfg_order.mfg_order_revision = dbo.mfg_note_header.mfg_order_revision (+) ) and (dbo.pcb_header.pcb_header_id = D2.pcb_header_id ) and dbo.mfg_order.bu_mfg_loc_id = 5 AND dbo.pcb_header.prod_code='0050' AND dbo.mfg_order.mfg_order_type = 'SO' and dbo.pcb_header.mfg_prod_grp   = 'FN05' AND (D2.mfg_catg = 'FSIZ') AND D2.MFG_CATG_VALUE='28FB' AND dbo.mfg_order.MFG_BILL_LETTER='A' AND LENGTH(dbo.mfg_order.MFG_ORDER_NAME)=7;

这里它更易于人类阅读(其中一个在 SQL Developer 中工作):

SELECT 
distinct dbo.mfg_order.mfg_order_name, 
dbo.mfg_order.mfg_bill_letter, 
dbo.mfg_order.mfg_order_name || '-' || dbo.mfg_order.mfg_bill_letter as mfg_order_bill, 
dbo.pcb_header.position_seq_nbr || '00', 
D2.mfg_catg as catg,D2.mfg_catg_value as catgvalue 
FROM 
dbo.sales_order, 
dbo.planned_shipment, 
dbo.eco_include_exclude,    
dbo.mfg_note_header, 
dbo.pcb_subset, 
dbo.pcb_detail D2, 
dbo.pcb_header, 
dbo.mfg_order 
WHERE 
( dbo.mfg_order.mfg_order_name = dbo.pcb_header.mfg_order_name ) and 
( dbo.mfg_order.mfg_bill_letter = dbo.pcb_header.mfg_bill_letter ) and 
( dbo.mfg_order.mfg_order_revision = dbo.pcb_header.mfg_order_revision ) and 
( dbo.mfg_order.planned_shipment_nbr = dbo.planned_shipment.planned_shipment_nbr (+) ) and 
( dbo.mfg_order.sales_ord_id = dbo.planned_shipment.sales_ord_id (+) ) and 
( dbo.mfg_order.sales_ord_id = dbo.sales_order.sales_ord_id (+) ) and 
( dbo.pcb_header.pcb_header_id = dbo.pcb_subset.pcb_header_id (+) ) and 
( dbo.mfg_order.mfg_order_name = dbo.eco_include_exclude.mfg_order_name (+) ) and 
( dbo.mfg_order.mfg_bill_letter = dbo.eco_include_exclude.mfg_bill_letter (+) ) and 
( dbo.mfg_order.mfg_order_revision = dbo.eco_include_exclude.mfg_order_revision (+) ) and 
( dbo.mfg_order.mfg_order_name = dbo.mfg_note_header.mfg_order_name (+) ) and 
( dbo.mfg_order.mfg_bill_letter = dbo.mfg_note_header.mfg_bill_letter (+)  ) and 
( dbo.mfg_order.mfg_order_revision = dbo.mfg_note_header.mfg_order_revision (+) ) and 
(dbo.pcb_header.pcb_header_id = D2.pcb_header_id ) and 
dbo.mfg_order.bu_mfg_loc_id = 5 AND 
dbo.pcb_header.prod_code='0050' AND 
dbo.mfg_order.mfg_order_type = 'SO' and 
dbo.pcb_header.mfg_prod_grp   = 'FN05' AND 
(D2.mfg_catg = 'FSIZ') AND 
D2.MFG_CATG_VALUE='28FB' AND 
dbo.mfg_order.MFG_BILL_LETTER='A' AND 
LENGTH(dbo.mfg_order.MFG_ORDER_NAME)=7;

Answer 1

我假设您错过了 dbo.pcb_header.position_seq_nbr || '00' 的别名

尝试

dbo.pcb_header.position_seq_nbr || '00' AS position_seq,

试试这样:

'Initializes variables
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
DIM cmd As New ADODB.Command
Dim ConnectionString As String
Dim StrQuery As String

'Setup the connection string for accessing MS SQL database
ConnectionString = "Provider=OraOLEDB.Oracle;Password=XXXXX;User ID=XXXXX;Data Source=XXXXX;"

cnn.Provider = "OraOLEDB.Oracle"
cnn.CursorLocation = adUseClient


'Opens connection to the database
cnn.Open ConnectionString
'Timeout error in seconds for executing the entire query
cnn.CommandTimeout = 90

Set cmd.ActiveConnection = con
Set rst.Source = cmd
rst.CursorType = adOpenStatic

'Query String
StrQuery = "SELECT "
...

cmd.CommandText = StrQuery 
rst.Open