miller1975
Programmer
I been having problems with this query for couple of days.
I was trying to convert this from Db2 to access but something is missing.
I was trying to convert this from Db2 to access but something is missing.
Code:
Db2 Version
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');
[\code]
[code]
The Access Version
SELECT ACRP1_CONTRACT.CUSTOMER_NBR, ACRP1_CONTRACT.CONTRACT_ID, ACRP1_CUSTOMER.LEGAL_NAME,
IIF(IsNull(ACRP1_CUST_TEAM.TEAM), ACRP1_BRANCH.TEAM, ACRP1_CUST_TEAM.TEAM) As [TEAM],
ACRP1_CONTRACT.CONTRACT_STATUS, ACRP1_CUSTOMER.NATL_ACCT_EMPL, ACRP1_CUSTOMER.SAR_CODE, ACRP1_CONTRACT_II.SAR_CODE, ACRP1_CONTRACT_II.SAR_CODE_END_DT
FROM (((ACRP1_CUSTOMER INNER JOIN ACRP1_CUST_TEAM ON ACRP1_CUSTOMER.CUSTOMER_NBR = ACRP1_CUST_TEAM.CUSTOMER_NBR) INNER JOIN ACRP1_CONTRACT ON ACRP1_CUSTOMER.CUSTOMER_NBR = ACRP1_CONTRACT.CUSTOMER_NBR) INNER JOIN ACRP1_CONTRACT_II ON (ACRP1_CONTRACT.CUSTOMER_NBR = ACRP1_CONTRACT_II.CUSTOMER_NBR) AND (ACRP1_CONTRACT.CONTRACT_ID = ACRP1_CONTRACT_II.CONTRACT_ID)) INNER JOIN ACRP1_BRANCH ON (ACRP1_CONTRACT.BRANCH_CO_NBR = ACRP1_BRANCH.BRANCH_CO_NBR) AND (ACRP1_CONTRACT.BRANCH_NBR = ACRP1_BRANCH.BRANCH_NBR)
WHERE (((ACRP1_CUSTOMER.SAR_CODE)="Y")) OR (((ACRP1_CONTRACT_II.SAR_CODE)="Y"))
GROUP BY ACRP1_CONTRACT.CUSTOMER_NBR, ACRP1_CONTRACT.CONTRACT_ID, ACRP1_CUSTOMER.LEGAL_NAME, ACRP1_CONTRACT.CONTRACT_STATUS, ACRP1_CUSTOMER.NATL_ACCT_EMPL, ACRP1_CUSTOMER.SAR_CODE, ACRP1_CONTRACT_II.SAR_CODE, ACRP1_CONTRACT_II.SAR_CODE_END_DT
HAVING (((ACRP1_CONTRACT.CONTRACT_STATUS)="A" Or (ACRP1_CONTRACT.CONTRACT_STATUS)="A"));
[\code]
Anyone, please help me here.