Using Excel VBA to query database & SQL

Below is an abstract how i used Excel VBA to develop Excel Macros, with specific functions to query from Oracle Database to generate the desired result/outcome i want (ie: wafer_id) when i received emails from colleagues only with limited information as input source.

With the know-how in SQL command and the domain knowledge of where the data is stored in database, it is very helpful to tabulate a more complete picture from a RDMS (ie: Oracle Relational Database Management System)

► Below is a video demo using Excel VBA showing the query of Oracle database to query or search for products, Lot Type or in any other form of data analysis using SQL to perform summation or count of your inventory

Sub WAFER_ID_QUERIES()

Dim wafer_id As String
Dim i As Long
Dim j As Integer
Dim last_col As Integer

wafer_id = “”
i = 2
Do While Cells(i, 1) <> “”
wafer_id = wafer_id & “,’” & Cells(i, 1) & “‘“
i = i + 1
Loop

If wafer_id <> “” Then
wafer_id = Trim(Right(wafer_id, Len(wafer_id) — 1))
End If



If Cells(1, 3) <> “” Then
Range(Cells(1, 3), Cells(1, 11)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range(“A1”).Select
End If

SQL_String = “ SELECT a.ID, d.INGOT_SEG, b.LOT_NUM as CPN, b.CURR_PRODUCT as MAPL, c.CUST_SHORT_NAME as CUST_NAME, c.CUST_SPEC_NICK_NAME as NICKNAME, b.CURR_OPERATION as CURR_OPN, b.HOLD_NAME , b.HOLD_NOTE , b.CURR_OWNER as OWNER “ _
& “ FROM MPPDB_OWNER.MCS_WAFER a INNER JOIN MPPDB_OWNER.LOT_MASTERS b ON a.materialid = b.lot_num INNER JOIN MPPDB_OWNER.PRODUCT_SPECS c ON b.CURR_PRODUCT = c.WS_PRODUCT INNER JOIN MPPDB_OWNER.WAFERS d ON a.id = d.id “ _
& “ WHERE a.id IN (“ & wafer_id & “) “
& “ AND b.DELETED = ’N’ “ _
& “ AND b.CURR_OPERATION <= ‘2010’ “ _
& “ AND b.CURR_QTY > 0 “ _
& “ ORDER BY b.CURR_PRODUCT , b.CURR_OPERATION “


i = 0
Set database_connection= New XXX.Database_Connection_Oracle
Set OracleDB_IDM1 = New ADODB.Recordset
If DBC.Connect_OracleDB(OracleDB_IDM1, SQL_String, , , “COGNOS”, “XXXX”, “XXXX”) = True Then ‘// IDM_OWNER True.
If OracleDB_IDM1.EOF = False Then
‘// 01: Load Field Name.
For j = 0 To OracleDB_IDM1.Fields.Count — 1
Cells(1, j + 3) = OracleDB_IDM1.Fields(j).Name
Next j
last_col = j + 3
‘// 02: Load Field Data.
i = 2
Do While Not OracleDB_IDM1.EOF
For j = 0 To OracleDB_IDM1.Fields.Count — 1
If IsNull(OracleDB_IDM1.Fields(j)) = False Then
Cells(i, j + 3) = OracleDB_IDM1.Fields(j)
End If
Next j
i = i + 1
OracleDB_IDM1.MoveNext
Loop
End If
End If
Set OracleDB_IDM1 = Nothing

End Sub

The above Excel VBA is a snippets of how i modify the SQL to query from the Oracle Database to return me with the desired results/outcome (ie: product name, customer, product_nickname, operation) from an array/list of data as input (ei: waferid)

2) With the change in SQL, i can query with a different result the aggregate of wafers, product and customer name for those wafers flag with ‘Recut’. Hence, SQL is very useful for real-time query from Oracle RDMS database using Excel Macro.

select curr_product, cust_spec_nick_name, sum(curr_qty) as total_qty from

(

select t1.CURR_PRODUCT, t1.CURR_QTY, ps.cust_spec_nick_name from LOT_MASTERS t1,

(select STAB_NUMMER , STAB_END_POSITION, STAB_NUMMER || ‘/’ || STAB_END_POSITION AS ingots from MPPDB_OWNER.STAEBE where STAB_RECUT_FLAG = ‘Y’ ) t2, product_specs ps

where t1.CURR_OPERATION = ‘1795’ AND t1.PARENT_LOT_NUM = t2.ingots(+) AND t1.curr_product = ps.ws_product(+)

) group by curr_product, cust_spec_nick_name having sum(curr_qty) > 0;

3) To manage any manufacturing operations/work-centre that violates FIFO (ie: move-out fresh wip instead of aging wip). With the aim to resolve/reduce the amount of aging wip inventory at certain manufacturing operations, below is the SQL used to detect for FIFO violation

FIFO Violation Detection

Select a.lot_num , a.product

From lot_movement_v a , lot_masters b

Where (sysdate — b.enter_oper_date) < ( sysdate — min (b.enter_oper_date)) and * check for move_out CT less than < max CT

Where a.operation=’2010’ and a.operation_new=’2025’ and *check for products that move out from opn: 2010

Where a.product=b.product and

Where a.owner=’PROD’ and a.facility=’4701’ and

Where mvou_date=sysdate * display only when product move_out on same date