3707 Error; expected like a name or a unicode btwn the word 'DWELL_TYP_HSE' and 'MATCHING DWELL'

Teradata Debugger

3707 Error; expected like a name or a unicode btwn the word 'DWELL_TYP_HSE' and 'MATCHING DWELL'

Sorry if my question is repeated couldnt tell if the post went through. Keep getting a sequence of 3707 errors with my query.

 

SyntaxEditor Code Snippet

/************************************************THIS SECTION PULL ALL SUBSCRIBERS BY DWELL TYPE, STRIPPING KEYWORDS FROM ADDRESS LINE ONE AND TWO*********************************************************************/
WITH HBB_ATL AS (SELECT DISTINCT SYS_HSE--- ATLANTA, PRIN_HSE, AGNT_HSE, SBB.SUB_ACCT_NO_SBB, SBB.RES_NAME_SBB, HSE_KEY_HSE, DWELL_TYP_HSE, HSE_STAT_HSE, ADDR1_HSE, ADDR1_LOT_HSE, RES_ADDR_2_HSE, REGEXP_REPLACE(ADDR1_HSE,'OFC 10|OFC 9|OFC 66|OFC 320|OFC 330|OFC 1|OFC 2|OFC 3|OFC 4|OFC 5|OFC 6|OFC 7|OFC 8|OFC A|BSMT M OFC 1|OFC 2|OFC 1|HOME|OFC|RM TELEWORKE|
|REAR OFFICE| MDTA BOX| MAIN SEP|LOWR LEVEL1|BLDG ITNESS|BLDG EDUCAT|OFC 2ND MODEM|3RD MODE|2ND MODE|2ND ACCT|REAR|FRNT|2ND|SPC SHRD|TRLR 2|TRLR 1|TRLR', NULL) AS ADDR1_HSE_STRP
, REGEXP_REPLACE(ADDR1_LOT_HSE,'OFC 10|OFC 9|OFC 66|OFC 320|OFC 330|OFC 1|OFC 2|OFC 3|OFC 4|OFC 5|OFC 6|OFC 7|OFC 8|OFC A|BSMT M OFC 1|OFC 2|OFC 1|HOME|OFC|RM TELEWORKE|
|REAR OFFICE| MDTA BOX| MAIN SEP|LOWR LEVEL1|BLDG ITNESS|BLDG EDUCAT|OFC 2ND MODEM|3RD MODE|2ND MODE|2ND ACCT|REAR|FRNT|2ND|SPC SHRD|TRLR 2|TRLR 1|TRLR', NULL) AS ADDR1_LOT_HSE_STRP
, REGEXP_REPLACE(REGEXP_REPLACE(RES_ADDR_2_HSE,'HOME OFFICE 1|HOME OFFICE 2|HOME OFFICE 3|HOME OFFICE|OFFICE|OFC 1|OFC 2|OFC|HOME OFFIE|HOME|VCSA ACCOUNT|UPPR|HSI ACCOUNT|
                                |HOMOE|BUSINESS|HME|EQUIPMENT ACCOUNT|VBSA ACCOUNT|OFFFICE|OFFCE|OFFCE1|HSI ACCOUNT|ACCOUNT HIERARCHY|
                                |DO NOT USE|TELCO ROOM|EQUIPMENT ACCT|SALES|LEASING|HSI 1|HSI DUPLICATE 3|REAR|DO NOT USE 4|
                                |DO NOT USE 2|DO NOT USE 1|- DO NOT USE|DO NOT USE|CLUBHOUSE|- DUPLICATE|2ND ACCT|OFFICE|OFICE|OFFUCE', NULL)
,REGEXP_REPLACE(RES_ADDR_2_HSE,'|OFFIVE|OFIFCE|OFFI|OFF|- TLWK|HSI ACCT|VCAE ACCOUNT|VCAE ACCT|TELCO ROOM|TELCO CLOSET|TELCO|WIFI ACCOUNT|WIFI ACCT|WIFI|
                                |THEATRE ROOM|THEATER', NULL), NULL) AS RES_ADDR_2_HSE_STRP
, RES_CITY_HSE, RES_STATE_HSE, SUBSTR(POSTAL_CDE_HSE,0,5) as POSTAL_CDE_HSE
FROM NDW_LOCATION_JRNL_VIEWS.HSE_BASE

JOIN NDW_ACCOUNTS_JRNL_VIEWS.SBB_BASE SBB
    ON SBB.SYS_SBB = SYS_HSE
    AND SBB.PRIN_SBB = PRIN_HSE
    AND SBB.AGNT_SBB = AGNT_HSE
    AND SBB.HSE_KEY_SBB = HSE_KEY_HSE
  AND SBB.EXT_STAT_SBB = ' '
  WHERE DWELL_TYP_HSE IN('BL','BM','CL','CM'))

/****************************************************************************THIS SECTION WILL PULL ANY MATCHING LOCATIONS BASED ADDRESS SEARCH********************************************************************/
, MATCH_ATL AS (SELECT HBB.SYS_HSE, HBB.PRIN_HSE, HBB.AGNT_HSE, HBB.SUB_ACCT_NO_SBB, HBB.RES_NAME_SBB,    HBB.HSE_KEY_HSE,    HBB.DWELL_TYP_HSE,    HBB.HSE_STAT_HSE,    HBB.ADDR1_HSE,    HBB.ADDR1_LOT_HSE,    HBB.RES_ADDR_2_HSE,    HBB.RES_CITY_HSE,    HBB.RES_STATE_HSE,    HBB.POSTAL_CDE_HSE, ADDR1_HSE_STRP, ADDR1_LOT_HSE_STRP, RES_ADDR_2_HSE_STRP, OTH.DWELL_TYP_HSE 'MATCHING DWELL', OTH.HSE_KEY_HSE 'MATCHING HOUSE', CASE WHEN OTH.DWELL_TYP_HSE IN ('AS','BT','CT','DT','YS')THEN 'Y' ELSE ' ' END AS 'GOVERNMENT', CASE WHEN OTH.DWELL_TYP_HSE IN ('BO','CO') THEN 'Y' ELSE ' ' END AS 'SCHOOL', CASE WHEN OTH.DWELL_TYP_HSE IN ('C4','C5','C6','C7','CF','DF','BF') THEN 'Y' ELSE ' 'END AS 'HOSPITALITY', CASE WHEN OTH.DWELL_TYP_HSE IN ('WA','WB','WC','WD')THEN 'Y' ELSE ' ' END AS 'COMCAST', CASE WHEN OTH.DWELL_TYP_HSE IN ('BL','BM','BP','BY','ZA','ZC','ZE'                    , 'ZW','C2','C3','CA','CC','CE','CG','CH','CJ','CL','CM','CP','CQ','CV'                    , 'HA','HB','DA','DH','DL','DM','LL','LN') THEN 'Y' ELSE ' ' END AS 'COMMERCIAL', CASE WHEN OTH.DWELL_TYP_HSE IN ('AA','AU','BH','BW','HC','HD','NB'                    ,'NC','ND','NI','NJ','NK','NR','ZB','ZD','ZF','ZZ','YA','YB','YC','YD'                    ,'YI','YJ','YK','YR','YX','NN', 'AX') THEN 'Y' ELSE ' ' END AS 'RESIDENTIAL', CASE WHEN OTH.DWELL_TYP_HSE IN ('XA','XB','XC','XD','XI','XJ','XK','XR','XS','XX','XZ') THEN 'Y' ELSE  ' ' END AS 'BULK MASTER'

FROM HBB_ATL HBB
    LEFT JOIN OPS$WOM.HSE_BASE  OTH
    ON HBB.SYS_HSE = OTH.SYS_HSE
        AND (TRIM(HBB.ADDR1_HSE_STRP)
                    || TRIM(HBB.ADDR1_LOT_HSE_STRP)
                    || TRIM(HBB.RES_ADDR_2_HSE_STRP)
                    || HBB.POSTAL_CDE_HSE) = (TRIM(OTH.ADDR1_HSE)
                                                || TRIM(OTH.ADDR1_LOT_HSE)
                                                || TRIM(OTH.RES_ADDR_2_HSE)
                                                || SUBSTR(OTH.POSTAL_CDE_HSE,0,5))
        AND OTH.HSE_KEY_HSE <> HBB.HSE_KEY_HSE)

/****************************************************** THIS SECTION WILL APPLY HBB IDENTIFICATION LOGIC****************************************/
SELECT SYS_HSE,     SUB_ACCT_NO_SBB,     RES_NAME_SBB,    HSE_KEY_HSE,    DWELL_TYP_HSE,    HSE_STAT_HSE,    ADDR1_HSE,    ADDR1_LOT_HSE,    RES_ADDR_2_HSE,    RES_CITY_HSE,    RES_STATE_HSE,    POSTAL_CDE_HSE,listagg(('MATCHING HOUSE' ||','|| 'MATCHING DWELL'), '|') WITHIN GROUP (ORDER BY 'MATCHING DWELL') AS 'OTHER DWELL TYPES'

FROM MATCH_ATL

GROUP BY
SYS_HSE,     SUB_ACCT_NO_SBB,     RES_NAME_SBB,    HSE_KEY_HSE,    DWELL_TYP_HSE,    HSE_STAT_HSE,    ADDR1_HSE,    ADDR1_LOT_HSE,    RES_ADDR_2_HSE,    RES_CITY_HSE,    RES_STATE_HSE,    POSTAL_CDE_HSE

 

3 REPLIES
Teradata Employee

Re: 3707 Error; expected like a name or a unicode btwn the word 'DWELL_TYP_HSE' and 'MATCHING DWELL'

In your query, 'MATCHING DWELL' is a string literal; for a name / alias, use double quotes: "MATCHING IDENTIFIER"

Re: 3707 Error; expected like a name or a unicode btwn the word 'DWELL_TYP_HSE' and 'MATCHING DWELL'

Thanks I fixed my aliases now I am looking at a "SELECT Failed 3706: Syntax error:expected something between '(' and '('

 

SyntaxEditor Code Snippet

/************************************************THIS SECTION PULL ALL SUBSCRIBERS BY DWELL TYPE, STRIPPING KEYWORDS FROM ADDRESS LINE ONE AND TWO*********************************************************************/
WITH HBB_ATL AS (SELECT DISTINCT SYS_HSE--- ATLANTA, PRIN_HSE, AGNT_HSE, SBB.SUB_ACCT_NO_SBB, SBB.RES_NAME_SBB, HSE_KEY_HSE, DWELL_TYP_HSE, HSE_STAT_HSE, ADDR1_HSE, ADDR1_LOT_HSE, RES_ADDR_2_HSE, REGEXP_REPLACE(ADDR1_HSE,'OFC 10|OFC 9|OFC 66|OFC 320|OFC 330|OFC 1|OFC 2|OFC 3|OFC 4|OFC 5|OFC 6|OFC 7|OFC 8|OFC A|BSMT M OFC 1|OFC 2|OFC 1|HOME|OFC|RM TELEWORKE|
|REAR OFFICE| MDTA BOX| MAIN SEP|LOWR LEVEL1|BLDG ITNESS|BLDG EDUCAT|OFC 2ND MODEM|3RD MODE|2ND MODE|2ND ACCT|REAR|FRNT|2ND|SPC SHRD|TRLR 2|TRLR 1|TRLR', NULL) AS ADDR1_HSE_STRP
, REGEXP_REPLACE(ADDR1_LOT_HSE,'OFC 10|OFC 9|OFC 66|OFC 320|OFC 330|OFC 1|OFC 2|OFC 3|OFC 4|OFC 5|OFC 6|OFC 7|OFC 8|OFC A|BSMT M OFC 1|OFC 2|OFC 1|HOME|OFC|RM TELEWORKE|
|REAR OFFICE| MDTA BOX| MAIN SEP|LOWR LEVEL1|BLDG ITNESS|BLDG EDUCAT|OFC 2ND MODEM|3RD MODE|2ND MODE|2ND ACCT|REAR|FRNT|2ND|SPC SHRD|TRLR 2|TRLR 1|TRLR', NULL) AS ADDR1_LOT_HSE_STRP
, REGEXP_REPLACE(REGEXP_REPLACE(RES_ADDR_2_HSE,'HOME OFFICE 1|HOME OFFICE 2|HOME OFFICE 3|HOME OFFICE|OFFICE|OFC 1|OFC 2|OFC|HOME OFFIE|HOME|VCSA ACCOUNT|UPPR|HSI ACCOUNT|
                                |HOMOE|BUSINESS|HME|EQUIPMENT ACCOUNT|VBSA ACCOUNT|OFFFICE|OFFCE|OFFCE1|HSI ACCOUNT|ACCOUNT HIERARCHY|
                                |DO NOT USE|TELCO ROOM|EQUIPMENT ACCT|SALES|LEASING|HSI 1|HSI DUPLICATE 3|REAR|DO NOT USE 4|
                                |DO NOT USE 2|DO NOT USE 1|- DO NOT USE|DO NOT USE|CLUBHOUSE|- DUPLICATE|2ND ACCT|OFFICE|OFICE|OFFUCE', NULL)
,REGEXP_REPLACE(RES_ADDR_2_HSE,'|OFFIVE|OFIFCE|OFFI|OFF|- TLWK|HSI ACCT|VCAE ACCOUNT|VCAE ACCT|TELCO ROOM|TELCO CLOSET|TELCO|WIFI ACCOUNT|WIFI ACCT|WIFI|
                                |THEATRE ROOM|THEATER', NULL), NULL) AS RES_ADDR_2_HSE_STRP
, RES_CITY_HSE, RES_STATE_HSE, SUBSTR(POSTAL_CDE_HSE,0,5) as POSTAL_CDE_HSE
FROM NDW_LOCATION_JRNL_VIEWS.HSE_BASE

JOIN NDW_ACCOUNTS_JRNL_VIEWS.SBB_BASE SBB
    ON SBB.SYS_SBB = SYS_HSE
    AND SBB.PRIN_SBB = PRIN_HSE
    AND SBB.AGNT_SBB = AGNT_HSE
    AND SBB.HSE_KEY_SBB = HSE_KEY_HSE
  AND SBB.EXT_STAT_SBB = ' '
  WHERE DWELL_TYP_HSE IN('BL','BM','CL','CM'))

/****************************************************************************THIS SECTION WILL PULL ANY MATCHING LOCATIONS BASED ADDRESS SEARCH********************************************************************/
, MATCH_ATL AS (SELECT HBB.SYS_HSE, HBB.PRIN_HSE, HBB.AGNT_HSE, HBB.SUB_ACCT_NO_SBB, HBB.RES_NAME_SBB,    HBB.HSE_KEY_HSE,    HBB.DWELL_TYP_HSE,    HBB.HSE_STAT_HSE,    HBB.ADDR1_HSE,    HBB.ADDR1_LOT_HSE,    HBB.RES_ADDR_2_HSE,    HBB.RES_CITY_HSE,    HBB.RES_STATE_HSE,    HBB.POSTAL_CDE_HSE, ADDR1_HSE_STRP, ADDR1_LOT_HSE_STRP, RES_ADDR_2_HSE_STRP, OTH.DWELL_TYP_HSE "MATCHING DWELL", OTH.HSE_KEY_HSE "MATCHING HOUSE", CASE WHEN OTH.DWELL_TYP_HSE IN ('AS','BT','CT','DT','YS')THEN 'Y' ELSE ' ' END AS "GOVERNMENT", CASE WHEN OTH.DWELL_TYP_HSE IN ('BO','CO') THEN 'Y' ELSE ' ' END AS "SCHOOL", CASE WHEN OTH.DWELL_TYP_HSE IN ('C4','C5','C6','C7','CF','DF','BF') THEN 'Y' ELSE ' 'END AS "HOSPITALITY", CASE WHEN OTH.DWELL_TYP_HSE IN ('WA','WB','WC','WD')THEN 'Y' ELSE ' ' END AS "COMCAST", CASE WHEN OTH.DWELL_TYP_HSE IN ('BL','BM','BP','BY','ZA','ZC','ZE'                    , 'ZW','C2','C3','CA','CC','CE','CG','CH','CJ','CL','CM','CP','CQ','CV'                    , 'HA','HB','DA','DH','DL','DM','LL','LN') THEN 'Y' ELSE ' ' END AS "COMMERCIAL", CASE WHEN OTH.DWELL_TYP_HSE IN ('AA','AU','BH','BW','HC','HD','NB'                    ,'NC','ND','NI','NJ','NK','NR','ZB','ZD','ZF','ZZ','YA','YB','YC','YD'                    ,'YI','YJ','YK','YR','YX','NN', 'AX') THEN 'Y' ELSE ' ' END AS "RESIDENTIAL", CASE WHEN OTH.DWELL_TYP_HSE IN ('XA','XB','XC','XD','XI','XJ','XK','XR','XS','XX','XZ') THEN 'Y' ELSE  ' ' END AS "BULK MASTER"

FROM HBB_ATL HBB
    LEFT JOIN NDW_LOCATION_JRNL_VIEWS.HSE_BASE  OTH
    ON HBB.SYS_HSE = OTH.SYS_HSE
        AND (TRIM(HBB.ADDR1_HSE_STRP)
                    || TRIM(HBB.ADDR1_LOT_HSE_STRP)
                    || TRIM(HBB.RES_ADDR_2_HSE_STRP)
                    || HBB.POSTAL_CDE_HSE) = (TRIM(OTH.ADDR1_HSE)
                                                || TRIM(OTH.ADDR1_LOT_HSE)
                                                || TRIM(OTH.RES_ADDR_2_HSE)
                                                || SUBSTR(OTH.POSTAL_CDE_HSE,0,5))
        AND OTH.HSE_KEY_HSE <> HBB.HSE_KEY_HSE)

/****************************************************** THIS SECTION WILL APPLY HBB IDENTIFICATION LOGIC****************************************/
SELECT SYS_HSE,     SUB_ACCT_NO_SBB,     RES_NAME_SBB,    HSE_KEY_HSE,    DWELL_TYP_HSE,    HSE_STAT_HSE,    ADDR1_HSE,    ADDR1_LOT_HSE,    RES_ADDR_2_HSE,    RES_CITY_HSE,    RES_STATE_HSE,    POSTAL_CDE_HSE,listagg(("MATCHING HOUSE" ||','|| "MATCHING DWELL"), '|') WITHIN GROUP (ORDER BY "MATCHING DWELL") AS "OTHER DWELL TYPES"

FROM MATCH_ATL

GROUP BY
SYS_HSE,     SUB_ACCT_NO_SBB,     RES_NAME_SBB,    HSE_KEY_HSE,    DWELL_TYP_HSE,    HSE_STAT_HSE,    ADDR1_HSE,    ADDR1_LOT_HSE,    RES_ADDR_2_HSE,    RES_CITY_HSE,    RES_STATE_HSE,    POSTAL_CDE_HSE
Highlighted
Junior Contributor

Re: 3707 Error; expected like a name or a unicode btwn the word 'DWELL_TYP_HSE' and 'MATCHING DWELL'

LISTAGG is not valid Teradata syntax, it's Oracle.

 

When you use SQL Assistant, switch to a .NET connection and you'll get the position of the error marked with a red line (similar to Word)