ODBC/VBA?EXCEL and ORACLE database

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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 ?


--
Tim Williams
Palo Alto, CA


"BAC" <[email protected]> wrote in message
news:[email protected]...
> 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','20
0-0000004-001','200-0000005-001','200-0000005-002','200-0000005-003','200-00
00006-001','200-0000007-002','200-0000007-003','200-0000007-004','200-000000
7-005','200-0000008-002','200-0000008-003','200-0000008-004','200-0000009-00
1','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-0000
017-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','20
1-000000
>
1-001','201-0000001-002','202-0000001-001','202-0000002-002','203-0000001-00
1','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-0000
006-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','21
5-2001033-003','215-2001033-004','215-2001035-001','216-2001032-001','216-20
01034-001','216-2001034-002','217-0000004-001','217-0000008-002','217-000000
9-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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

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...



"Tim Williams" wrote:

> 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 ?
>
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "BAC" <[email protected]> wrote in message
> news:[email protected]...
> > 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','20
> 0-0000004-001','200-0000005-001','200-0000005-002','200-0000005-003','200-00
> 00006-001','200-0000007-002','200-0000007-003','200-0000007-004','200-000000
> 7-005','200-0000008-002','200-0000008-003','200-0000008-004','200-0000009-00
> 1','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-0000
> 017-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','20
> 1-000000
> >
> 1-001','201-0000001-002','202-0000001-001','202-0000002-002','203-0000001-00
> 1','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-0000
> 006-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','21
> 5-2001033-003','215-2001033-004','215-2001035-001','216-2001032-001','216-20
> 01034-001','216-2001034-002','217-0000004-001','217-0000008-002','217-000000
> 9-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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

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/~ro...eryeditor.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

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.
--
Tim Williams
Palo Alto, CA


"BAC" <[email protected]> wrote in message
news:[email protected]...
> 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...
>
>
>
> "Tim Williams" wrote:
>
> > 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 ?
> >
> >
> > --
> > Tim Williams
> > Palo Alto, CA
> >
> >
> > "BAC" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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','20
> >
0-0000004-001','200-0000005-001','200-0000005-002','200-0000005-003','200-00
> >
00006-001','200-0000007-002','200-0000007-003','200-0000007-004','200-000000
> >
7-005','200-0000008-002','200-0000008-003','200-0000008-004','200-0000009-00
> >
1','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-0000
> >
017-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','20
> > 1-000000
> > >
> >
1-001','201-0000001-002','202-0000001-001','202-0000002-002','203-0000001-00
> >
1','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-0000
> >
006-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','21
> >
5-2001033-003','215-2001033-004','215-2001035-001','216-2001032-001','216-20
> >
01034-001','216-2001034-002','217-0000004-001','217-0000008-002','217-000000
> > 9-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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

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.



"Tim Williams" wrote:

> 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.
> --
> Tim Williams
> Palo Alto, CA
>
>
> "BAC" <[email protected]> wrote in message
> news:[email protected]...
> > 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...
> >
> >
> >
> > "Tim Williams" wrote:
> >
> > > 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 ?
> > >
> > >
> > > --
> > > Tim Williams
> > > Palo Alto, CA
> > >
> > >
> > > "BAC" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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','20
> > >
> 0-0000004-001','200-0000005-001','200-0000005-002','200-0000005-003','200-00
> > >
> 00006-001','200-0000007-002','200-0000007-003','200-0000007-004','200-000000
> > >
> 7-005','200-0000008-002','200-0000008-003','200-0000008-004','200-0000009-00
> > >
> 1','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-0000
> > >
> 017-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','20
> > > 1-000000
> > > >
> > >
> 1-001','201-0000001-002','202-0000001-001','202-0000002-002','203-0000001-00
> > >
> 1','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-0000
> > >
> 006-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','21
> > >
> 5-2001033-003','215-2001033-004','215-2001035-001','216-2001032-001','216-20
> > >
> 01034-001','216-2001034-002','217-0000004-001','217-0000008-002','217-000000
> > > 9-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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>