By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,640 Members | 2,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,640 IT Pros & Developers. It's quick & easy.

Problem with left join statement

P: n/a
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?

May 16 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
DFS
It's probably a mismatch in the number of parentheses in the FROM clause.
man7 wrote:
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?

May 16 '06 #2

P: n/a

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..

May 16 '06 #3

P: n/a
DFS
Are your data types for org_id, run_id, plan_id matching in the tables?

man7 wrote:
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..

May 16 '06 #4

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

May 16 '06 #5

P: n/a
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...

May 16 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.