Error in JOIN Query

[miller1975]

Hello,
I had written this query for importing some data. I made some changes to it but now its not working. This is written to VB to update a Access DB
Please advice whats wrong

SELECT A.CUSTOMER_NBR, A.CONTRACT_ID, A.BRANCH_CO_NBR, A.BRANCH_NBR, C.BRANCH_NAME, D.DEALER_NBR, D.DEALER_NM, D.SOG_NBR, E.SOG_NAME, A.CNTR_BILL_ADDR, I.LAST_NAME AS BILLTO_NAME, A.ACCT_PLAN_ID, A.SETUP_DATE, F.CONT_COLL_STAT, K.EMPLOYE_ID AS EMPLOYEE_WORKING , F.RT_DAYS_PST_DUE, A.CYCLE_DAY, A.TOTAL_RENT AS CONTRACT_BAL, A.TOTAL_RENT+B.TOT_NOW_DUE AS TOT_CONTRACT_BAL, B.TOT_NOW_DUE AS RENT_NOW_DUE, B.TOT_CURRENT_AGED AS RENT_CURR_AGED, B.TOT_OVER_30_AGED AS RENT_OV30_AGED, B.TOT_OVER_60_AGED AS RENT_OV60_AGED, B.TOT_OVER_90_AGED AS RENT_OV90_AGED, B.TOT_OVER_120_AGD AS RENT_OV120_AGED, B.TOT_150_AGED_AMT AS RENT_OV150_AGED, B.TOT_210_AGED_AMT+B.TOT_240_AGED_AMT+B.TOT_270_AGED_AMT+B.TOT_300_AGED_AMT+B.TOT_330_AGED_AMT+B.TOT_360_AGED_AMT+B.TOT_180_AGED_AMT AS RENT_OV180_AGED, K.TEAM AS TEAM, H.LEGAL_NAME AS CUSTOMER_NAME
FROM (((((((((ACRP1_CONTRACT AS A INNER JOIN ACRP1_SCHEDULE_TOTALS AS B ON (A.CUSTOMER_NBR = B.CUSTOMER_NBR) AND (A.CONTRACT_ID = B.CONTRACT_ID)) INNER JOIN ACRP1_BRANCH AS C ON (A.BRANCH_CO_NBR = C.BRANCH_CO_NBR) AND (A.BRANCH_NBR = C.BRANCH_NBR)) INNER JOIN ACRP1_PAYSYS_CUST_MSTR AS J ON (A.CUSTOMER_NBR = J.CUSTOMER_NBR) AND (A.CONTRACT_ID = J.CONTRACT_ID)) INNER JOIN ACRP1_EMPLOYEE AS K ON (J.QR_COLLECTOR = K.EMPL_COLL_ID)) INNER JOIN ACRP1_DEALER AS D ON (C.DEALER_CO_NBR = D.DEALER_CO_NBR) AND (C.DEALER_NBR = D.DEALER_NBR)) INNER JOIN ACRP1_SOG AS E ON (D.SOG_CO_NBR = E.SOG_CO_NBR) AND (D.SOG_NBR = E.SOG_NBR)) INNER JOIN ACRP1_CONTRACT_II AS F ON (A.CUSTOMER_NBR = F.CUSTOMER_NBR) AND (A.CONTRACT_ID = F.CONTRACT_ID)) INNER JOIN ACRP1_CUSTOMER AS H ON A.CUSTOMER_NBR = H.CUSTOMER_NBR) LEFT JOIN ACRP1_CONTRACT_NAMES AS I ON (A.CUSTOMER_NBR = I.CUSTOMER_NBR) AND (A.CONTRACT_ID = I.CONTRACT_ID)) LEFT JOIN ACRP1_CUST_TEAM AS G ON A.CUSTOMER_NBR = G.CUSTOMER_NBR
WHERE (((A.CONTRACT_STATUS)='A' Or (A.CONTRACT_STATUS)='T' AND ((A.CLOSE_BILL_IND)<>'T')) AND ((B.SCHEDULE)='B') AND ((B.SCHEDULE_TYPE)='RENT') AND ((I.NAME_TYPE)='BLTO');

 

[bjd4jc]

What is the error essage? "it's not working" is not very descriptive. Please read faq222-2244 for tips on how to get a better response.

I would suggest that you start by breaking this down into smaller portions and working with them one at a time.

 

[jebenson]

It also might help if you could post the original SQL that worked, and re-post the changed SQL with the changes highlighted (e.g., in red or in bold text).

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[miller1975]

Hello,
This is the original code I had in VB..

While i <= max
    strSQL = "INSERT INTO CONTRACT_THOR ( CUSTOMER_NBR, CONTRACT_ID, BRANCH_CO_NBR, BRANCH_NBR, BRANCH_NAME, DEALER_NBR, DEALER_NM, SOG_NBR, SOG_NAME, CNTR_BILL_ADDR, BILLTO_NAME, ACCT_PLAN_ID, SETUP_DATE, CONT_COLL_STAT, EMPLOYEE_WORKING, NBR_DAYS_PST_DUE, CYCLE_DAY, CONTRACT_BAL, TOT_CONTRACT_BAL, RENT_NOW_DUE, RENT_CURR_AGED, RENT_OV30_AGED, RENT_OV60_AGED, RENT_OV90_AGED, RENT_OV120_AGED, RENT_OV150_AGED, RENT_OV180_AGED, TEAM, CUSTOMER_NAME )"
    strSQL = strSQL + " SELECT A.CUSTOMER_NBR, A.CONTRACT_ID, A.BRANCH_CO_NBR, A.BRANCH_NBR, C.BRANCH_NAME, D.DEALER_NBR, D.DEALER_NM, D.SOG_NBR, E.SOG_NAME, A.CNTR_BILL_ADDR, I.LAST_NAME AS BILLTO_NAME, A.ACCT_PLAN_ID, A.SETUP_DATE, F.CONT_COLL_STAT, H.EMPLOYEE_WORKING, F.RT_DAYS_PST_DUE, A.CYCLE_DAY, A.TOTAL_RENT AS CONTRACT_BAL, A.TOTAL_RENT+B.TOT_NOW_DUE AS TOT_CONTRACT_BAL, B.TOT_NOW_DUE AS RENT_NOW_DUE, B.TOT_CURRENT_AGED AS RENT_CURR_AGED, B.TOT_OVER_30_AGED AS RENT_OV30_AGED, B.TOT_OVER_60_AGED AS RENT_OV60_AGED, B.TOT_OVER_90_AGED AS RENT_OV90_AGED, B.TOT_OVER_120_AGD AS RENT_OV120_AGED, B.TOT_150_AGED_AMT AS RENT_OV150_AGED, B.TOT_210_AGED_AMT+B.TOT_240_AGED_AMT+B.TOT_270_AGED_AMT+B.TOT_300_AGED_AMT+B.TOT_330_AGED_AMT+B.TOT_360_AGED_AMT+B.TOT_180_AGED_AMT AS RENT_OV180_AGED, IIf(IsNull(G.TEAM),C.TEAM,G.TEAM) AS TEAM, H.LEGAL_NAME AS CUSTOMER_NAME"
    strSQL = strSQL + " FROM (((((((ACRP1_CONTRACT AS A INNER JOIN ACRP1_SCHEDULE_TOTALS AS B ON (A.CUSTOMER_NBR = B.CUSTOMER_NBR) AND (A.CONTRACT_ID = B.CONTRACT_ID)) INNER JOIN ACRP1_BRANCH AS C ON (A.BRANCH_CO_NBR = C.BRANCH_CO_NBR) AND (A.BRANCH_NBR = C.BRANCH_NBR)) INNER JOIN ACRP1_DEALER AS D ON (C.DEALER_CO_NBR = D.DEALER_CO_NBR) AND (C.DEALER_NBR = D.DEALER_NBR)) INNER JOIN ACRP1_SOG AS E ON (D.SOG_CO_NBR = E.SOG_CO_NBR) AND (D.SOG_NBR = E.SOG_NBR)) INNER JOIN ACRP1_CONTRACT_II AS F ON (A.CUSTOMER_NBR = F.CUSTOMER_NBR) AND (A.CONTRACT_ID = F.CONTRACT_ID)) INNER JOIN ACRP1_CUSTOMER AS H ON A.CUSTOMER_NBR = H.CUSTOMER_NBR) LEFT JOIN ACRP1_CONTRACT_NAMES AS I ON (A.CUSTOMER_NBR = I.CUSTOMER_NBR) AND (A.CONTRACT_ID = I.CONTRACT_ID)) LEFT JOIN ACRP1_CUST_TEAM AS G ON A.CUSTOMER_NBR = G.CUSTOMER_NBR"
    strSQL = strSQL + " WHERE (((A.CONTRACT_STATUS)='A' Or (A.CONTRACT_STATUS)='T' AND ((A.CLOSE_BILL_IND)<>'T')) AND ((B.SCHEDULE)='B') AND ((B.SCHEDULE_TYPE)='RENT') AND ((I.NAME_TYPE)='BLTO')"
    strSQL = strSQL + " And ((A.CUSTOMER_NBR)>=" & CStr(i) & " And (A.CUSTOMER_NBR)<" & CStr(i + incr) & "))"
    adoConn.Execute strSQL
    txtStream.WriteLine CStr(i + incr)
    i = i + incr
Wend

This was the version that I changed and it didnt work..

'While i <= max
'    strSQL = "INSERT INTO CONTRACT_THOR ( CUSTOMER_NBR, CONTRACT_ID, BRANCH_CO_NBR, BRANCH_NBR, BRANCH_NAME, DEALER_NBR, DEALER_NM, SOG_NBR, SOG_NAME, CNTR_BILL_ADDR, BILLTO_NAME, ACCT_PLAN_ID, SETUP_DATE, CONT_COLL_STAT, EMPLOYEE_WORKING, NBR_DAYS_PST_DUE, CYCLE_DAY, CONTRACT_BAL, TOT_CONTRACT_BAL, RENT_NOW_DUE, RENT_CURR_AGED, RENT_OV30_AGED, RENT_OV60_AGED, RENT_OV90_AGED, RENT_OV120_AGED, RENT_OV150_AGED, RENT_OV180_AGED, TEAM, CUSTOMER_NAME )"
'    strSQL = strSQL + " SELECT A.CUSTOMER_NBR, A.CONTRACT_ID, A.BRANCH_CO_NBR, A.BRANCH_NBR, C.BRANCH_NAME, D.DEALER_NBR, D.DEALER_NM, D.SOG_NBR, E.SOG_NAME, A.CNTR_BILL_ADDR, I.LAST_NAME AS BILLTO_NAME, A.ACCT_PLAN_ID, A.SETUP_DATE, F.CONT_COLL_STAT, [b]K.EMPLOYEE_ID AS EMPLOYEE_WORKING[/b], F.RT_DAYS_PST_DUE, A.CYCLE_DAY, A.TOTAL_RENT AS CONTRACT_BAL, A.TOTAL_RENT+B.TOT_NOW_DUE AS TOT_CONTRACT_BAL, B.TOT_NOW_DUE AS RENT_NOW_DUE, B.TOT_CURRENT_AGED AS RENT_CURR_AGED, B.TOT_OVER_30_AGED AS RENT_OV30_AGED, B.TOT_OVER_60_AGED AS RENT_OV60_AGED, B.TOT_OVER_90_AGED AS RENT_OV90_AGED, B.TOT_OVER_120_AGD AS RENT_OV120_AGED, B.TOT_150_AGED_AMT AS RENT_OV150_AGED, B.TOT_210_AGED_AMT+B.TOT_240_AGED_AMT+B.TOT_270_AGED_AMT+B.TOT_300_AGED_AMT+B.TOT_330_AGED_AMT+B.TOT_360_AGED_AMT+B.TOT_180_AGED_AMT AS RENT_OV180_AGED,[b] K.TEAM AS TEAM[/b], H.LEGAL_NAME AS CUSTOMER_NAME"
'    strSQL = strSQL + " FROM (((((((ACRP1_CONTRACT AS A INNER JOIN ACRP1_SCHEDULE_TOTALS AS B ON (A.CUSTOMER_NBR = B.CUSTOMER_NBR) AND (A.CONTRACT_ID = B.CONTRACT_ID)) INNER JOIN ACRP1_BRANCH AS C ON (A.BRANCH_CO_NBR = C.BRANCH_CO_NBR) AND (A.BRANCH_NBR = C.BRANCH_NBR)) INNER JOIN ACRP1_DEALER AS D ON (C.DEALER_CO_NBR = D.DEALER_CO_NBR) AND [b](ACRP1_PAYSYS_CUST_MSTR_QR_COLLECTOR = ACRP1_EMPLOYEE_EMPL_COLL_ID) AND (A.CONTRACT_ID = ACRP1_PAYSYS_CUST_MSTR.CONTRACT_ID) AND (A.CUSTOMER_NBR = ACRP1_PAYSYS_CUST_MSTR_CUSTOMER_NBR)[/b] AND (C.DEALER_NBR = D.DEALER_NBR)) INNER JOIN ACRP1_SOG AS E ON (D.SOG_CO_NBR = E.SOG_CO_NBR) AND (D.SOG_NBR = E.SOG_NBR)) INNER JOIN ACRP1_CONTRACT_II AS F ON (A.CUSTOMER_NBR = F.CUSTOMER_NBR) AND (A.CONTRACT_ID = F.CONTRACT_ID)) INNER JOIN ACRP1_CUSTOMER AS H ON A.CUSTOMER_NBR = H.CUSTOMER_NBR) LEFT JOIN ACRP1_CONTRACT_NAMES AS I ON (A.CUSTOMER_NBR = I.CUSTOMER_NBR) AND (A.CONTRACT_ID = I.CONTRACT_ID)) LEFT JOIN ACRP1_CUST_TEAM AS G ON A.CUSTOMER_NBR = G.CUSTOMER_NBR"
'    strSQL = strSQL + " WHERE (((A.CONTRACT_STATUS)='A' Or (A.CONTRACT_STATUS)='T' AND ((A.CLOSE_BILL_IND)<>'T')) AND ((B.SCHEDULE)='B') AND ((B.SCHEDULE_TYPE)='RENT') AND ((I.NAME_TYPE)='BLTO')"
'    strSQL = strSQL + " And ((A.CUSTOMER_NBR)>=" & CStr(i) & " And (A.CUSTOMER_NBR)<" & CStr(i + incr) & "))"
'    adoConn.Execute strSQL
'    txtStream.WriteLine CStr(i + incr)
'    i = i + incr
'Wend

 

[ChewDoggie]

Have you tried pasting the query in the Access query window? Sometimes that helps solve issues.

AMACycle

 

[HarleyQuinn]

Where is the table you called K that you reference in K.EMPLOYEE etc.?

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[ChewDoggie]

The word "team" appears twice in the code:

"TEAM" and "K.team as TEAM"

HTH

AMACycle

 

[ChewDoggie]

also, sorry for the multiple posts, I see single quotes appearing on some lines that don't appear in your original sql query, as if the lines are commented out. Is this intentional?

 

[miller1975]

The lines were commented out as I was not running it...not intentional..sorry abt that..
"K that you reference in K.EMPLOYEE " K is ACRP1_EMPLOYEE

 

[DrJavaJoe]

...and it didnt work.

You still haven't told us what that means, does it not return what you expect or does it error out? If it is an error what is the error?



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[miller1975]

Gives error on the FROM clause

 

[DrJavaJoe]

What error?



Two strings walk into a bar. The first string says to the bartender: 'Bartender, I'll have a beer. u.5n$x5t?*&4ru!2[sACC~ErJ'. The second string says: 'Pardon my friend, he isn't NULL terminated'.

 

[jebenson]

Here are the table reference I can find in your new code:

ACRP1_CONTRACT AS A
ACRP1_SCHEDULE_TOTALS AS B
ACRP1_BRANCH AS C
ACRP1_DEALER AS D
ACRP1_SOG AS E
ACRP1_CONTRACT_II AS F
ACRP1_CUST_TEAM AS G
ACRP1_CUSTOMER AS H
ACRP1_CONTRACT_NAMES AS I

In your first post, you do have a table referenced as "ACRP1_EMPLOYEE AS K", but in your later post where you have the original and the changed code, neither the original nor the changed code has a reference to "ACRP1_EMPLOYEE AS K", nor to any table aliased as "K".


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[fredericofonseca]

Assuming that the first sql was posted here as a copy+paste, and that it does reflect the SQL that issued the error when I past the first post code I get the following error

syntax error in query expression '(((A.CONTRACT_STATUS)='A' Or (A.CONTRACT_STATUS)='T' AND ((A.CLOSE_BILL_IND)<>'T')) AND ((B.SCHEDULE)='B') AND ((B.SCHEDULE_TYPE)='RENT') AND ((I.NAME_TYPE)='BLTO');'

Looks obvious that the error is a wrong pairing of parenthesis. I'll leave to the original poster to COUNT them and remove/add the necessary one(s).

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[litton1]

i think u need another ) in the query..possibly

A problem with no solution is a problem viewed from the wrong angle

 

[fredericofonseca]

Hum.. don't you think it has WAY TOO MANY already?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[litton1]

lol there is 12 of ( them and 11 of ) them i think, it could b me

A problem with no solution is a problem viewed from the wrong angle

 

[HarleyQuinn]

jebenson - That was the exact point I was attempting to make in my post.

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[jebenson]

HarleyQuinn - I know, I was just trying to reinforce what you said.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson