Teradata max(date) from a table

I use a few Teradata tables that are updated once a month, and the max date is always the first day of the previous month.
What is the syntax to select the max(load_dt) in Teradata?

I am using this code below and getting this error:

3149: TDWM Filter violation for query Request: For Rule Name 'HighStepRowsProdJoinLimit'

ACCT.WAE_PRD_DT in (select distinct max(ACCT.WAE_PRD_DT) from WAE_ACCT)
AND summ.wae_prd_dt  in (select distinct max(summ.wae_prd_dt) from WAE_ACCT_SUMM)
AND CXA.EXPIR_DT in (select distinct max(CXA.EXPIR_DT) from WAE_CLIENT_X_ACCT)
and bdw.WAE_PRD_DT in (select distinct max(bdw.WAE_PRD_DT) from BDW_ACCT)

Thank you.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
You did not post your complete query, so I am not sure about your tables. But you have same column name both in where clause and the subquery.
Instead, try changing like this. Also DISTINCT is not required.
ACCT.WAE_PRD_DT in (select max(wa.WAE_PRD_DT) from WAE_ACCT wa)
AND summ.wae_prd_dt  in (select max(was.wae_prd_dt) from WAE_ACCT_SUMM was)
AND CXA.EXPIR_DT in (select max(wca.EXPIR_DT) from WAE_CLIENT_X_ACCT wca)
and bdw.WAE_PRD_DT in (select max(ba.WAE_PRD_DT) from BDW_ACCT ba)

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
btw: there is no need for IN() really as each of those subqueries can only return a single value
ACCT.WAE_PRD_DT = (select max(wa.WAE_PRD_DT) from WAE_ACCT wa)
AND summ.wae_prd_dt  = (select max(was.wae_prd_dt) from WAE_ACCT_SUMM was)
AND CXA.EXPIR_DT = (select max(wca.EXPIR_DT) from WAE_CLIENT_X_ACCT wca)
AND bdw.WAE_PRD_DT = (select max(ba.WAE_PRD_DT) from BDW_ACCT ba)

Open in new window

and, do take care with overuse of distinct - it can slow down queries if used badly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Enterprise Software

From novice to tech pro — start learning today.