Syntax Error: WHERE "a"."Field1" IN("b"."Test1", "b"."Test2")

Database

Syntax Error: WHERE "a"."Field1" IN("b"."Test1", "b"."Test2")

I have a query that cross joins a table of information against a table of dynamic dates that I create on the fly.

For some reason, when using IN, I cannot list multiple fields.  It will run and not error if I only provide one list in the where clause:

WHERE

("a"."SITE_ID" IN ("b"."Var1"))

It errors out: >[Error] Script lines: 1-97 -------------------------

 [Teradata Database] [TeraJDBC 15.10.00.09] [Error 3706] [SQLState 42000] Syntax error: expected something between the word 'Var1' and ','.

For:

WHERE

("a"."SITE_ID" IN ("b"."Var1","b"."Var5"))

How do I create a list of Fields within an IN statement then?  Here is my full SQL for reference:

SELECT

"a"."SITE_ID" 

FROM

"PRD_EDW_APEX_SEM"."SERVICE_CODE_V" "a" 

CROSS JOIN (SELECT

CAST (0 AS INTEGER) AS "Offset",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 1) 

) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 1)) AS DATE format 'yyyy') 

AS "Var1",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 5) 

) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 5)) AS DATE format 'yyyy') 

AS "Var5",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 6) 

) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 6)) AS DATE format 'yyyy') 

AS "Var6",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 7) 

) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 7)) AS DATE format 'yyyy') 

AS "Var7",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 11 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 11)) AS DATE format 'yyyy') 

AS "Var11",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 12 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 12)) AS DATE format 'yyyy') 

AS "Var12",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 13 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 13)) AS DATE format 'yyyy') 

AS "Var13",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 14 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 14)) AS DATE format 'yyyy') 

AS "Var14",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 15 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 15)) AS DATE format 'yyyy') 

AS "Var15",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 16 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 16)) AS DATE format 'yyyy') 

AS "Var16",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 23 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 23)) AS DATE format 'yyyy') 

AS "Var23",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 24 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 24)) AS DATE format 'yyyy') 

AS "Var24",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 25 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 25)) AS DATE format 'yyyy') 

AS "Var25",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 35 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 35)) AS DATE format 'yyyy') 

AS "Var35",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 36 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 36)) AS DATE format 'yyyy') 

AS "Var36",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 37 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 37)) AS DATE format 'yyyy') 

AS "Var37",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 47 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 47)) AS DATE format 'yyyy') 

AS "Var47",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 48 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 48)) AS DATE format 'yyyy') 

AS "Var48",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 49 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 49)) AS DATE format 'yyyy') 

AS "Var49",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 59 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 59)) AS DATE format 'yyyy') 

AS "Var59",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 60 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 60)) AS DATE format 'yyyy') 

AS "Var60",

'CL: '||CAST (ADD_MONTHS(CURRENT_DATE, -1 * (Offset + 61 

)) AS DATE format 'mmm')||' '||CAST (ADD_MONTHS( 

CURRENT_DATE, -1 * (Offset + 61)) AS DATE format 'yyyy') 

AS "Var61") "b" 

WHERE

("a"."SITE_ID" IN ("b"."Var1",

"b"."Var5"))


3 REPLIES

Re: Syntax Error: WHERE "a"."Field1" IN("b"."Test1", "b"."Test2")

Hi.

Replace "CROSS JOIN" with a comma "," and change the IN clause to:

WHERE a.SITE_ID = b.Var1 OR a.SITE_ID = b.Var5

Cheers.

Carlos.

Re: Syntax Error: WHERE "a"."Field1" IN("b"."Test1", "b"."Test2")

So is this just a limitation of IN within Teradata?  The OR solution works but the real query I'm writing has multiple IN statements already mixed with a bunch of OR and AND, so I was trying to keep the clutter down using IN.

I suppose I could just feed these values through a SP or something instead of trying to use the table.  Other thought I had was creating a vertical dates table instead of horizontal and using a subquery against it.  Any other thoughts?

Re: Syntax Error: WHERE "a"."Field1" IN("b"."Test1", "b"."Test2")

You can turn the "b" SELECT into a subquery and then use the IN clause. You could spare the rest of the "b" columns if you only need Var1 and Var5 and your select list is only a.SITE_ID.
The IN "limitation" is not such limitation. It's just how SQL works. "It's written in the books".
Cheers.
Carlos.