ODBC/VBA?EXCEL and ORACLE database

[Guest]

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 sheet

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

 

[Tim Williams]

Can you paste an example of the generated SQL ?

What is the value of "pdate", and what is the datatype of PROPERTY_DATE ?

Tim.

 

[Guest]

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]

If your PROPERTY_DATE field includes a time component then your current
query will only return records where the timestamp is 00:00 (since by
default the time component will get assigned as 00:00), so you might think
about modifying that part if that's the case.

You seem to be missing a space here:
UNEARNED_RESIDUALFROM


Can you execute a "select *" on your view with no problems?
Have you tried running the sample query directly against the database in
SQLPlus ?

 

[Guest]

You are correct RE: the TimeStamp portion of the date. However, my "ORACLE
guru's" tell me the TO_DATE function will resolve that issue.

I found the "missing space" (it was hiding under the phone) and put it back
in but no help there.

I know nothing (or a little less) about SQLPLUS. I can run this query in
Access, as long as I run it as a Pass-Through query with the contracts list
in a separate table and not as an IN() list. The SQL from Access submits the
date as #06/30/05# => which I have tried but ended up with the same, rather
non-specific, error "SQL Syntax Error"

Thanx...

 

[DM Unseen]

BAC,

Excel is notorious for ditching valid SQL, This can be for a lot of
reasons (like the ODBC driver not liking the syntax.

download the following tool to help you out:

http://homepages.paradise.net.nz/~robree/excel/queryeditor.html

Your have 2 issues

- a parameter issue
- a lookup list issue.

try to use ODBC parameters by using questionmarks. After a refresh
excel will promtp you for a value and you can work from there

My advice on big and tricky queries: use VIEWS or stored procedures to
hide most of the logic.

For small lookup lists that need to be maintained in XL and need to be
joined to a query the following trick can be used:

Create a SQL stored proc that accepts a long string(ORACLE has max
2000). In that string delimit your values. Decompose this string in
your stored proc into a temp table and JOIN this with your original
query.

In Excel you can compose the string by concatenating all cells in a
certain range with a delimiter in between. This is then passed as one
parameter to the stored proc. This works well, but you need ODBC
parameter binding for this to work.

I have an example but that is for MS SQLserver (uses T-SQL) that has a
lookup list of around 25 items

Dm Unseen

 

[Tim Williams]

this is not going to work - as it will only get records entered at midnight

where PROPERTY_DATE= to_date('2005-06-30', 'yyyy-mm-dd')

try this instead

where (PROPERTY_DATE > to_date('2005-06-30', 'yyyy-mm-dd') and
PROPERTY_DATE < to_date('2005-07-01', 'yyyy-mm-dd')

or even

where (PROPERTY_DATE > to_date('2005-06-30', 'yyyy-mm-dd') and
PROPERTY_DATE < (to_date('2005-06-30', 'yyyy-mm-dd')+1)

Get one of your Oracle gurus to run your SQL in SQLPLus and get the real
error.

Tim.

 

[Guest]

I have successfully used the following SQL where to retreive dates from an
Oracle database:

WHERE (ALL_MARGINAL_PRICES.LOC_ID=4) AND (ALL_MARGINAL_PRICES.MP_DATE >= {ts
'" & st & "'}) AND (ALL_MARGINAL_PRICES.MP_DATE <= {ts '" & en & "'})"

be careful of the times.