Problem with left join statement

Hi
Im having problems migrating a query from Oracle to MS Access..after
much modification i arrived at this query:

SELECT t4.org_nm, t1.run_id, t3.org_id, t1.Plan_Yr_n, t2.plan_id,
t3.Opt_Out_i,
(IIf(t3.Prod_ty_c="EPO","HMO",t3.Prod_ty_c)) as Product,
t3.bus_ln_c, t3.FUND_TY_C, t2.orig_plan_c,
t2.orig_plan_nm
FROM (((BENEXCH_T_RIBT_BTCH t1
LEFT JOIN BENEXCH_T_RIOR_ORG t4 ON (t1.org_id = t4.org_id))
LEFT JOIN BENEXCH_T_RCOP_ORG_PLAN_RUN t2 ON (t1.run_id = t2.run_id))
LEFT JOIN RFI_T_RCPL_ORG_PLAN t3 ON (t1.org_id = t3.org_id
AND (t2.plan_id = t3.plan_id))
WHERE
((t1.plan_yr_n = 2006 OR t1.plan_yr_n = 2005)
AND t4.Org_nm Not IN ('%Atria%', '%Honda%', '%Analog%')
AND t3.bus_ln_c = 'MEDICAL'
AND t3.Prod_ty_c IN ('HMO', 'EPO', 'PPO', 'POS', 'CDHC', 'UNK',
'UNKNOWN', 'IND')
AND t1.Use_i = 'Y'
AND t2.Orig_Plan_c NOT LIKE 'COB%' AND t2.Orig_Plan_c NOT LIKE 'RET%')



Im sure it must be a tiny,stupid error..but when i run it,it says
"Syntax error in Join Operation"
Any help?

re: Problem with left join statement

It's probably a mismatch in the number of parentheses in the FROM clause.


man7 wrote:[color=blue]
> Hi
> Im having problems migrating a query from Oracle to MS Access..after
> much modification i arrived at this query:
>
> SELECT t4.org_nm, t1.run_id, t3.org_id, t1.Plan_Yr_n, t2.plan_id,
> t3.Opt_Out_i,
> (IIf(t3.Prod_ty_c="EPO","HMO",t3.Prod_ty_c)) as Product,
> t3.bus_ln_c, t3.FUND_TY_C, t2.orig_plan_c,
> t2.orig_plan_nm
> FROM (((BENEXCH_T_RIBT_BTCH t1
> LEFT JOIN BENEXCH_T_RIOR_ORG t4 ON (t1.org_id = t4.org_id))
> LEFT JOIN BENEXCH_T_RCOP_ORG_PLAN_RUN t2 ON (t1.run_id = t2.run_id))
> LEFT JOIN RFI_T_RCPL_ORG_PLAN t3 ON (t1.org_id = t3.org_id
> AND (t2.plan_id = t3.plan_id))
> WHERE
> ((t1.plan_yr_n = 2006 OR t1.plan_yr_n = 2005)
> AND t4.Org_nm Not IN ('%Atria%', '%Honda%', '%Analog%')
> AND t3.bus_ln_c = 'MEDICAL'
> AND t3.Prod_ty_c IN ('HMO', 'EPO', 'PPO', 'POS', 'CDHC', 'UNK',
> 'UNKNOWN', 'IND')
> AND t1.Use_i = 'Y'
> AND t2.Orig_Plan_c NOT LIKE 'COB%' AND t2.Orig_Plan_c NOT LIKE
> 'RET%')
>
>
>
> Im sure it must be a tiny,stupid error..but when i run it,it says
> "Syntax error in Join Operation"
> Any help?[/color]

re: Problem with left join statement

SELECT t4.org_nm, t1.run_id, t3.org_id, t1.Plan_Yr_n, t2.plan_id,
t3.Opt_Out_i,
(IIf(t3.Prod_ty_c="EPO","HMO",t3.Prod_ty_c)) as Product,
t3.bus_ln_c, t3.FUND_TY_C, t2.orig_plan_c,
t2.orig_plan_nm
FROM (((BENEXCH_T_RIBT_BTCH t1
LEFT JOIN BENEXCH_T_RIOR_ORG t4 ON (t1.org_id = t4.org_id))
LEFT JOIN BENEXCH_T_RCOP_ORG_PLAN_RUN t2 ON (t1.run_id = t2.run_id))
LEFT JOIN RFI_T_RCPL_ORG_PLAN t3 ON (t1.org_id = t3.org_id)
AND (t2.plan_id = t3.plan_id))
WHERE
((t1.plan_yr_n = 2006 OR t1.plan_yr_n = 2005)
AND t4.Org_nm Not IN ('%Atria%', '%Honda%', '%Analog%')
AND t3.bus_ln_c = 'MEDICAL'
AND t3.Prod_ty_c IN ('HMO', 'EPO', 'PPO', 'POS', 'CDHC', 'UNK',
'UNKNOWN', 'IND')
AND t1.Use_i = 'Y'
AND t2.Orig_Plan_c NOT LIKE 'COB%' AND t2.Orig_Plan_c NOT LIKE 'RET%')






ya now it says join operation not supported..

re: Problem with left join statement

Are your data types for org_id, run_id, plan_id matching in the tables?



man7 wrote:[color=blue]
> SELECT t4.org_nm, t1.run_id, t3.org_id, t1.Plan_Yr_n, t2.plan_id,
> t3.Opt_Out_i,
> (IIf(t3.Prod_ty_c="EPO","HMO",t3.Prod_ty_c)) as Product,
> t3.bus_ln_c, t3.FUND_TY_C, t2.orig_plan_c,
> t2.orig_plan_nm
> FROM (((BENEXCH_T_RIBT_BTCH t1
> LEFT JOIN BENEXCH_T_RIOR_ORG t4 ON (t1.org_id = t4.org_id))
> LEFT JOIN BENEXCH_T_RCOP_ORG_PLAN_RUN t2 ON (t1.run_id = t2.run_id))
> LEFT JOIN RFI_T_RCPL_ORG_PLAN t3 ON (t1.org_id = t3.org_id)
> AND (t2.plan_id = t3.plan_id))
> WHERE
> ((t1.plan_yr_n = 2006 OR t1.plan_yr_n = 2005)
> AND t4.Org_nm Not IN ('%Atria%', '%Honda%', '%Analog%')
> AND t3.bus_ln_c = 'MEDICAL'
> AND t3.Prod_ty_c IN ('HMO', 'EPO', 'PPO', 'POS', 'CDHC', 'UNK',
> 'UNKNOWN', 'IND')
> AND t1.Use_i = 'Y'
> AND t2.Orig_Plan_c NOT LIKE 'COB%' AND t2.Orig_Plan_c NOT LIKE
> 'RET%')
>
>
>
>
>
>
> ya now it says join operation not supported..[/color]

re: Problem with left join statement

ya..the same query is running in Toad...guess i'll just manage with
Toad and skip the MS Access part..thanks

re: Problem with left join statement

access / jet sql has serious problems supporting joins that use more
than one criteria. you'd be advised to find an alternative way to form
the query...