Troubleshooting Syntax Error

Toad Data Point
1

Hi all,

I’m receiving the following error when attempting to automate a .tim import template

Steps taken:

  1. Wrote and tested SQL query against data source, it works

  2. Created an import template to query one database (Teradata database) and import the results into a SQL Server database. When I choose to run the import now, it works totally fine.

  3. When I choose to send to automation (or create a new automation script and use the import wizard to load up the .tim file that I created in step 2), the automation run fails with the following error:

Import_Activity - [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error, expected something like an ‘EXCEPT’ keyword or an ‘UNION’ keyword or a ‘MINUS’ keyword between ‘)’ and ‘;’. (“Embedded Query” into “[Clarity - Tapestry].dbo.WFP_PRODUCTION”)

To reiterate: the sql that drives the import step works in TOAD Data Point when I run from the sql editor, the import .tim works when I run it manually. However, it does not work when run via automation.

2

Hi jlegrand, I could not reproduce your problem with my SQL. Can you send me example of your SQL query? Please try checking your connection settings in the automation script and try removing any comments from your SQL query. Regards, Petr

3

Try taking the semicolon out. Should work without it. Try to make the import SQL just the select for the import and not multiple commands followed by a select if you can. If you need to set things up before running the import try to do that in a separate job step that executes the other sql before running the import step.

4

Here’s the code. I’ve removed company specific segments, have run this against toad, and still receive the same error.

Note: the code works when run from sql editor. When I create the import statement and choose “import data now” it works. Once the .tim is saved and I load it into an automation script, the script itself fails.

select

ap.status_date as PROD_DT

,‘HI’ as REGION

,case

when ven.vendor_name like ‘%Hawaii%’ then ‘Internal’

else ‘External’

end as INT_EXT

,lob.lob_name as LOB

,case when ap.orig_adjst_clm_id is null and ap.orig_rev_clm_id is null then ‘Y’ else ‘N’ end as FIRST_PASS

,case when d_aa.claim_id is not null then ‘Y’ else ‘N’ end as AA

,count (distinct clm.internal_id) as CLM_CT

from ap_claim ap

inner join clm_map clm on ap.claim_id = clm.cid

left outer join clarity_vendor ven on ap.vendor_id = ven.vendor_id

left outer join clarity_lob lob on ap.clm_lob_id = lob.lob_id

left outer join

(SELECT distinct CLM.CLAIM_ID

FROM AP_CLAIM CLM

WHERE CLM.CLAIM_ID NOT IN

(SELECT CLAIM_ID

FROM AP_CLAIM_CHANGE_HX

WHERE (

(ACTION_C = ‘18100’ AND CHANGE_HX_NEW_VAL = ‘2’)

OR CHANGE_HX_USER_ID NOT IN (‘130HI50101’,‘130HI50100’)))

AND (CLM.STATUS_C IN (‘3’,‘4’) AND CLM.ORIG_REV_CLM_ID IS NULL AND CLM.ORIG_ADJST_CLM_ID IS NULL)

) D_AA on ap.claim_id = d_aa.claim_id

where

ap.status_c in (3,4,5)

group by 1,2,3,4,5,6

5

I don’t see anything wrong with your sql and there is no semicolon in it, so the error message does not make sense. It might be something specific to a Terradata connection that automation is not handling correctly. You need Toad support analyst to weigh in on this one. I have never used a Terradata connection.

6

I just noticed that the Toad editor no longer likes blank rows within SQL statements. Try removing all of the blank rows in your SQL.

Does anyone know if this is a setting somewhere? Not allowing blank rows is very annoying…

For example:

Select *
From tableABC; – Works great

But…

Select *

From tblABC; – Throws a syntax error

7

I would like to try and reproduce this exact error. Can you take the SQL above and execute in the editor? After that generate a support bundle and add here as attachment.

In our later releases of TDP, if you have SQL Editor focused when generating support bundle we will create ddl for the columns used in the SQL. It puts it in the SQLAnalyze.log file in the app dir,

If you can get me that file generated I can reproduce the error.