SQL Pass Thru from normal qry

[perryclisbee via AccessMonster.com]

I have a qry that pulls information from 4 seperate tables that are all
linked via ODBC. It's reaaaaally slow. I was told that one way to speed it
up is to create a pass thru qry, so I copied the entire SQL code from the
query and pasted it in a pass thru query. Is that Ok to do? Because after I
try and connect to Oracle (entering username, password, server), I keep on
getting the following message:

"ODBC call failed.... Invalid relational operator (#920).

My ODBC is set up for that server.
Any idea what I may be doing wrong?

thanks,

Perry

 

[perryclisbee via AccessMonster.com]

Below is the SQL coding. Is the structure wrong? I altered the date
parameters and put single quotes where double quotes used to be, but still
get the same error.:

SELECT SPIDER_PATIENT_DIM.EXPIRED, SPIDER_FACILITY_DIM.EXPIRED,
SPIDER_FINANCIAL_CLASS_DIM.EXPIRED, SPIDER_INSURANCE_DIM.EXPIRED,
SPIDER_CLAIM_FACT.POSTING_DATE, SPIDER_FACILITY_DIM.FACILITY_ABBR,
SPIDER_PATIENT_DIM.PATIENT_MPI, SPIDER_CLAIM_FACT.COVERAGE_TYPE,
SPIDER_PATIENT_DIM.LAST_NAME, SPIDER_PATIENT_DIM.FIRST_NAME,
SPIDER_CLAIM_FACT.DATASET_CODE, SPIDER_INSURANCE_DIM.INSURANCE_PLAN_TYPE,
SPIDER_CLAIM_FACT.CONTRACT, SPIDER_PATIENT_DIM.LAST_MODALITY_NAME,
SPIDER_FACILITY_DIM.FACILITY_CODE, SPIDER_FACILITY_DIM.FACILITY_NAME,
SPIDER_FACILITY_DIM.STATE, SPIDER_FINANCIAL_CLASS_DIM.FINANCIAL_CLASS_DESC,
SPIDER_CLAIM_FACT.PRIMARY_SPA, SPIDER_INSURANCE_DIM.PLAN_GROUP,
SPIDER_INSURANCE_DIM.INSURANCE_NAME, SPIDER_PATIENT_DIM.ADDRESS1,
SPIDER_PATIENT_DIM.ADDRESS2, SPIDER_PATIENT_DIM.CITY, SPIDER_PATIENT_DIM.
STATE, SPIDER_PATIENT_DIM.ZIP, SPIDER_PATIENT_DIM.MEDICARE_SWITCH_DATE

FROM (((SPIDER_CLAIM_FACT INNER JOIN SPIDER_FACILITY_DIM ON
(SPIDER_CLAIM_FACT.FACILITY_PTR = SPIDER_FACILITY_DIM.FACILITY_PTR) AND
(SPIDER_CLAIM_FACT.DATASET_CODE = SPIDER_FACILITY_DIM.DATASET_CODE)) INNER
JOIN SPIDER_PATIENT_DIM ON (SPIDER_CLAIM_FACT.PATIENT_PTR =
SPIDER_PATIENT_DIM.PATIENT_PTR) AND (SPIDER_CLAIM_FACT.DATASET_CODE =
SPIDER_PATIENT_DIM.DATASET_CODE)) INNER JOIN SPIDER_FINANCIAL_CLASS_DIM ON
(SPIDER_CLAIM_FACT.FINANCIAL_CLASS_PTR = SPIDER_FINANCIAL_CLASS_DIM.
FINANCIAL_CLASS_PTR) AND (SPIDER_CLAIM_FACT.DATASET_CODE =
SPIDER_FINANCIAL_CLASS_DIM.DATASET_CODE)) INNER JOIN SPIDER_INSURANCE_DIM ON
(SPIDER_CLAIM_FACT.INSURANCE_PTR = SPIDER_INSURANCE_DIM.INSURANCE_PTR) AND
(SPIDER_CLAIM_FACT.DATASET_CODE = SPIDER_INSURANCE_DIM.DATASET_CODE)

WHERE (((SPIDER_PATIENT_DIM.EXPIRED)='31-DEC-9999') AND ((SPIDER_FACILITY_DIM.
EXPIRED)='31-DEC-9999') AND ((SPIDER_FINANCIAL_CLASS_DIM.EXPIRED)='31-DEC-
9999') AND ((SPIDER_INSURANCE_DIM.EXPIRED)='31-DEC-9999') AND (
(SPIDER_CLAIM_FACT.POSTING_DATE) 'Between 01 JUL 2006 And 31 JUL 2006') AND (
(SPIDER_FACILITY_DIM.FACILITY_ABBR) Like '*-A') AND ((SPIDER_CLAIM_FACT.
COVERAGE_TYPE)='P') AND ((SPIDER_CLAIM_FACT.DATASET_CODE)='D') AND (
(SPIDER_INSURANCE_DIM.PLAN_GROUP)='HUMA')) OR (((SPIDER_PATIENT_DIM.EXPIRED)
='31-DEC-9999') AND ((SPIDER_FACILITY_DIM.EXPIRED)='31-DEC-9999') AND (
(SPIDER_FINANCIAL_CLASS_DIM.EXPIRED)='31-DEC-9999') AND (
(SPIDER_INSURANCE_DIM.EXPIRED)='31-DEC-9999') AND ((SPIDER_CLAIM_FACT.
POSTING_DATE) Between '01 JUL 2006 And 31 JUL 2006') AND (
(SPIDER_FACILITY_DIM.FACILITY_ABBR) Like '*-A') AND ((SPIDER_CLAIM_FACT.
COVERAGE_TYPE)='P') AND ((SPIDER_CLAIM_FACT.DATASET_CODE)='G') AND (
(SPIDER_INSURANCE_DIM.INSURANCE_NAME) Like 'HU*'));



Thanks,

Perry

 

[Duane Hookom]

I don't know if ORACLE supports "INNER JOIN". A quick google search provided
this link http://www.oreilly.com/catalog/orsqlpluspr2/chapter/ch01.html.

I'm fairly certain ORACLE uses "%" rather than "*" as a wildcard. You have
weird quoting at
'Between 01 JUL 2006 And 31 JUL 2006'

Once you get the syntax and date expressions correct, I expect your query
will perform much faster.