0 Replies Latest reply on Mar 7, 2002 6:23 AM by 232271

    large sql in vb to fill Excel cells ?

    232271
      I have a large sql statement to call from Excel 97. I use Visual Basic (newbie).

      When I have a small sql it is ok (the cells are filling ok), but I don't know how to call a large sql. Can I call directly a sql file ? it would be easier. Or a package ? but how to retrieve info in cells ???

      Here the code I'm trying :

      ------------------
      Sub connect()

      Dim OraSession As Object 'Declare variables as OLE Objects
      Dim OraDatabase As Object
      Dim OraDynaset As Object
      Dim ColNames As Object

      'my sqlStmt is defined here on several lines of 255 chars max, and separated with ' _' at the end (not the last line)

      sqlStmt = "SELECT RAS.NAME, RAC.CUSTOMER_NUMBER, RAC.CUSTOMER_NAME, (raa.address1||decode(raa.address1,null,null,',')|| raa.postal_code||decode(raa.postal_code,null,null,',')|| raa.city||decode(raa.city,null,null,',')|| raa.country), ct.TRX_NUMBER, ct.TRX_DATE," _
      cl.SALES_ORDER, cl.SALES_ORDER_DATE, SUM(cl.REVENUE_AMOUNT) "Somme Bulletin" FROM RA_CUSTOMER_TRX_ALL ct, RA_CUSTOMERS RAC, RA_CUSTOMER_TRX_LINES_ALL cl, RA_ADDRESSES_ALL RAA, RA_SALESREPS_ALL RAS WHERE cl.REVENUE_AMOUNT <>0 AND CL.revenue_amount is" _
      not null AND cl.SALES_ORDER IS NOT NULL AND ct.COMPLETE_FLAG ='Y' AND ct.CUSTOMER_TRX_ID = cl.CUSTOMER_TRX_ID and exists (select 1 from RA_CUST_TRX_LINE_GL_DIST_ALL gl where cl.customer_trx_line_id = gl.customer_trx_line_id and GL.GL_DATE BETWEEN" _
      TO_DATE('01/02/2002', 'DD/MM/YYYY') AND TO_DATE('28/02/2002', 'DD/MM/YYYY')) AND CT.PRIMARY_SALESREP_ID = RAS.SALESREP_ID(+) AND CT.ORG_ID = RAS.ORG_ID(+) AND ct.BILL_TO_CUSTOMER_ID = RAC.CUSTOMER_ID AND RAC.CUSTOMER_ID = RAA.CUSTOMER_ID GROUP BY" _
      RAS.NAME, RAC.CUSTOMER_NUMBER, RAC.CUSTOMER_NAME, raa.address1, raa.postal_code, raa.city, raa.country, CT.TRX_NUMBER, ct.TRX_DATE, cl.SALES_ORDER, cl.SALES_ORDER_DATE ORDER BY RAS.NAME ASC, RAC.CUSTOMER_NUMBER ASC, RAC.CUSTOMER_NAME ASC, raa.address1," _
      raa.postal_code, raa.city, raa.country, ct.TRX_NUMBER ASC, ct.TRX_DATE ASC, cl.SALES_ORDER ASC, cl.SALES_ORDER_DATE ASC"

      Set OraSession = CreateObject("OracleInProcServer.XOraSession")
      Set OraDatabase = OraSession.DbOpenDatabase("ora11idb", "user/pass", 0&)
      Set OraDynaset = OraDatabase.DbCreateDynaset(sqlStmt, 0&)

      ' Using field array, ie. ColNames("ename").value, is significantly faster than using field lookup, ie. EmpDynaset.fields("ename").value
      Set ColNames = OraDynaset.Fields

      ' Place column headings on sheet
      ' note : Excel is installed in French language
      For icols = 1 To ColNames.Count
      'For icols = 1 To 2
      Worksheets("Feuil1").Cells(1, icols).Value = ColNames(icols - 1).Name
      Next

      ' Check if the current dynaset row is valid
      If OraDynaset.BOF <> True And OraDynaset.EOF <> True Then
      ' Place data on sheet using CopyToClipboard
      OraDynaset.CopyToClipboard -1
      Worksheets("Feuil1").Visible = True
      Sheets("Feuil1").Select
      Range("A2").Select
      Sheets("Feuil1").Paste
      End If

      End Sub
      ------------------

      When I try this I get 'Compile error / Syntax error' at 'sqlStmt'
      What is wrong ???

      Thanks, Antoine
      my e-mail please : antoine.theytaz at gmvs.migros.ch