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:
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
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.
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)