Find in Oracle

[Sakol]

How can i find field "Item_Number" in Oracle Object if found need to get
Item_Description to fill-out another sheet.

sub FindOCL()

SQL_Text = “SELECT item_number, Item_Description FROM mtl_system_items_bâ€
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0)
Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0)

If oraDynaSet.RecordCount > 0 Then
oraDynaSet.CopyToClipboard -1
oraDynaSet.MoveFirst
End If

End Sub

 

[Martin Fishlock]

Hi Sakol:

I have not used the oracle model but have used the SQL.

What you want from the query is the Item_descrption where it matches a
certain Item_number

so try this if item_number is a number:

SQL_Text = “SELECT Item_Description" & _
" FROM mtl_system_items_b†& _
" WHERE Item_number = " & 123

or if item_number is a string

SQL_Text = “SELECT Item_Description" & _
" FROM mtl_system_items_b†& _
" WHERE Item_number = '123'"


You can use a variable if you have one defined as in sItemNo

SQL_Text = “SELECT Item_Description" & _
" FROM mtl_system_items_b†& _
" WHERE Item_number = '" & sItemNo & "'"


You should then beable to actually get the data fro mthe record without
copying it to the clipboard but step through the code and have a look at the
object and the data in the vba editor.

 

[Sakol]

In additional if i have 3 fields Item_number, Item_Description, and Quantity
(see Example). The object from Oracle contain more than 1 records of
Item_number, I need to fill-out Description, sum quantity to excel sheet1
that Item_Number find & match to the Oracle Object.

Example:-

Oracle Object:
Item_Number Item_Description Quantity
A1 A1_Description 100
A1 A1_Description 200
B1 B1_Description 300
B1 B1_Description 400

Excel Sheet1:
Item_Number Description Quantity
A1 ? ?
B1 ? ?

Thanks

 

[Martin Fishlock]

OK, Sakol, try this but you may have to play around with it for a bit.

You SQL needs to be like this:

SQL_Text = “SELECT Item_number, Item_Description, sum(quantity) as SumQty"
& _
" FROM mtl_system_items_b†& _
" GROUP BY Item_number, Item_Description"

Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDatabase = objSession.OpenDatabase("fbnprod", "apps/apps", 0)
Set oraDynaSet = objDatabase.DBCreateDynaset(SQL_Text, 0)
on error resume next
If oraDynaSet.RecordCount > 0 Then
oraDynaSet.RecordCount
for i=1 to oraDynaSet.RecordCount
' not sure if you use ! or .
worksheets("sheet1").range("a" & i) = _
oraDynaSet!Item_number
worksheets("sheet1").range("b" & i) = _
oraDynaSet!Item_Description
worksheets("sheet1").range("c" & i) = _
oraDynaSet!SumQty

oraDynaSet.movenext
next i
' now do the clean up and close connections.

 

[Sakol]

Hi Martin,

Code is cool it's mean pull all data that i unexpect (C1,D1 ..Z..) from
Oracle to fill-out in excel sheet1. but i need only A1, and B1 to fill-out
description and quantity in the excel sheet1. That mean how can i use vba
Find, Seek, or similar etc. and do loop to get all records of A1, and B1 in
Oracle.

 

[Tim Williams]

You need to add the "where" clause back in to the SQL - as described in
Matin's earlier post.


Tim

 

[Martin Fishlock]

Hi,

It just lost my reply.

So use the 'in' statement in your sql as in :

SQL_Text = “SELECT Item_number, Item_Description," & _
" sum(quantity) as SumQty" & _
" FROM mtl_system_items_b†& _
" WHERE Item_number in ('A1', 'B1')" & _
" GROUP BY Item_number, Item_Description"

If that doesn't work try:
" WHERE Item_number = 'A1' or Item_number = 'B1')" & _

otherwise do the test in the code:

lrow=1
for i=1 to oraDynaSet.RecordCount
' not sure if you use ! or .
if oraDynaSet!Item_number = "A1" or _
oraDynaSet!Item_number = "B1" then
worksheets("sheet1").range("a" & i) = _
oraDynaSet!Item_number
worksheets("sheet1").range("b" & i) = _
oraDynaSet!Item_Description
worksheets("sheet1").range("c" & i) = _
oraDynaSet!SumQty
lrow=lrow+1
end if
oraDynaSet.movenext
next i

 

[Sakol]

Hi,

If the excel sheet1 have item unique more than 10,000 item_numbers, SQL not
serve i need. because of spend time to fill-WHERE condition more than 10,000
item_numbers. Could you please help to find the way that program can work
like example below.

Example :-
1st Loop:
Excel sheet1 item_number = "A1"
go to find data in Oracle object that item_number = "A1", if found
sum quantity all record that contain "A1" in Oracle object and fill out back
to Excel Sheet1 at line that contain "A1"
movenext to 2nd line

