Syntax Error in Alteryx - SQL Runs in Teradata SQL Assistant

[rwsarisky]

When I run the below SQL using the Input Data Tool, I am getting an error message in regards to my syntax.  However, the below statement runs fine in Teradata SQL Assistant.  Does anyone know how I can get this SQL to run in Alteryx?

SQL Statement:

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 7777 StartFragment: 314 EndFragment: 7745 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

SELECT    AL1.TEAM_CD, AL1.BILL_CUST_NM, AL1.BILL_CUST, AL1.BILL_CUST_NR, AL1.FRT_BILL_NR, AL1.AUDITOR_CD, AL1.USER_ID, AL1.PROCESS_DT, AL1.TRNSACT_CD, AL1.TRNSACT_AMT, AL1.WB_DT, AL1.WB_NR, AL1.STCC, AL1.ORGN_CITY, AL1.ORGN_ST, AL1.DEST_CITY, AL1.DEST_ST, AL1.BANK_CD, AL1.BANK_SN, AL1.TRNSACT_CATG, AL1.REC_TYPE, AL1.DB_STATUS_CD, AL1.EQ_INIT, AL1.EQ_NR
FROM REVENUE_PVIEW.AR_TRANSACTION
AL1 WHERE    ((AL1.FRT_BILL_NR IN (SELECT    AL2.FRT_BILL_NR 
FROM    REVENUE_PVIEW.AR_TRANSACTION  
AL2 WHERE    ((AL2.TEAM_CD = 'A' 
    AND AL2.FRT_BILL_DT >= '2018-01-01' 
    AND AL2.TRNSACT_CATG = 'PY'))) 
    AND AL1.PROCESS_DT >= '2012-01-01'))ORDER BY 5,9

Error in Alteryx:

Input Data (1) Error SQLPrepare: [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between the word 'TEAM_CD¶, AL1' and '.'.

[NicoleJohnson · Accepted solution]

I've had similar issues with some of my own SQL queries relating to certain types of whitespace/characters - in your example below under the "Error in Alteryx", looks like you might have a carriage return between TEAM_CD and the comma? Might try removing that, along with any extra tabs & duplicate whitespace, etc. Try taking a look at your query in something like Notepad++ (turn on the "Show all characters" option) and then eliminate any unnecessary whitespace/characters from your SQL statement.

Let us know if that works!

Cheers,

NJ

[rwsarisky]

Nicole,

Thank you for the advice - I went through and reformatted, but I am still getting the same error in Alteryx.  Any other ideas?

Reformatted SQL Statement:

Select AL1.TEAM_CD, AL1.AUDITOR_CD, AL1.USER_ID, AL1.BILL_CUST_NM, AL1.BILL_CUST, AL1.BILL_CUST_NR, AL1.FRT_BILL_NR, Case When AL1.TRNSACT_CATG = 'AC' Then AL1.TRNSACT_AMT End As ACCRUAL, Case When AL1.TRNSACT_CATG = 'AC' Then AL1.FRT_BILL_DT End As ACCRUAL_DT, AL1.FRT_BILL_DT, AL1.PROCESS_DT, AL1.TRNSACT_CD, AL1.TRNSACT_AMT, AL1.WB_DT, AL1.WB_NR, AL1.EQ_INIT, AL1.EQ_NR, AL1.STCC, AL1.ORGN_CITY, AL1.ORGN_ST, AL1.DEST_CITY, AL1.DEST_ST, AL1.BANK_CD, AL1.BANK_SN, AL1.TRNSACT_CATG, AL1.REC_TYPE, AL1.DB_STATUS_CD, Case When AL1.TRNSACT_CATG = 'PY' Then AL1.TRNSACT_AMT End As PAYMENT, Case When AL1.TRNSACT_CATG = 'PY' Then AL1.FRT_BILL_DT End As PAYMENT_DT From REVENUE_PVIEW.AR_TRANSACTION AL1 Where AL1.FRT_BILL_NR In (Select AL2.FRT_BILL_NR From REVENUE_PVIEW.AR_TRANSACTION AL2 Where AL2.TEAM_CD = 'A' And AL2.FRT_BILL_DT >= '2018-01-01' And AL2.TRNSACT_CATG = 'PY') And AL1.PROCESS_DT >= '2012-01-01'

[rwsarisky]

@NicoleJohnson

I stand corrected - I just took the reformatted statement from SQL Assistant again and re-input into Alteryx and it worked the second time around.  I don't know what happened there...we can say "user error" HAHA. Thank you for you help!