SQL Exist in either TableA or TableB

I am attempting to create a script which needs a clause to check of a column value in Table 3 exists in Table 1 or Table 2

SELECT *      
FROM [ZERO_BALANCE].[DBO].[dat_acct_codes_REJECTIONS] ACR
WHERE [REJECTIONREASON] = 'MISSING LINKING ACCOUNT'
AND 
EXISTS 
(SELECT 1 FROM ref_PX WHERE CAST(PX AS VARCHAR(MAX)) = CAST(ACR.[Code] AS VARCHAR(MAX))
OR
(SELECT 1 FROM ref_DX WHERE CAST(DX AS VARCHAR(MAX)) = CAST(ACR.[Code] AS VARCHAR(MAX)))

Msg 4145, Level 15, State 1, Line 44 An expression of non-boolean type specified in a context where a condition is expected, near ‘)’.

Answer

Problems with your query:

• Missing parentheses on the CAST() function (as commented by Dale Burrell)

• You need to repeat the EXISTS keyword for each condition

• Beware of operator prescedence: OR has lower prescendence than AND, so, as far as I understand your query, you ORed conditions should be surrounded with parentheses.

Proper formatting helps spotting this kind of stuff.

SELECT *      
FROM [ZERO_BALANCE].[DBO].[dat_acct_codes_REJECTIONS] ACR
WHERE 
    [REJECTIONREASON] = 'MISSING LINKING ACCOUNT'
    AND (
        EXISTS (
            SELECT 1 
            FROM ref_PX 
            WHERE CAST(PX AS VARCHAR(MAX)) = CAST(ACR.[Code] AS VARCHAR(MAX))
        )
        OR EXISTS (
            SELECT 1 
            FROM ref_DX 
            WHERE CAST(DX AS VARCHAR(MAX)) = CAST(ACR.[Code] AS VARCHAR(MAX))
        )
    )