| 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 looked at a freebee from Oracle called Oracle Objects for OLE
(OO4O)? I haven't used it since the days of Excel 97, but it was a lifesaver them - installed on each workstation needing to access the Oracle DB, it allows very simple connection to and extraction of data from Oracle DB. We used it extensively in some custom work I did for SWBell and the SBC Corp. As I said, my age may be showing - all of that was done back just before Office 2000 was released, and I haven't had a call to do Oracle interfacing from Excel since then. The only 'limitation' is whatever permissions are set up for the users to access the Oracle DB. In our case, the users were limited to reading from a single view that had already done most of the work such as you're doing in your SQL statement - we were just pulling unique records from that and filtering them down even more. "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
Unfortuanately I'm not dealing with a permissions issue. In fact everyone
has tried out my VBA driven Excel Template and the query works fine. I am just trying to validate everything, and when I go to validate the actual written query (or SQL Statement), when you go back into edit it, is when I get the problems. I'm thinking at this point to just write part of the validation as the Microsoft Query has to be re-written any time you have to go in to edit it. (which should be never) but, I have to validate it as if it might have to be edited someday. "JLatham" wrote: Have you looked at a freebee from Oracle called Oracle Objects for OLE (OO4O)? I haven't used it since the days of Excel 97, but it was a lifesaver them - installed on each workstation needing to access the Oracle DB, it allows very simple connection to and extraction of data from Oracle DB. We used it extensively in some custom work I did for SWBell and the SBC Corp. As I said, my age may be showing - all of that was done back just before Office 2000 was released, and I haven't had a call to do Oracle interfacing from Excel since then. The only 'limitation' is whatever permissions are set up for the users to access the Oracle DB. In our case, the users were limited to reading from a single view that had already done most of the work such as you're doing in your SQL statement - we were just pulling unique records from that and filtering them down even more. "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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
