2
Answers

Update existing Oracle10g database connection with new query

Photo of Lotus

Lotus
12y
2.5k
1 Validations
this is my project 10 oracle database and visual studio 2005 version 8... with old plsql query its work....

Dim dbconn, sql, dbcomm, myReader     dbconn = New System.Data.OleDb.OleDbConnection("Provider=OraOLEDB.Oracle.1; Password=dev; Persist Security Info=True; User ID=dev; Data Source=PROD;")     dbconn.Open()     sql = "SELECT B.INVENTORY_ITEM_ID, B.SEGMENT1 || '-' || B.SEGMENT2 || '-' || B.SEGMENT3 || '-' ||B.SEGMENT4 || '-' || B.SEGMENT5 , B.primary_uom_code, sum(nvl(t.primary_quantity,0) ),B.DESCRIPTION from mtl_material_transactions t,mtl_system_items_b b "     sql = String.Concat(sql, " where b.inventory_item_id=t.inventory_item_id(+) and b.organization_id=t.organization_id(+) and b.organization_id=(", Session("OrgId"), ")")     sql = String.Concat(sql, " and upper(B.SEGMENT1 || '-' || B.SEGMENT2 || '-' || B.SEGMENT3 || '-' ||B.SEGMENT4 || '-' || B.SEGMENT5) like upper('%", Request.Form("search"), "%')")     sql = String.Concat(sql, " group by  B.INVENTORY_ITEM_ID, B.SEGMENT1 || '-' || B.SEGMENT2 || '-' || B.SEGMENT3 || '-' ||B.SEGMENT4 || '-' || B.SEGMENT5 , B.primary_uom_code,B.DESCRIPTION ")     sql = String.Concat(sql, " ORDER BY 2")     dbcomm = New System.Data.OleDb.OleDbCommand(sql, dbconn)     myReader = dbcomm.ExecuteReader()

this is my new new modified query i want to update above connection with this query


select sum(m.primary_quantity)   from apps.mtl_material_transactions m, apps.MTL_SYSTEM_ITEMS_B b  where m.inventory_item_id = b.inventory_item_id  and m.organization_id =b.organization_id    and b.description like 'PAPER CONE 68MM 5 PLY PRINT LINE GREEN'    and m.subinventory_code = 'SS'    and m.organization_id =82

can anyone help me plz ASAP

Answers (2)