Error Code 3785

General

Error Code 3785

Hello

I'm currently writing a query including an inner join, however when I run it Teradata is throwing back error code 3785 'expected ANY or ALL is missing.'

This error code however is not anything my colleagues or I have experienced before and the help function/google are not being very helpful!

My syntax is:

"SELECT

TRIM (prodvm.customer_letter.Trading_Code||prodvm.customer_letter.Account_Number) AS Customer_Number,
prodvm.customer_letter.Letter_Type_Code,
prodvm.customer_letter.Parcel_Number,
prodvm.customer_letter.Week_Letter_Processed,
prodvm.balance_adjustment.adjustment_reason_code

FROM

prodvm.customer_letter

INNER JOIN

prodvm.balance_adjustment ON (TRIM (prodvm.customer_letter.Trading_Code||prodvm.customer_letter.Account_Number)) = (TRIM (prodvm.balance_adjustment.Trading_Code||prodvm.balance_adjustment.Account_Number))

WHERE
prodvm.balance_adjustment.adjustment_reason_code = ('B', 'C')
AND
prodvm.customer_letter.week_letter_processed BETWEEN '201027' AND '201149'"

If anyone has any idea how to overcome this error, you're help will be greatly appreciated.

Thanks,
Annie.
1 REPLY
Junior Contributor

Re: Error Code 3785

Hi Annie,
your first where-condition is wrong:
prodvm.balance_adjustment.adjustment_reason_code = ('B', 'C')

The parser expects:
prodvm.balance_adjustment.adjustment_reason_code = ANY ('B', 'C')

which of course is the same as
prodvm.balance_adjustment.adjustment_reason_code IN ('B', 'C')

Btw, the join-condition is quite bad, it results in unusable statistics and always a redistribution.
Better rewrite it as:
(prodvm.customer_letter.Trading_Code=prodvm.balance_adjustment.Trading_Code)
AND
(prodvm.customer_letter.Account_Number=prodvm.balance_adjustment.Account_Number)

Dieter