| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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, A_COMPL_SUMMARY.PART_SEQUENCE, A_COMPL_SUMMARY.PART_NUMBER, A_COMPL_SUMMARY.LOT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE, A_COMPL_SUMMARY.ENTRY_DATE, A_COMPL_SUMMARY.NAME, A_COMPL_SUMMARY.MONTH, A_COMPL_SUMMARY.PRODUCT_FAMILY, A_COMPL_SUMMARY.COMPLAINT, A_COMPL_SUMMARY.REPORTABLE, A_COMPL_SUMMARY.PRODUCT_RETURNED, A_COMPL_SUMMARY.CASE_DESC, A_COMPL_SUMMARY.FAILURE_INVEST_DESC, A_COMPL_SUMMARY.LHR_SEARCH, A_COMPL_SUMMARY.ROOT_CAUSE, A_COMPL_SUMMARY.CORRECTIVE_ACTION, A_COMPL_SUMMARY.REGION, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION, RP_QA_PATIENT_CODES.PATIENT_CODE, RP_QA_PATIENT_CODES.PATIENT_CODE_CLARIF, RP_QA_PATIENT_CODES.PATIENT_CODE_SEVERITY, RP_QA_PATIENT_CODES.DESCRIPTION FROM CHSUSER.A_COMPL_SUMMARY A_COMPL_SUMMARY, CHSUSER.RP_QA_PATIENT_CODES RP_QA_PATIENT_CODES, CHSUSER.RP_QA_REPORTED_DEVICE_CODES RP_QA_REPORTED_DEVICE_CODES WHERE A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_PATIENT_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_PATIENT_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_PATIENT_CODES.CASE_NUMBER AND RP_QA_PATIENT_CODES.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND RP_QA_PATIENT_CODES.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND RP_QA_PATIENT_CODES.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND ((A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE Not Like ?) AND (A_COMPL_SUMMARY.PRODUCT_DIVISION='CP') AND (A_COMPL_SUMMARY.ENTRY_DATE=? And A_COMPL_SUMMARY.ENTRY_DATE<=?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ?) AND (A_COMPL_SUMMARY.REGION Like ?) AND (A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ?) AND (A_COMPL_SUMMARY.COMPLAINT Like ?)) (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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, A_COMPL_SUMMARY.PART_SEQUENCE, A_COMPL_SUMMARY.PART_NUMBER, A_COMPL_SUMMARY.LOT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE, A_COMPL_SUMMARY.ENTRY_DATE, A_COMPL_SUMMARY.NAME, A_COMPL_SUMMARY.MONTH, A_COMPL_SUMMARY.PRODUCT_FAMILY, A_COMPL_SUMMARY.COMPLAINT, A_COMPL_SUMMARY.REPORTABLE, A_COMPL_SUMMARY.PRODUCT_RETURNED, A_COMPL_SUMMARY.CASE_DESC, A_COMPL_SUMMARY.FAILURE_INVEST_DESC, A_COMPL_SUMMARY.LHR_SEARCH, A_COMPL_SUMMARY.ROOT_CAUSE, A_COMPL_SUMMARY.CORRECTIVE_ACTION, A_COMPL_SUMMARY.REGION, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION, RP_QA_PATIENT_CODES.PATIENT_CODE, RP_QA_PATIENT_CODES.PATIENT_CODE_CLARIF, RP_QA_PATIENT_CODES.PATIENT_CODE_SEVERITY, RP_QA_PATIENT_CODES.DESCRIPTION FROM CHSUSER.A_COMPL_SUMMARY A_COMPL_SUMMARY, CHSUSER.RP_QA_PATIENT_CODES RP_QA_PATIENT_CODES, CHSUSER.RP_QA_REPORTED_DEVICE_CODES RP_QA_REPORTED_DEVICE_CODES WHERE A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_PATIENT_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_PATIENT_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_PATIENT_CODES.CASE_NUMBER AND RP_QA_PATIENT_CODES.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND RP_QA_PATIENT_CODES.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND RP_QA_PATIENT_CODES.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND ((A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE Not Like ?) AND (A_COMPL_SUMMARY.PRODUCT_DIVISION='CP') AND (A_COMPL_SUMMARY.ENTRY_DATE=? And A_COMPL_SUMMARY.ENTRY_DATE<=?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ?) AND (A_COMPL_SUMMARY.REGION Like ?) AND (A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ?) AND (A_COMPL_SUMMARY.COMPLAINT Like ?)) 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, A_COMPL_SUMMARY.PART_SEQUENCE, A_COMPL_SUMMARY.PART_NUMBER, A_COMPL_SUMMARY.LOT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE, A_COMPL_SUMMARY.ENTRY_DATE, A_COMPL_SUMMARY.NAME, A_COMPL_SUMMARY.MONTH, A_COMPL_SUMMARY.PRODUCT_FAMILY, A_COMPL_SUMMARY.COMPLAINT, A_COMPL_SUMMARY.REPORTABLE, A_COMPL_SUMMARY.PRODUCT_RETURNED, A_COMPL_SUMMARY.CASE_DESC, A_COMPL_SUMMARY.FAILURE_INVEST_DESC, A_COMPL_SUMMARY.LHR_SEARCH, A_COMPL_SUMMARY.ROOT_CAUSE, A_COMPL_SUMMARY.CORRECTIVE_ACTION, A_COMPL_SUMMARY.REGION, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION, RP_QA_PATIENT_CODES.PATIENT_CODE, RP_QA_PATIENT_CODES.PATIENT_CODE_CLARIF, RP_QA_PATIENT_CODES.PATIENT_CODE_SEVERITY, RP_QA_PATIENT_CODES.DESCRIPTION FROM CHSUSER.A_COMPL_SUMMARY A_COMPL_SUMMARY, CHSUSER.RP_QA_PATIENT_CODES RP_QA_PATIENT_CODES, CHSUSER.RP_QA_REPORTED_DEVICE_CODES RP_QA_REPORTED_DEVICE_CODES WHERE A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_PATIENT_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_PATIENT_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_PATIENT_CODES.CASE_NUMBER AND RP_QA_PATIENT_CODES.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND RP_QA_PATIENT_CODES.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND RP_QA_PATIENT_CODES.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND ((A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE Not Like ?) AND (A_COMPL_SUMMARY.PRODUCT_DIVISION='CP') AND (A_COMPL_SUMMARY.ENTRY_DATE=? And A_COMPL_SUMMARY.ENTRY_DATE<=?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ?) AND (A_COMPL_SUMMARY.REGION Like ?) AND (A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ?) AND (A_COMPL_SUMMARY.COMPLAINT Like ?)) 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? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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, A_COMPL_SUMMARY.PART_SEQUENCE, A_COMPL_SUMMARY.PART_NUMBER, A_COMPL_SUMMARY.LOT_NUMBER, A_COMPL_SUMMARY.ALERT_DATE, A_COMPL_SUMMARY.ENTRY_DATE, A_COMPL_SUMMARY.NAME, A_COMPL_SUMMARY.MONTH, A_COMPL_SUMMARY.PRODUCT_FAMILY, A_COMPL_SUMMARY.COMPLAINT, A_COMPL_SUMMARY.REPORTABLE, A_COMPL_SUMMARY.PRODUCT_RETURNED, A_COMPL_SUMMARY.CASE_DESC, A_COMPL_SUMMARY.FAILURE_INVEST_DESC, A_COMPL_SUMMARY.LHR_SEARCH, A_COMPL_SUMMARY.ROOT_CAUSE, A_COMPL_SUMMARY.CORRECTIVE_ACTION, A_COMPL_SUMMARY.REGION, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE, RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION, RP_QA_PATIENT_CODES.PATIENT_CODE, RP_QA_PATIENT_CODES.PATIENT_CODE_CLARIF, RP_QA_PATIENT_CODES.PATIENT_CODE_SEVERITY, RP_QA_PATIENT_CODES.DESCRIPTION FROM CHSUSER.A_COMPL_SUMMARY A_COMPL_SUMMARY, CHSUSER.RP_QA_PATIENT_CODES RP_QA_PATIENT_CODES, CHSUSER.RP_QA_REPORTED_DEVICE_CODES RP_QA_REPORTED_DEVICE_CODES WHERE A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.INCIDENT_NUMBER = RP_QA_PATIENT_CODES.INCIDENT_NUMBER AND A_COMPL_SUMMARY.PART_SEQUENCE = RP_QA_PATIENT_CODES.PART_SEQUENCE AND A_COMPL_SUMMARY.CASE_NUMBER = RP_QA_PATIENT_CODES.CASE_NUMBER AND RP_QA_PATIENT_CODES.INCIDENT_NUMBER = RP_QA_REPORTED_DEVICE_CODES.INCIDENT_NUMBER AND RP_QA_PATIENT_CODES.PART_SEQUENCE = RP_QA_REPORTED_DEVICE_CODES.PART_SEQUENCE AND RP_QA_PATIENT_CODES.CASE_NUMBER = RP_QA_REPORTED_DEVICE_CODES.CASE_NUMBER AND ((A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ? Or A_COMPL_SUMMARY.PRODUCT_FAMILY Like ?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEVICE_CODE Not Like ?) AND (A_COMPL_SUMMARY.PRODUCT_DIVISION='CP') AND (A_COMPL_SUMMARY.ENTRY_DATE=? And A_COMPL_SUMMARY.ENTRY_DATE<=?) AND (RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ? Or RP_QA_REPORTED_DEVICE_CODES.REPORTED_DEV_CLARIFICATION Like ?) AND (A_COMPL_SUMMARY.REGION Like ?) AND (A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ? Or A_COMPL_SUMMARY.NAME Like ?) AND (A_COMPL_SUMMARY.COMPLAINT Like ?)) 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? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
