Converting ANSI to Jet 2

[miller1975]

Hello all,
I have this query in ASNI (written for my DB2 database) and I want it converted to Jet as I am going to now run it in Access.
Please help

SELECT B.CUSTOMER_NBR,						
       B.CONTRACT_ID,						
       A.LEGAL_NAME,						
       (CASE WHEN E.TEAM IS NULL THEN C.TEAM						
             ELSE E.TEAM END) AS TEAM,						
       A.NATL_ACCT_EMPL,						
       A.SAR_CODE AS CUST_INV_HOLD,						
       D.SAR_CODE AS CONT_INV_HOLD,						
       D.SAR_CODE_END_DT AS INV_HOLD_END_DT						
  FROM ACRP1.CONTRACT B,						
       ACRP1.CONTRACT_II D,						
       ACRP1.BRANCH C,						
       ACRP1.CUSTOMER A 						
  LEFT OUTER JOIN ACRP1.CUST_TEAM E						
    ON E.CUSTOMER_NBR = A.CUSTOMER_NBR						
 WHERE A.CUSTOMER_NBR = B.CUSTOMER_NBR						
   AND B.CUSTOMER_NBR = D.CUSTOMER_NBR						
   AND B.CONTRACT_ID = D.CONTRACT_ID						
   AND B.BRANCH_CO_NBR = C.BRANCH_CO_NBR						
   AND B.BRANCH_NBR = C.BRANCH_NBR						
   AND B.CONTRACT_STATUS = 'A'						
   AND (A.SAR_CODE = 'Y' OR D.SAR_CODE = 'Y');

 

[PHV]

SELECT B.CUSTOMER_NBR,
B.CONTRACT_ID,
A.LEGAL_NAME,
Nz(E.TEAM, C.TEAM) AS TEAM,
A.NATL_ACCT_EMPL,
A.SAR_CODE AS CUST_INV_HOLD,
D.SAR_CODE AS CONT_INV_HOLD,
D.SAR_CODE_END_DT AS INV_HOLD_END_DT
FROM (((ACRP1.CONTRACT B
INNER JOIN ACRP1.CONTRACT_II D
ON B.CUSTOMER_NBR = D.CUSTOMER_NBR
AND B.CONTRACT_ID = D.CONTRACT_ID)
INNER JOIN ACRP1.BRANCH C
ON B.BRANCH_CO_NBR = C.BRANCH_CO_NBR
AND B.BRANCH_NBR = C.BRANCH_NBR)
INNER JOIN ACRP1.CUSTOMER A
ON B.CUSTOMER_NBR = A.CUSTOMER_NBR)
LEFT JOIN ACRP1.CUST_TEAM E
ON B.CUSTOMER_NBR = E.CUSTOMER_NBR
WHERE B.CONTRACT_STATUS = 'A'
AND (A.SAR_CODE = 'Y' OR D.SAR_CODE = 'Y');

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Golom]

You need two changes

(CASE WHEN E.TEAM IS NULL THEN C.TEAM
ELSE E.TEAM END) AS TEAM

becomes

IIF(IsNull(E.TEAM), C.TEAM, E.TEAM) As [TEAM]

and

LEFT OUTER JOIN

is just

LEFT JOIN

 

[miller1975]

Thank you guys. You all been very helpful. Gave stars for the response.

 

[miller1975]

I am getting "Micorsoft Access can't represent the join expression B.Branch_CO_NBR = C.BRANCH_CO_NBR in Design View" when I try to paste the code in the query SQL mode.

 

[Golom]

The Access query designer gets confused with embedded multi-field joins in design view but it should still be able to process the equivalent SQL.

Just go into SQL-View, paste the code and save it or run it without switching to design view.

 

[miller1975]

I didnt try to switch to design view. I pasted the code in SQL view and tried to run..
it didnt work.

I am going to try entering
ACRP1_CONTRACT AS B instead of
ACRP1.CONTRACT B