|
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 |
ODBC/VBA?EXCEL and ORACLE database
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" wrote in message ... 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 |
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" wrote in message ... 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 |
ODBC/VBA?EXCEL and ORACLE database
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 he 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" wrote in message ... 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" wrote in message ... 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 |
ODBC/VBA?EXCEL and ORACLE database
You are correct 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 he 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" wrote in message ... 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" wrote in message ... 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 |
ODBC/VBA?EXCEL and ORACLE database
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 |
ODBC/VBA?EXCEL and ORACLE database
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" wrote in message ... You are correct 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 he 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" wrote in message ... 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" wrote in message ... 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 |
ODBC/VBA?EXCEL and ORACLE database
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" wrote in message ... You are correct 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 he 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" wrote in message ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 01:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com