Cannot have aggregate function in JOIN operation

[norrisinc]

Ok, i'm stumped.... This works:

SELECT TRANSDATE, B.TRANDESC, A.CARDID, D.FIRSTNAME, D.LASTNAME
FROM (CARDTRANSHISTORY AS A
LEFT JOIN TRANSACTIONCODES AS B ON A.TRANSCODE=B.TRANCODE)
LEFT JOIN CARDINFO AS D ON A.CARDID="CARD#=" & VAL(D.CARDID)

This doesn't:

SELECT A.TransDate, B.TranDesc, A.IqxId, C.Door_Description,
A.Reader, A.CardID, A.RECORDID, D.LASTNAME, D.FIRSTNAME
FROM ((CardTransHistory AS A
LEFT JOIN TransactionCodes AS B ON A.TransCode = B.TranCode)
LEFT Join DoorConfig AS C On A.IqxId = C.Iqx_Id)
LEFT JOIN CARDINFO AS D ON A.CARDID = "Card#=" & var(D.CARDID)
WHERE A.Reader = "Reader=A" and C.door_number mod 2 = 1

UNION SELECT A.TransDate, B.TranDesc, A.IqxId, C.Door_Description,
A.Reader, A.CardID, A.RECORDID, D.LASTNAME, D.FIRSTNAME
FROM ((CardTransHistory AS A
LEFT JOIN TransactionCodes AS B ON A.TransCode =B.TranCode)
LEFT Join DoorConfig AS C On A.IqxId =C.Iqx_Id)
LEFT JOIN CARDINFO AS D ON A.CARDID = "Card#=" & var(D.CARDID)
WHERE A.Reader = "Reader=B" and C.door_number mod 2 = 0

ORDER BY A.RecordId;
UNION SELECT A.TransDate, B.TranDesc, A.IqxId, " " as
Door_Description, A.Reader, A.CardID, A.RECORDID, D.LASTNAME,
D.FIRSTNAME
FROM (CardTransHistory AS A
LEFT JOIN TransactionCodes AS B ON A.TransCode = B.TranCode)
LEFT JOIN CARDINFO AS D ON A.CARDID = "Card#=" & var(D.CARDID)
WHERE A.Reader <> "Reader=A" and A.Reader <> "Reader=B";

I get an error: Cannot have aggregate function in JOIN operation

If i remove the Var(xyz) from the Left join it will run, but doesn't
give the correct information because the D.CARDID has leading 0's. I
might accept this, but the above query watered down with the same VAR
in the JOIN works. Any ideas?

 

[Guest]

I did not analyze all of your query but a solution seems to be to have
Var(xyz) in a query preseeding this one. Use the query instead of the table
directly.