thanks, but I am well aware of how to do that.
I probably didn't explain well enough.
After successfully creating a query (using Microsoft Query in Excel) and returning the results successfully as well, I am having to validate the query that I created. To validate it I have to be able to go back in to edit the query. When I do this, with or without changes to the query, if I then refresh the query or try and return the results back to Excel, I get a ORA-00936: missing expression error.
Here is an example of the criteria I write in the beginning that is successful in returning the DB results to excel:
TEMPLATEProductFamily-ReportedCode.xls |
---|
|
---|
| A | B | C | D | E | F | G | H |
---|
1 | PRODUCT_FAMILY | REPORTED_DEVICE_CODE | DIVISION | ENTRY_DATE | REPORTED_DEVICE_CLARIFICATION | REGION | NAME(COUNTRY) | COMPLAINT |
---|
2 | Like[PF1]OrLike[PF2]OrLike[PF3]OrLike[PF4]OrLike[PF5] | NotLike[Exclude] | 'CP' | >=[Date1]And<=[Date2] | Like[CL1]OrLike[CL2]OrLike[CL3]OrLike[CL4]OrLike[CL5] | Like[R1] | Like[C1]OrLike[C2]OrLike[C3]OrLike[C4]OrLike[C5] | Like[Y/N] |
---|
|
---|
Now, at the point of writing the above criteria in MS Query, if I just refresh the data without returning it to Excel, it is successful, and at this point I've selected to view the SQL which here is a copy/paste of that:
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 ?))
So, I copied/pasted that SQL into a new worksheet as text. Went out of MS Query, opened a new Workbook in Excel, went straight to Data, Import External Data, New Database Query, then opened the View SQL and pasted the SQL (from above) and end up getting a Syntax error or access violation error box.
I really need help with the MS Query portion of this Message.
Recap:
1. No problem writing initial Query, Parameters, Fields, Tables, Joins, Criteria--nothing. Everything runs smoothly and returns data from DB into Excel exactly as needed.
2. Problem arises when having to edit the query. When you go back in to it, after a query refresh, or an attempt to return data to Excel a ORA-00936: missing expression error message pops up.
3. Only way I've been able to fix it is by re-writing the Criteria (which i've copied/pasted an example above) everytime before returning data to Excel
PLEASE HELP!!!, I'm very frustrated at validation processes at this point. it's the only thing holding me back from finalizing a major project.
Thanks,
David