[Teradata Database] [3706] Syntax error: expected something between the word 'groupdn' and ','.

Database
Enthusiast

[Teradata Database] [3706] Syntax error: expected something between the word 'groupdn' and ','.

Hello - I am trying to run the query below and keep getting hit with the following error message:

[Teradata Database] [3706] Syntax error: expected something between the word 'groupdn' and ','.

 

Select g.name as grpName,Substring(g.groupdn,1,Charindex(',', g.groupdn)-1) as Node1,g.as_of_time as AsOf_time

from ad_Group_6 g

where g.as_of_time='2017-01-24 00:00:00'

and g.groupdn like '%DC=abc%'

group by g.groupdn,g.name, g.as_of_tms

order by 2

 

I've done research and it was suggested in several posts that each field in the SELECT statement needs an alias, but this has not solved my issue.  Why am I receiving this error message still?


Accepted Solutions
Junior Contributor

Re: [Teradata Database] [3706] Syntax error: expected something between the word 'groupdn' and ','.

This is MS SQL Server syntax, in Teradata there's no CHARINDEX and the SUBSTRING syntax is different:

Substring(g.groupdn from 1 for position (',' in g.groupdn)-1) -- Standard SQL 

or 

Substr(g.groupdn, 1, position (',' in g.groupdn)-1) -- Teradata SQL

Shortest/easiest:

STRTOK(g.groupdn, ',', 1)

 

1 ACCEPTED SOLUTION
5 REPLIES
Junior Contributor

Re: [Teradata Database] [3706] Syntax error: expected something between the word 'groupdn' and ','.

This is MS SQL Server syntax, in Teradata there's no CHARINDEX and the SUBSTRING syntax is different:

Substring(g.groupdn from 1 for position (',' in g.groupdn)-1) -- Standard SQL 

or 

Substr(g.groupdn, 1, position (',' in g.groupdn)-1) -- Teradata SQL

Shortest/easiest:

STRTOK(g.groupdn, ',', 1)

 

Teradata Employee

Re: [Teradata Database] [3706] Syntax error: expected something between the word 'groupdn' and ','.

Mixing Teradata and ANSI syntax.

Either:

SUBSTRING( string-expr FROM expr FOR expr)

or

SUBSTR(string-expr, expr, expr)

Enthusiast

Re: [Teradata Database] [3706] Syntax error: expected something between the word 'groupdn' and ','.

Hi dnoeth,

 

All of your solutions are valid, but I absolutely love the 'easiest' one:

 

STRTOK(g.groupdn, ',', 1)

 

Being a Microsoft T-SQL guy, I've never encountered this function before - amazing and efficient!

Enthusiast

Re: [Teradata Database] [3706] Syntax error: expected something between the word 'groupdn' and ','.

I am facing a similar issue while running SQL via a BTQ embedded in a .ksh. I am able to run the same code via SQL assistant.

-***************************************************
$
*** Failure 3706 Syntax error: expected something between the beginning of
the request and '-'.
Statement# 2, Info =3

*** Exiting BTEQ...
*** RC (return code) = 8

 

Is there a solution you could suggest?

 

SyntaxEditor Code Snippet

