Re: ODBC/VBA?EXCEL and ORACLE database

pdate is the date selected by the user from a drop down box listing "AS OF"
date for the report(s). The date is stored in date formatted cell on
sheets("MISC").cell (17,4)


"PROPERTY_DATE is date value in ORACLE view in Datawarehouse. A "straight
read" of this value includes TimeStamp
Generated SQL:

debug.Print sql_str
SELECT LEASE_TYPE, CONTRACT_NBR, LL_NET_CBR, LL_REMAINING_PRETAX_INC,
RESIDUAL_NET, CONTRACT_BALANCE, UNEARNED_FINANCE, UNEARNED_RESIDUALFROM
IL_REPORT_CONTRACTS_FINAL_CURRENT WHERE (PROPERTY_DATE= to_date('2005-06-30',
'yyyy-mm-dd'))AND (CONTRACT_NBR IN
('200-0000001-001','200-0000002-001','200-0000002-003','200-0000002-004','200-0000004-001','200-0000005-001','200-0000005-002','200-0000005-003','200-0000006-001','200-0000007-002','200-0000007-003','200-0000007-004','200-0000007-005','200-0000008-002','200-0000008-003','200-0000008-004','200-0000009-001','200-0000009-004','200-0000009-005','200-0000009-006','200-0000010-002','200-0000011-001','200-0000011-002','200-0000011-003','200-0000011-004','200-0000012-001','200-0000013-001','200-0000014-001','200-0000016-001','200-0000017-001','200-0000018-001','200-0000020-001','200-0000021-001','200-0000022-001','200-0000022-002','200-0000023-001','200-0000023-002','200-0000024-001','200-0000031-001','200-0000035-001','200-0000036-001','200-0000037-001','201-000000
1-001','201-0000001-002','202-0000001-001','202-0000002-002','203-0000001-001','203-0000001-002','204-0000001-001','204-0000001-002','204-0000001-003','204-0000002-001','204-0000003-001','204-0000003-002','204-0000003-003','204-0000004-001','204-0000004-002','204-0000005-001','204-0000006-001','204-0000006-002','204-0000007-001','204-0000007-002','204-0000007-003','205-0000001-001','206-2001005-001','206-2001006-001','206-2001007-001','207-0990091-001','207-0990091-003','207-0990091-004','215-2001033-001','215-2001033-002','215-2001033-003','215-2001033-004','215-2001035-001','216-2001032-001','216-2001034-001','216-2001034-002','217-0000004-001','217-0000008-002','217-0000009-001'))ORDER
BY CONTRACT_NBR;

THanx..



"Tim Williams" wrote:

> Can you paste an example of the generated SQL ?
>
> What is the value of "pdate", and what is the datatype of PROPERTY_DATE ?
>
> Tim.
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "BAC" <BAC@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:C15D18F0-1F07-49AB-86F5-D97CAFADDCC7@xxxxxxxxxxxxxxxx
> > We recently converted one of our datawarehouses to ORACLE.
> >
> > I am now having difficulty converting several Excel Macros that use VBA
> > generated SQL statements to execute. e.g.:
> > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> > Sub gogetem()
> > 'Fields appropriately dimmed
> > 'Loads Warehouse Data to the DownLoad ***
> >
> > curwrkbk = Format(Sheets("MISC").Cells(18, 2), "mmddyy") &
> "_this_File.xls"
> > curwrkbk = "\\crpAtlFnp03\Accounting\" & curwrkbk
> >
> > K_List = contract_list 'Function builds list of contracts to have data
> > returned
> >
> > 'Retrieve data from data warehouse
> > 'Get the detail data
> > 'Initialize period date variables
> > 'User selects report "AS of Date" from drop down and selection stored in:
> >
> > pdate = Sheets("Misc").Cells(17, 4).Value
> >
> >
> > Sql_Str = "SELECT LEASE_TYPE, CONTRACT_NBR, LL_NET_CBR,
> > LL_REMAINING_PRETAX_INC, " & _
> > "RESIDUAL_NET, CONTRACT_BALANCE, UNEARNED_FINANCE,
> UNEARNED_RESIDUAL
> > " & _
> > "FROM IL_REPORT_CONTRACTS_FINAL_CURRENT " & _
> > "WHERE (PROPERTY_DATE= to_date('" & pdate & "', 'yyyy-mm-dd'))AND
> "
> > & _
> > "(CONTRACT_NBR IN (" & K_List & "))" & _
> > "ORDER BY CONTRACT_NBR;"
> >
> > Sheets("Download").Select
> > Range("A2").Select
> > With Selection.QueryTable
> > .Connection = _
> > "ODBC;DRIVER={Microsoft ODBC for Oracle};UID=" & IAM & ";PWD=" &
> > MyPWD & ";SERVER=CDMP.com;"
> > .Sql = Sql_Str
> >
> > .Refresh BackgroundQuery:=False
> >
> > End With
> >
> > ActiveWorkbook.SaveAs Filename:=curwrkbk
> >
> > End Sub
> >
> > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
> >
> > The debugger kicks in at the " .Refresh BackgroundQuery:=False"
> >
> > With a SQL Syntax error.
> >
> > pdate format must be "yyyy-mm-dd", and my understanding is that the
> TO_DATE
> > function is required to override ORACLE default format of :date
> Timestamp"
> >
> > K_List format is:
> > ('123-0000007-000','123-0000008-001',...)
> >
> > I run the query in ACCESS without difficulty when K_List is in a separate
> > table.
> > Access bombs with "Exceeding 1,024 character limit for query grid" error
> > when I keep IN(list) format in grid layout
> >
> > When I try to run as an SQL Pass-through I get an ODBC error indicating
> > "this operator must be followed by Any or ALL", but nothing to indicate
> what
> > "this operator" may be.
> >
> > Any help would be greatly appreciated as we are soon to convert all out
> data
> > warehouses to ORACLE and I have tons of similar queries that will need to
> be
> > revised as well...
> >
> > TIA
> >
> > BAC
>
>
>
.

Loading