Find in Oracle
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
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.
"Sakol" wrote:
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.
--
Sakol
"Martin Fishlock" wrote:
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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.
"Sakol" wrote:
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
--
Sakol
"Martin Fishlock" wrote:
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.
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.
"Sakol" wrote:
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
--
Thanks
Sakol
|