Query in access having problems after conversion 1

[miller1975]

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.

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.

 

[lespaul]

Do you get an error message? No records returned?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[miller1975]

Hello Leslie,
When I run the converted code, i get messages like
"Micorsoft Access can't represent the join expression B.Branch_CO_NBR = C.BRANCH_CO_NBR in Design View" and so on...
And this happens for every compare expression...

 

[lespaul]

are you trying to view the SQL in the design grid? I would just run it from the SQL view.

 

[Golom]

Gotta be a parens thing. Try this

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") 
      AND (ACRP1_CONTRACT.CONTRACT_STATUS ="A")

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

I moved HAVING to WHERE since the HAVING conditions were field conditions ... not group conditions. I also noticed that your HAVING clause had the same test ORed with itself so I eliminated one of them. The rest was just removing parentheses that are not required.

 

[fredericofonseca]

try the following.

there is a error on one of the joins (should be left, not inner), and the having clause was using the wrong table.
Also moved it to the where clause group.

SELECT A.CUSTOMER_NBR,
 A.CONTRACT_ID,
 B.LEGAL_NAME,

IIF(IsNull(C.TEAM), D.TEAM, C.TEAM) As [TEAM],
 A.CONTRACT_STATUS,
 B.NATL_ACCT_EMPL,
 B.SAR_CODE,
 E.SAR_CODE,
 E.SAR_CODE_END_DT
FROM (((
ACRP1_CUSTOMER AS B
INNER JOIN ACRP1_CONTRACT AS A
   ON B.CUSTOMER_NBR = A.CUSTOMER_NBR)
INNER JOIN ACRP1_CONTRACT_II AS E
   ON (A.CUSTOMER_NBR = E.CUSTOMER_NBR) AND (A.CONTRACT_ID = E.CONTRACT_ID))
INNER JOIN ACRP1_BRANCH AS D
   ON (A.BRANCH_CO_NBR = D.BRANCH_CO_NBR) AND (A.BRANCH_NBR = D.BRANCH_NBR)

LEFT JOIN ACRP1_CUST_TEAM AS C
   ON B.CUSTOMER_NBR = C.CUSTOMER_NBR)

WHERE (B.SAR_CODE="Y" OR E.SAR_CODE="Y")
and A.CONTRACT_STATUS="A"

GROUP BY A.CUSTOMER_NBR,
 A.CONTRACT_ID,
 B.LEGAL_NAME,
 A.CONTRACT_STATUS,
 B.NATL_ACCT_EMPL,
 B.SAR_CODE,
 E.SAR_CODE,
 E.SAR_CODE_END_DT
;

Not fully sure as I don't have the table definitions.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[miller1975]

Here is the table definition.

COLUMN NAME	DATATYPE	FROM TABLE
CUSTOMER_NBR	DECIMAL (11,0)	CONTRACT
CONTRACT_ID	CHAR (12)	CONTRACT
LEGAL_NAME	CHAR (30)	CUSTOMER
TEAM	CHAR (4)	BRANCH/CUST_TEAM
NATL_ACCT_EMPL	CHAR (8)	CUSTOMER
CUST_INV_HOLD	CHAR (1)	CUSTOMER
CONT_INV_HOLD	CHAR (1)	CONTRACT_II
INV_HOLD_END_DT	DATE	CONTRACT_II

For the code give by GOLUM I get
"You tried to execute a query that does not include the specified expression
'IIf(IsNull(ACRP1_CUST_TEAM.TEAM),ACRP1_BRANCH.TEAM,ACRP1_CUST_TEAM.TEAM)' as part of an aggregrate function"

For fredericofonseca code I get
"Syntax error (missing operator) in query expression '(A.BRANCH_CO_NBR = D.BRANCH_CO_NBR) AND (A.BRANCH_NBR = D.BRANCH_NBR

LEFT JOIN ACPR1_CUST_TEAM AS C
ON B.CUSTOMER_NBR = C.CUSTOMER_NBR'.

 

[Golom]

OK. Now we're making some progress

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") 
      AND (ACRP1_CONTRACT.CONTRACT_STATUS ="A")

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,
         IIF(IsNull(ACRP1_CUST_TEAM.TEAM), ACRP1_BRANCH.TEAM, ACRP1_CUST_TEAM.TEAM)

 

[fredericofonseca]

again my version.

SELECT A.CUSTOMER_NBR,
 A.CONTRACT_ID,
 B.LEGAL_NAME,

IIF(IsNull(C.TEAM), D.TEAM, C.TEAM) As [TEAMX],
 A.CONTRACT_STATUS,
 B.NATL_ACCT_EMPL,
 B.SAR_CODE,
 E.SAR_CODE,
 E.SAR_CODE_END_DT
FROM (((
ACRP1_CUSTOMER AS B
INNER JOIN ACRP1_CONTRACT AS A
   ON B.CUSTOMER_NBR = A.CUSTOMER_NBR)
INNER JOIN ACRP1_CONTRACT_II AS E
   ON (A.CUSTOMER_NBR = E.CUSTOMER_NBR) AND (A.CONTRACT_ID = E.CONTRACT_ID))
INNER JOIN ACRP1_BRANCH AS D
   ON (A.BRANCH_CO_NBR = D.BRANCH_CO_NBR) AND (A.BRANCH_NBR = D.BRANCH_NBR))

LEFT JOIN ACRP1_CUST_TEAM AS C
   ON B.CUSTOMER_NBR = C.CUSTOMER_NBR

WHERE (B.SAR_CODE="Y" OR E.SAR_CODE="Y")
and A.CONTRACT_STATUS="A"

GROUP BY A.CUSTOMER_NBR,
 A.CONTRACT_ID,
 B.LEGAL_NAME,
 A.CONTRACT_STATUS,
 B.NATL_ACCT_EMPL,
 B.SAR_CODE,
 E.SAR_CODE,
 E.SAR_CODE_END_DT, IIF(IsNull(C.TEAM), D.TEAM, C.TEAM)
;

Both my ang Golom versions should work now. Results will be different

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com