Failed Query! Not sure why - 3706 Syntax Error?

Database

Failed Query! Not sure why - 3706 Syntax Error?

I'm trying to query our database by running the following SQL statement (see below) in Teradata SQL Assistant, but I keep recieving this error message - "Query Failed. [3706] Syntax Error. Data Type "cust_id" does not match a Defined Type name" I've run this same SQL statement multiple times in the past, not sure why I'm having problems any suggestions for troubleshooting?...

SELECT DISTINCT

      concat(a.cust_id,a.sell_to_loc_id),

      a.sell_to_loc_name,

      c.loc_street_txt,

      c.city_name,

      c.state_cd,

      c.ctry_cd,

      c.zip_cd

FROM

      edward.active_cust_plate a JOIN edward.cust_loc c

      ON    a.cust_srce_cd = c.cust_srce_cd

            and a.cust_id = c.cust_id

            and a.sell_to_loc_id = c.cust_loc_id

      JOIN edward.account_grps g

      ON    a.cust_id = g.cust_id

WHERE

      (g.acct_grouping_lvl_cd = 'C'

      and a.cust_srce_cd = 'C')

AND

      g.acct_grp_cd IN ('BEALL''S DEPT STORES INC', 'BELK', 'BOB STORES CORP', 'BON-TON DEPT STORES INC', 'BOSCOVS DEPT STORE INC', 'DILLARD''S', 'FRED MEYER STORES INC', 'JC PENNEY CORP INC', 'KOHLS DEPT STORE', 'MACY''S HEADQUARTERS', 'NORDSTROM', 'SEARS ROEBUCKCO', 'STAGE STORES INC')

AND

      CASE c.loc_clsd_dt

      WHEN '1582-01-01' THEN NULL

      ELSE c.loc_clsd_dt

      END > '2010/04/22'

AND   a.sap_sell_to_loc_type_cd = 'Z02'

AND   a.cust_mail_addr_name_1 not like '%CLOSED%'

AND   a.cust_id != '91450'

AND   c.state_cd != 'PR'

AND   c.state_cd != 'GU'

AND   a.sell_to_loc_name not in ('PARISIAN INC','GOODY''S','CHUGIAK-EAGLE RIVER','SPECIALTY RETAILERS LP','NORTH BELLINGHAM','SUMMIT SIERRA','EASTLAND','GOODY''S SOUTH','GOODY''S / SSI','SSI')

4 REPLIES
Senior Apprentice

Re: Failed Query! Not sure why - 3706 Syntax Error?

CONCAT is no Teradata but ODBC function which is rewritten by the ODBC driver:

In SQL Assistant there's Tools - Options - Query - Allow use of ODBC SQL extensions in query

It's recommended to uncheck it and use only valid Teradata functions:

concat(a.cust_id,a.sell_to_loc_id), -> a.cust_id || a.sell_to_loc_id

Dieter

Re: Failed Query! Not sure why - 3706 Syntax Error?

Thank you! That's great to keep in mind. Is there any other areas of the SQL statement that should be re-written?

Re: Failed Query! Not sure why - 3706 Syntax Error?

Hi Every one,

1)In general what should be the date format in DDL and what should be date format in scripts. 

2)I am using cast function but its saying rdms error 3706

Senior Apprentice

Re: Failed Query! Not sure why - 3706 Syntax Error?

Hi Sandeep,

please post new questions as new topic.

#1: ISO & ANSI & Standard SQL format: yyyy-mm-dd, literals as DATE '2015-10-15'

#2: This is a very generic error, you need to show the actual code & error message