CREATE MULTISET VOLATILE TABLE address_risk AS(
    SELECT
  ADR_ALL.cust_id  , NATION_CD_TO_SCORE AS _adr_NATIONCD 
  , ZIP_CD_TO_SCORE AS _adr_ZIPCD 
        , CAST(ADR_ALL.adr_NATION_rsk_C AS VARCHAR(2)) || CAST(ADR_ALL.adr_ZIP_rsk_C AS VARCHAR(2)) AS address_risk        , ADR_ALL.flg_elg        , ROW_NUMBER() OVER(PARTITION BY cust_id ORDER BY flg_elg DESC, adr_NATION_rsk_C DESC, adr_ZIP_rsk_C DESC, NATION_CD_TO_SCORE DESC, ZIP_CD_TO_SCORE DESC, ACCT_ID DESC)
 AS address_rank
    FROM    (
        SELECT
            ADDRESS.cust_id            , ADDRESS.ACCT_ID            , ADDRESS.SOR_ID            , CASE
                WHEN CAST(ADDRESS.adr_NATION_rsk_ACCT AS SMALLINT) > CAST(ADDRESS.adr_NATION_rsk_C AS SMALLINT) THEN ADDRESS.ACCT_NATION_CD
                ELSE ADDRESS.PARTY_NATION_CD
            END AS NATION_CD_TO_SCORE            , CASE
                WHEN CAST(ADDRESS.adr_ZIP_rsk_ACCT AS SMALLINT) > CAST(ADDRESS.adr_ZIP_rsk_C AS SMALLINT) THEN ADDRESS.ACCT_ZIP_CD
                ELSE ADDRESS.PARTY_ZIP_CD
            END AS ZIP_CD_TO_SCORE            , CASE
                WHEN CAST(ADDRESS.adr_NATION_rsk_ACCT AS SMALLINT) > CAST(ADDRESS.adr_NATION_rsk_C AS SMALLINT) THEN CAST(ADDRESS.adr_NATION_rsk_ACCT AS SMALLINT)
                ELSE CAST(ADDRESS.adr_NATION_rsk_C AS SMALLINT)
            END AS adr_NATION_rsk_C            , CASE
                WHEN CAST(ADDRESS.adr_ZIP_rsk_ACCT AS SMALLINT) > CAST(ADDRESS.adr_ZIP_rsk_C AS SMALLINT) THEN CAST(ADDRESS.adr_ZIP_rsk_ACCT AS SMALLINT)
                ELSE CAST(ADDRESS.adr_ZIP_rsk_C AS SMALLINT)
            END AS adr_ZIP_rsk_C            , ADDRESS.flg_elg
        FROM        (
            SELECT
                BASE_TBL.cust_id                , BASE_TBL.flg_elg                , BASE_TBL.ACCT_ID                , BASE_TBL.SOR_ID                , BASE_TBL.ACCT_NATION_CD                , BASE_TBL.ACCT_ZIP_CD                , BASE_TBL.PARTY_NATION_CD                , BASE_TBL.PARTY_ZIP_CD                , TRIM(CASE
                    WHEN BASE_TBL.flg_elg = 0 THEN -2
                    WHEN PTY_CNTRY_REF.NATION_CD IS NULL THEN -3 
                    ELSE PTY_CNTRY_REF.RISK_LEVEL_CD
                    END) AS adr_NATION_rsk_C                , TRIM(CASE 
                    WHEN BASE_TBL.flg_elg = 0 THEN -2
                    WHEN PTY_ZIP_REF.ZIP_CODE IS NULL THEN -3
                    ELSE PTY_ZIP_REF.RISK_IND_CD 
                    END) AS adr_ZIP_rsk_C                , TRIM(CASE
                    WHEN BASE_TBL.flg_elg = 0 THEN -2
                    WHEN ACCT_CNTRY_REF.NATION_CD IS NULL THEN -3
                    ELSE ACCT_CNTRY_REF.RISK_LEVEL_CD
                    END) AS adr_NATION_rsk_ACCT                , TRIM(CASE 
                    WHEN BASE_TBL.flg_elg = 0 THEN -2
                    WHEN ACCT_ZIP_REF.ZIP_CODE IS NULL THEN -3
                    ELSE ACCT_ZIP_REF.RISK_IND_CD 
                    END) AS adr_ZIP_rsk_ACCT
            FROM            (
                SELECT
                    cust_id                    , ACCT_ID                    , SOR_ID                    , ACCT_NATION_CD                    , SUBSTR(ACCT_ZIP_CD,1,5) AS ACCT_ZIP_CD 
                    , PARTY_NATION_CD                    , SUBSTR(PARTY_ZIP_CD,1,5) AS PARTY_ZIP_CD 
                    , CASE
                        WHEN REL_TYPE_TIER_NUM IN (1,2) AND ACCT_OPEN_IND = 'Y' THEN 1 
                        ELSE 0
                    END AS flg_elg
                FROM C_acct_base 
                GROUP BY 1,2,3,4,5,6,7,8            ) BASE_TBL
            LEFT JOIN reference_table_NATION_DEV PTY_CNTRY_REF
                ON PTY_CNTRY_REF.NATION_CD = BASE_TBL.PARTY_NATION_CD
                OR PTY_CNTRY_REF.NATION3_CD = BASE_TBL.PARTY_NATION_CD
                OR PTY_CNTRY_REF.NATION_NAME = BASE_TBL.PARTY_NATION_CD
            LEFT JOIN reference_table_ZIP_CODE_DEV PTY_ZIP_REF
                ON PTY_ZIP_REF.ZIP_CODE = BASE_TBL.PARTY_ZIP_CD
            LEFT JOIN reference_table_NATION_DEV ACCT_CNTRY_REF
                ON ACCT_CNTRY_REF.NATION_CD = BASE_TBL.ACCT_NATION_CD
                OR ACCT_CNTRY_REF.NATION3_CD = BASE_TBL.ACCT_NATION_CD
                OR ACCT_CNTRY_REF.NATION_NAME = BASE_TBL.ACCT_NATION_CD
            LEFT JOIN reference_table_ZIP_CODE_DEV ACCT_ZIP_REF
                ON ACCT_ZIP_REF.ZIP_CODE = BASE_TBL.ACCT_ZIP_CD        ) ADDRESS
        GROUP BY 1,2,3,4,5,6,7,8    ) ADR_ALL
    QUALIFY address_rank = 1) WITH DATA
PRIMARY INDEX(cust_id)ON COMMIT PRESERVE ROWS;

Junior Apprentice

Re: [Teradata Database] [3706] Syntax error: expected something between the word 'groupdn' and ','.

Hi,

 

I don't think it is the code itself which is wrong. I think the problem is that you're trying to code a single line comment but only starting it with a single '-' character, it needs to be two characters '--'.

 

So "-**************************************************" should be "--**************************************************"

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com