VB Programmer with an Oracle SQ question 1

[Trudye]

I have inherited this (sub) query from a Guy who coveted his work. I have figured out everything else except I cannot figure out what the (+) is for. Because of the field values he could not have been adding the fields.

Does anyone have any idea what (+) means and how it would translate to Access SQL?

Thank you so much,
Trudye

(SELECT 0
FROM SVT_INV_RPT_T
WHERE VIN_NUM=I.VIN_NUM
AND LEASE_ACCT_NUM=I.LEASE_ACCT_NUM
AND BUS_SRC_CD=I.BUS_SRC_CD
AND CUR_RPC_ID IN('S309 ','S308 ')
AND SVT_CATG_TYP_CD='B'
AND VEH_CUR_STAT_CD='AQ')
AND I.MDL_LINE=M.MDL_LINE(+)
AND I.NMC_MDL_CD=M.NMC_MDL_CD(+)
AND I.VIN_YEAR=M.VIN_YEAR(+)
AND I.VIN_NUM=N.VIN_NUM(+)
AND I.VIN_NUM=R.VIN_NUM
AND I.LEASE_ACCT_NUM=R.CNTR_NUM
AND I.BUS_SRC_CD=R.BUS_SRC_CD
AND R.BK_FLG!='Y'
AND I.VEH_SLD_SEQ_NUM=0

 

[SantaMufasa]

Trudye,

The "(+)" operator invokes an Oracle outer join. In your code, for example, "AND I.MDL_LINE=M.MDL_LINE(+)" means, "Match up the values of MDL_LINE from the 'I' table with values of MDL_LINE from the 'M' table. If, however, the 'M' table has no matches with the 'I' table, don't let that prevent the displaying of 'I' rows that would have otherwise displayed if there were matching values."

Without the outer-join operator ["(+)"], Oracle will not display 'M' rows that lack matches with the 'I' table.

Does that answer your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Trudye]

Got it! Give me all I recs and those M recs that match the I recs?

Thank you so much I can duplicate that in Access.

Trudye

 

[SantaMufasa]

That is correct, Trudye. (And thanks for the star!)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Trudye]

I am trying to convert this query from Oracle to Access. I am getting an error
Syntax Error 'Missing Operator" in query expression 'Case When...'

Does anyone have any idea what is wrong with the syntax in this query?

Thanx,
Trudye

 SELECT R.RETAIL_CNTR_NUM, I.VIN_NUM
, I.LEASE_ACCT_NUM, I.BUS_SRC_CD, MID(I.VIN_NUM,1,8),  VIN8, 
CASE WHEN I.DIST_CHAN_CD='9' AND UCASE(I.NMC_MDL_CD) NOT IN ('NISSA','INFIN') THEN N.MODEL_CD 
WHEN I.DIST_CHAN_CD='9' AND UCASE(I.NMC_MDL_CD) IN('NISSA','INFIN') THEN MID(N.MODEL_CD,1,3) & 'X' & MID(RTRIM(N.MODEL_CD),5,1) 
ELSE MID(I.NMC_MDL_CD,1,3) & 'X' & MID(I.NMC_MDL_CD,5,1)
END, 
MDL_CD, I.VIN_YEAR, 
CAST(CASE WHEN MID(I.FULL_MDL_YR3,1,1)='0' THEN '20' ELSE '19' END & MID(I.FULL_MDL_YR3,1,2) AS NUMBER) MDL_YR
, UCASE(CASE I.DIST_CHAN_CD WHEN '1' THEN 'Nissan' WHEN '2' THEN 'Infiniti' 
ELSE N.MAKE_DEC END) 
MAKE, Ucase(CASE I.DIST_CHAN_CD WHEN '9' THEN N.MODEL_DESC 
ELSE M.MDL_LINE_DESC END) 
MODEL, I.CUR_RPC_ID, 
CASE WHEN I.EXCS_WTEAR_AMT+I.MISS_PART_AMT>=2000 OR I.EXCS_WTEAR_AMT=1 THEN 'X' ELSE NULL END 
EXCS_DMG, R.BK_FLG, R.RETAIL_RCVRY_ST_CD
FROM SMM.VEH_EVENTS_ENH_T I, SMM.REPO_BANKRUPTCY_T R, SMM.SMM_NONNNA_VEH_T N, SMM.MDL_LINE_T M
WHERE EXISTS

 

[SantaMufasa]

So, Trudye, are you seeing this error in Oracle or in Access?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[Trudye]

Hi Mufasa, thanks for responding. I am seeing the error in Access.

 

[SantaMufasa]

Can't help ya' there. You will receive quickest results by clicking on this link to this MS Access forum and posting your inquiry there.

Regards,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]