Find in Oracle

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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.
 
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.
 
You need to add the "where" clause back in to the SQL - as described in
Matin's earlier post.


Tim
 
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
 
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.
 
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.


Sakol said:
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.
Click to expand...
 
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
 
Back
Top