SQL for Oracle
I used Microsoft Excel Query to create a query that looks up an Oracle
database and of course brings results back to the spreadsheet based off the fields from the tables and criteria I select. Basically I have 3 tables joined by 3 different key fields. The criteria is where I run into problems. And I'm thinking at this point that it may just be that Excel can't handle the criteria breakdown. I have approximately 7 different criteria. Originally it's written something like this: PRODUCT_FAMILY REPORTED_DEVICE_CODE DIVISION ENTRY_DATE REPORTED_DEVICE_CLARIFICATION REGION NAME(COUNTRY) COMPLAINT Like [PF1] Or Like [PF2] Or Like [PF3] Or Like [PF4] Or Like [PF5] Not Like [Exclude] 'CP' =[Date1] And <=[Date2] Like [CL1] Or Like [CL2] Or Like [CL3] Or Like [CL4] Or Like [CL5] Like [R1] Like [C1] Or Like [C2] Or Like [C3] Or Like [C4] Or Like [C5] Like [Y/N] That looks pretty confusing; so I will copy/paste the SQL that is generated by everything: Code:
SELECT A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.CASE_NUMBER, (Data, Import External Data, New Database Query), it works fine. Problem is, I have to validate everything I do/create as far as queries, VBA, etc. So, final step in my validation is if the query ever has to be edited. So when I go back in to edit the query. It has automatically changed the criteria to be on one line to 125 lines (which I understand because 3 of my criteria have five(5) "or" statements. So 5 x 5 x 5 = 125. But, when I try and refresh data or "return data to Excel" I get an error message ORA-00936: missing expression. I looked this up on Google, etc and all I find is that it's an Oracle error (which is the database we use). From here, I've used a bunch of Oracle tools such as Toad for Oracle, SQL Plus, etc. I end up getting the same query (or SQL statement) written in a little bit different of variations that end up working in those particular programs. But I can't seem to get it to work in Excel. And I need to get it to work in Excel for other users, because they use other features with the data that is returned (such as Graphs, charts, pivot tables, etc). Any thoughts, suggestions, solutions? |
SQL for Oracle
Have you considered running a similar query in Access, and then getting the
data out of Access into Excel? Dave -- Brevity is the soul of wit. "David" wrote: I used Microsoft Excel Query to create a query that looks up an Oracle database and of course brings results back to the spreadsheet based off the fields from the tables and criteria I select. Basically I have 3 tables joined by 3 different key fields. The criteria is where I run into problems. And I'm thinking at this point that it may just be that Excel can't handle the criteria breakdown. I have approximately 7 different criteria. Originally it's written something like this: PRODUCT_FAMILY REPORTED_DEVICE_CODE DIVISION ENTRY_DATE REPORTED_DEVICE_CLARIFICATION REGION NAME(COUNTRY) COMPLAINT Like [PF1] Or Like [PF2] Or Like [PF3] Or Like [PF4] Or Like [PF5] Not Like [Exclude] 'CP' =[Date1] And <=[Date2] Like [CL1] Or Like [CL2] Or Like [CL3] Or Like [CL4] Or Like [CL5] Like [R1] Like [C1] Or Like [C2] Or Like [C3] Or Like [C4] Or Like [C5] Like [Y/N] That looks pretty confusing; so I will copy/paste the SQL that is generated by everything: Code:
SELECT A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.CASE_NUMBER, Anyway, Originally when I create this from scratch in Microsoft Query (Data, Import External Data, New Database Query), it works fine. Problem is, I have to validate everything I do/create as far as queries, VBA, etc. So, final step in my validation is if the query ever has to be edited. So when I go back in to edit the query. It has automatically changed the criteria to be on one line to 125 lines (which I understand because 3 of my criteria have five(5) "or" statements. So 5 x 5 x 5 = 125. But, when I try and refresh data or "return data to Excel" I get an error message ORA-00936: missing expression. I looked this up on Google, etc and all I find is that it's an Oracle error (which is the database we use). From here, I've used a bunch of Oracle tools such as Toad for Oracle, SQL Plus, etc. I end up getting the same query (or SQL statement) written in a little bit different of variations that end up working in those particular programs. But I can't seem to get it to work in Excel. And I need to get it to work in Excel for other users, because they use other features with the data that is returned (such as Graphs, charts, pivot tables, etc). Any thoughts, suggestions, solutions? |
SQL for Oracle
No I haven't tried that. I'm not sure how I'll do that either, because I'm
really trying to automate some dashboards for a bunch of engineers. And basically my VBA (macros) are written and I'm at the validation stage where if someone ever has to go back in and edit this query. At that point is where everything goes up in flames. I guess I would have to write in the validation portion that someone basically has to maintain the Access database (almost like a liaison to the Oracle Database. I still think there's got to be something very simple that I'm missing to get this query to work correctly; considering it works great the original time I create it. Why it bails with this ORA-00936 error message when you go back into edit it...that's where I'm stumped. "Dave F" wrote: Have you considered running a similar query in Access, and then getting the data out of Access into Excel? Dave -- Brevity is the soul of wit. "David" wrote: I used Microsoft Excel Query to create a query that looks up an Oracle database and of course brings results back to the spreadsheet based off the fields from the tables and criteria I select. Basically I have 3 tables joined by 3 different key fields. The criteria is where I run into problems. And I'm thinking at this point that it may just be that Excel can't handle the criteria breakdown. I have approximately 7 different criteria. Originally it's written something like this: PRODUCT_FAMILY REPORTED_DEVICE_CODE DIVISION ENTRY_DATE REPORTED_DEVICE_CLARIFICATION REGION NAME(COUNTRY) COMPLAINT Like [PF1] Or Like [PF2] Or Like [PF3] Or Like [PF4] Or Like [PF5] Not Like [Exclude] 'CP' =[Date1] And <=[Date2] Like [CL1] Or Like [CL2] Or Like [CL3] Or Like [CL4] Or Like [CL5] Like [R1] Like [C1] Or Like [C2] Or Like [C3] Or Like [C4] Or Like [C5] Like [Y/N] That looks pretty confusing; so I will copy/paste the SQL that is generated by everything: Code:
SELECT A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.CASE_NUMBER, Anyway, Originally when I create this from scratch in Microsoft Query (Data, Import External Data, New Database Query), it works fine. Problem is, I have to validate everything I do/create as far as queries, VBA, etc. So, final step in my validation is if the query ever has to be edited. So when I go back in to edit the query. It has automatically changed the criteria to be on one line to 125 lines (which I understand because 3 of my criteria have five(5) "or" statements. So 5 x 5 x 5 = 125. But, when I try and refresh data or "return data to Excel" I get an error message ORA-00936: missing expression. I looked this up on Google, etc and all I find is that it's an Oracle error (which is the database we use). From here, I've used a bunch of Oracle tools such as Toad for Oracle, SQL Plus, etc. I end up getting the same query (or SQL statement) written in a little bit different of variations that end up working in those particular programs. But I can't seem to get it to work in Excel. And I need to get it to work in Excel for other users, because they use other features with the data that is returned (such as Graphs, charts, pivot tables, etc). Any thoughts, suggestions, solutions? |
SQL for Oracle
See if this gives you any ideas:
http://www.contextures.com/excelfiles.html#External Select the QueryMaster.zip (The SQL is exposed to the user for easier maintenance.) Is that something you can work with? *********** Regards, Ron XL2002, WinXP "David" wrote: I used Microsoft Excel Query to create a query that looks up an Oracle database and of course brings results back to the spreadsheet based off the fields from the tables and criteria I select. Basically I have 3 tables joined by 3 different key fields. The criteria is where I run into problems. And I'm thinking at this point that it may just be that Excel can't handle the criteria breakdown. I have approximately 7 different criteria. Originally it's written something like this: PRODUCT_FAMILY REPORTED_DEVICE_CODE DIVISION ENTRY_DATE REPORTED_DEVICE_CLARIFICATION REGION NAME(COUNTRY) COMPLAINT Like [PF1] Or Like [PF2] Or Like [PF3] Or Like [PF4] Or Like [PF5] Not Like [Exclude] 'CP' =[Date1] And <=[Date2] Like [CL1] Or Like [CL2] Or Like [CL3] Or Like [CL4] Or Like [CL5] Like [R1] Like [C1] Or Like [C2] Or Like [C3] Or Like [C4] Or Like [C5] Like [Y/N] That looks pretty confusing; so I will copy/paste the SQL that is generated by everything: Code:
SELECT A_COMPL_SUMMARY.INCIDENT_NUMBER, A_COMPL_SUMMARY.CASE_NUMBER, Anyway, Originally when I create this from scratch in Microsoft Query (Data, Import External Data, New Database Query), it works fine. Problem is, I have to validate everything I do/create as far as queries, VBA, etc. So, final step in my validation is if the query ever has to be edited. So when I go back in to edit the query. It has automatically changed the criteria to be on one line to 125 lines (which I understand because 3 of my criteria have five(5) "or" statements. So 5 x 5 x 5 = 125. But, when I try and refresh data or "return data to Excel" I get an error message ORA-00936: missing expression. I looked this up on Google, etc and all I find is that it's an Oracle error (which is the database we use). From here, I've used a bunch of Oracle tools such as Toad for Oracle, SQL Plus, etc. I end up getting the same query (or SQL statement) written in a little bit different of variations that end up working in those particular programs. But I can't seem to get it to work in Excel. And I need to get it to work in Excel for other users, because they use other features with the data that is returned (such as Graphs, charts, pivot tables, etc). Any thoughts, suggestions, solutions? |
SQL for Oracle
David,
with a brief look at your query: The FROM statement appears to use many tables and there are not commas after every table name. Also, the wildcard character in Oracle is % and should be enclosed in single quotes. I don't have any OO4O code handy right now but I recall a syntax like: sqlstr = "select * from emp, dept where emp.deptno = dept.deptno and emp.ename like 'A%'" Also, I see more than 3 tables in your FROM clause. Is this helping any? Kostis Vezerides |
All times are GMT +1. The time now is 04:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com