CODEWITHSUNDEEP
sqlsubqueryteradata
Amber Williams
Amber Williams

Reputation: 24

Trying to understand how WHERE IN in a subquery works in Teradata SQL?

I'm trying to build a sub-query with a list in the where clause, I have tried several variations and I think the problem is with the way I'm structuring the WHERE IN. Help is grealy appreciated!!

SELECT  a.ACCT_SK, 
        a.BTN,
        a.PRODUCT_SET,
        MAX(b.ORD_CREATD_DT)

     FROM MM.MEC_ACCT_ATTR a, CDI_CRM.ORD_MSTR b

     WHERE a.ACCT_SK=b.ACCT_SK AND a.BTN=b.BTN

        (SELECT b.ACCT_SK, b.ORD_CREATD_DT
        FROM  CDI_CRM.ORD_MSTR b
        WHERE b.ACCT_SK IN ('44347714',
        '44023302',
        '43604964'));

SELECT Failed. 3706: (-3706)Syntax error: expected something between '(' and the 'SELECT' keyword

The desired output is a table with Product set for 50 ACCT_SKs with the most recent order date matched on ACCT_SK and BTN.

Upvotes: 0

Views: 1204

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270633

Sample data and desired results would really help. Your query doesn't make much sense, but I suspect you want:

SELECT a.ACCT_SK, a.BTN, a.PRODUCT_SET,
       MAX(o.ORD_CREATD_DT)
FROM MM.MEC_ACCT_ATTR a JOIN
     CDI_CRM.ORD_MSTR o
     ON a.ACCT_SK = o.ACCT_SK AND a.BTN = o.BTN
WHERE a.ACCT_SK IN ('44347714', '44023302', '43604964')
GROUP BY a.ACCT_SK, a.BTN, a.PRODUCT_SET;

This returns the columns you want for the three specified accounts.

Notes:

  • Always use proper, explicit, standard JOIN syntax. Never use commas in the FROM clause.
  • Your subquery simply makes no sense. It is not connected to anything else in the query.
  • You are using an aggregation function (MAX()) so your query is an aggregation query and needs a GROUP BY.
  • Use meaningful table aliases. a makes sense for an accounts table, but b does not make sense for an orders table.

Upvotes: 1

Related Questions