2nd Loop:
Excel sheet1 item_number = "1250-9999" 'at 2nd line of excel sheet1
go to find data in Oracle object that item_number = "1250-9999", if found
sum quantity all record that contain "1250-9999" in Oracle object and fill
out back to Excel Sheet1 at line that contain "1250-9999"
movenext to 3rd line

--do it until excel sheets end of record

Your kind help would be appreciate very much.

 

[Martin Fishlock]

Sakol

You need to make a loop going through the excel worksheet and modify the
sql statement run it and insert in in the current row then move down a row.

Have a go.

If you get stuck post a new question. But have a try.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

Hi,

If the excel sheet1 have item unique more than 10,000 item_numbers, SQL not
serve i need. because of spend time to fill-WHERE condition more than 10,000
item_numbers. Could you please help to find the way that program can work
like example below.

Example :-
1st Loop:
Excel sheet1 item_number = "A1"
go to find data in Oracle object that item_number = "A1", if found
sum quantity all record that contain "A1" in Oracle object and fill out back
to Excel Sheet1 at line that contain "A1"
movenext to 2nd line

2nd Loop:
Excel sheet1 item_number = "1250-9999" 'at 2nd line of excel sheet1
go to find data in Oracle object that item_number = "1250-9999", if found
sum quantity all record that contain "1250-9999" in Oracle object and fill
out back to Excel Sheet1 at line that contain "1250-9999"
movenext to 3rd line

--do it until excel sheets end of record

Your kind help would be appreciate very much.

 

[John Omana]

Hello All.

I am new to VBA/Oracle and I've been task to write an application that will
extract data from Oracle and then bring them into Excel which I guess similar
to the problem of Sakol.

I manage to extract the data from Oracle and saved them into Excel but I
need to limit the output using a worksheet within the excel file.

Can you please help me how can I use the worksheet called "Data" as part of
the query and then write them to a different worksheet.

Below are my codes which I found in the net.


Sub OracleExcel()

Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String

Dim OraDynaSet As Object
Dim objSession As Object
Dim objDataBase As Object
Dim i As Integer

'Create a reference to my database
Database_Name = "i021" ' Enter your database name here
User_ID = "ops$access" ' enter your user ID here
Password = "access" ' Enter your password here

'Create a reference to the OO4O dll
Set objSession = CreateObject("OracleInProcServer.XOraSession")
Set objDataBase = objSession.OpenDatabase(Database_Name, User_ID & "/" &
Password, 0&)

SQLStr = "SELECT BH_SUB_PROJ, BH_FAST_ACCESS, BH_DL_CD, BH_DOC_NO,
BH_SHT_NO, BH_DOC_REV_NO FROM BOM_HDR"
objDataBase.ExecuteSQL (SQLStr)

'Retrieve the results from Oracle
Set OraDynaSet = objDataBase.DBCreateDynaset(SQLStr, 0&)

If OraDynaSet.RecordCount > 0 Then
'There were records retrieved
OraDynaSet.MoveFirst

'Loop the recordset for returned rows
For i = 2 To OraDynaSet.RecordCount
'Put the results in column A, B, D, E and F
Sheets("Sample Data").Cells(i, 1) = OraDynaSet.Fields(0).Value
Sheets("Sample Data").Cells(i, 2) = OraDynaSet.Fields(1).Value
Sheets("Sample Data").Cells(i, 4) = OraDynaSet.Fields(3).Value
Sheets("Sample Data").Cells(i, 5) = OraDynaSet.Fields(4).Value
Sheets("Sample Data").Cells(i, 6) = OraDynaSet.Fields(5).Value
'End If
Next i

End If

Set OraDynaSet = Nothing
Set objSession = Nothing
objDataBase.Close
Set objDataBase = Nothing

End Sub

I have the following from worksheet data as an example.
WBS Priority P_Date M_Grp Addr. MTO_LVL
CA01 880 20090102 11 D01 I7
CA01 880 20090102 14 D01 I7

Need to filled worksheet Sample Data both from Oracle table and Worksheet
Data.
Sub Proj BH_FA WBS DWG_NO SHT Rev Priority P_Date M_Grp Addr. MTO_LVL

Fields WBS, Priority, P_Date, M_Grp, Addr., MTO_LVL will come from Worksheet
Data the rest from Oracle.

Oracle field must by equal to Worksheet table field.
[Data$]WBS = [BOM_HDR]BH_DL_CD
[Data$]Addr. = [BOM_HDR]BH_ADDR_CD
[Data$]MTO_LVL = [BOM_HDR]BH_MTO_LVL_CD

Hope you guys can help me.

Regards,
John