FLOAD Script throws error "TPT_INFRA: At "select" missing SEMICOL_ in rule: Job Definition Body"

Tools
Enthusiast

FLOAD Script throws error "TPT_INFRA: At "select" missing SEMICOL_ in rule: Job Definition Body"

Hi All,

I am having the below sql script from which we need to load the initial data as follows: 

It is throwing an error as TPT_INFRA: At "select" missing SEMICOL_ in rule: Job Definition Body

Can you guide me in identifying the cause of these error

SELECT a12.ANLST_ID as ANLST_ID,  

        a11.BKLG_MSR_IND as BKLG_MSR_IND, 

        a12.BUYER_ID as BUYER_NM1,  

         a11.CNSMPN_FCLTY_CD as CNSMPN_FCLTY_CD,  

        CASE WHEN a11.SSC_CD IN ('BTS','BTP') AND a11.FGA_ID IS NOT NULL THEN 'FGA EMBEDDED' 

                  WHEN a11.EMBDD_FLG = 'Y' THEN 'EMBEDDED'

                 WHEN a11.EMBDD_FLG = 'N' THEN 'NOT EMBEDDED' 

                 ELSE NULL END as CustCol_3,

                  a11.EMBDD_FLG as EMBDD_FLG,  

                 a11.SSC_CD as SSC_CD,  

                  a13.FULFLT_RGN_DESC as FULFLT_RGN_DESC,  

              CASE WHEN (a15.SUB_RGN_DESC= 'Inter-Company') THEN 'United States and Canada' ELSE a15.SUB_RGN_DESC END as         SUB_RGN_DESC, 

        a12.ISS_CD as ISS_CD, 

        a11.SYS_PROD_DEPENDENCY_CD as OPS_SALE_TYPE, 

        a12.ITM_DESC as ITM_DESC, a11.PART_ITM_NBR as PART_ITM_NBR, 

        a17.FISC_WEEK_VAL as FISC_MTH_VAL, 

        a16.FMLY_PARNT_PROD_TYPE_CD as FMLY_PARNT_PROD_TYPE_DESC, 

        a11.SYS_FLG as SYS_FLG, 

        a11.CCN as CCN, 

        CASE WHEN a11.ARB_FLG = 'Y' THEN 'ARB' ELSE a11.DERIVED_SLS_CHNL_VAL END as DERIVED_SLS_CHNL_VAL, 

        SUM(a11.PART_ITM_QTY) as  WJXBFS1 

        FROM ACTL_SALE_PART_SMRY_VW a11  

        JOIN  MFG_PART_ATTR  a12  ON (a11.CCN = a12.CCN AND a11.PART_ITM_NBR = a12.ITM_NBR) 

        JOIN  FULFLT_RGN  a13     ON (a11.BUILD_FCLTY_CD = a13.FCLTY_CD AND a11.INTER_CO_BU_ID = a13.INTER_CO_BU_ID) 

        JOIN CHNL_HIER    a14     ON (a11.INTER_CO_BU_ID = a14.BU_ID AND a11.INTER_CO_LCL_CHNL_CD = a14.LCL_CHNL_CODE) 

        JOIN  PHYS_GEO_HIER a15   ON (a14.RPTG_BU_ID = a15.BU_ID) 

        JOIN COMB_PROD_HIER a16   ON (a11.BASE_SYS_CD = a16.COMB_HIER_CD) 

        JOIN  CORP_CLDR a17       ON (a11.PART_SLS_DT = a17.CLDR_DATE) 

        WHERE a11.PART_SLS_DT BETWEEN (CURRENT_DATE - 100) AND CURRENT_DATE 

        GROUP BY a12.ANLST_ID, a11.BKLG_MSR_IND, 

                  a12.BUYER_ID, a12.BUYER_ID, a11.CNSMPN_FCLTY_CD, 

                CASE WHEN a11.SSC_CD IN ('BTS','BTP') AND a11.FGA_ID IS NOT NULL  THEN 'FGA EMBEDDED' 

                WHEN a11.EMBDD_FLG = 'Y' THEN 'EMBEDDED' 

                WHEN a11.EMBDD_FLG = 'N' THEN 'NOT EMBEDDED' 

                ELSE NULL END, 

                a11.EMBDD_FLG, a11.SSC_CD, 

                a13.FULFLT_RGN_DESC, CASE WHEN (a15.SUB_RGN_DESC= 'Inter-Company') THEN 'United States and Canada' ELSE a15.SUB_RGN_DESC END, 

                a12.ISS_CD, a11.SYS_PROD_DEPENDENCY_CD, a12.ITM_DESC, 

                a11.PART_ITM_NBR, a17.FISC_WEEK_VAL, a16.FMLY_PARNT_PROD_TYPE_CD, 

                a11.SYS_FLG, a11.CCN, 

                CASE WHEN a11.ARB_FLG = 'Y' THEN 'ARB' ELSE a11.DERIVED_SLS_CHNL_VAL END;', 

Thanks & Regards

saumil sanghvi

5 REPLIES
Teradata Employee

Re: FLOAD Script throws error "TPT_INFRA: At "select" missing SEMICOL_ in rule: Job Definition Body"

This is not a FLOAD job. This is a TPT job.

The error message you are getting is a syntax error.

In order to help you, you need to provide the entire script.

You are either missing a semicolon somewhere, or you have parentheses or quotes that are no matching up.

It would also be helpful if you would tell us what version of TPT you are using.

-- SteveF
Enthusiast

Re: FLOAD Script throws error "TPT_INFRA: At "select" missing SEMICOL_ in rule: Job Definition Body"

Hi, I am using 15.0 teradata client

I am having doubts regarding FLOAD.

We run fload using Cmd eg; C:/FASTLOAD < fld filename; Is it right?

I am having a case stmt in the select list, Is case allowed under select stmts and joins between multiple tables.

Below is the script

/***** This is the script for FLoad to Teradata Database *****/

/**** Logon to Teradata Environment ****/

/* Sessions command is used to restrict the Number of sessions FastLoad will make to connect to TD. Default is one session per AMP*/

SESSIONS 4;

LOGON 172.16.184.155/smartmigratecubes,smartmigratecubes;

/* In this section we are defining the table we want to load from Fastload. DROP commands are optional. There is no need to define the structure of ERROR tables they'll be created itself by Fastload. */

DROP TABLE smartmigratecubes.AGG_PART_SALES_TEST_CUBE_ET;

DROP TABLE smartmigratecubes.AGG_PART_SALES_TEST_CUBE_RL;

DROP TABLE smartmigratecubes.AGG_PART_SALES_TEST_CUBE_UV;

drop table smartmigratecubes.AGG_PART_SALES_TEST_CUBE;

/* In below section we will create Agg table having no records */

CREATE MULTISET TABLE smartmigratecubes.AGG_PART_SALES_TEST_CUBE ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Pk_Key INTEGER GENERATED ALWAYS AS IDENTITY (

      START WITH 1 INCREMENT BY 1 

      MINVALUE -2147483647 

      MAXVALUE 2147483647 NO CYCLE),

      ANLST_ID CHAR(4) CHARACTER SET LATIN CASESPECIFIC,

      BKLG_MSR_IND CHAR(1) CHARACTER SET LATIN CASESPECIFIC,

      BUYER_NM VARCHAR(35) CHARACTER SET LATIN CASESPECIFIC,

      CNSMPN_FCLTY_CD CHAR(16) CHARACTER SET LATIN CASESPECIFIC,

      CustCol_3 VARCHAR(20) CHARACTER SET LATIN CASESPECIFIC,

      EMBDD_FLG CHAR(1) CHARACTER SET LATIN CASESPECIFIC,

      SSC_CD VARCHAR(30) CHARACTER SET LATIN CASESPECIFIC,

      FULFLT_RGN_DESC CHAR(20) CHARACTER SET LATIN CASESPECIFIC,

      SUB_RGN_DESC VARCHAR(30) CHARACTER SET LATIN CASESPECIFIC,

      ISS_CD CHAR(2) CHARACTER SET LATIN CASESPECIFIC,

      OPS_SALE_TYPE CHAR(10) CHARACTER SET LATIN CASESPECIFIC,

      ITM_DESC VARCHAR(50) CHARACTER SET LATIN CASESPECIFIC,

      PART_ITM_NBR VARCHAR(30) CHARACTER SET LATIN CASESPECIFIC,

      FISC_MTH_VAL VARCHAR(10) CHARACTER SET LATIN CASESPECIFIC,

      FMLY_PARNT_PROD_TYPE_DESC VARCHAR(40) CHARACTER SET LATIN CASESPECIFIC,

      SYS_FLG CHAR(1) CHARACTER SET LATIN CASESPECIFIC,

      CCN CHAR(6) CHARACTER SET LATIN CASESPECIFIC,

      DERIVED_SLS_CHNL_VAL VARCHAR(16) CHARACTER SET LATIN CASESPECIFIC,

      PART_ITM_QTY BIGINT)

PRIMARY INDEX ( Pk_Key );

DEFINE

ANLST_ID (CHAR(4)),

      BKLG_MSR_IND (CHAR(1)),

      BUYER_NM (VARCHAR(35)),

      CNSMPN_FCLTY_CD (CHAR(16)),

      CustCol_3 (VARCHAR(20)),

      EMBDD_FLG (CHAR(1)),

      SSC_CD (VARCHAR(30)),

      FULFLT_RGN_DESC (CHAR(20)),

      SUB_RGN_DESC (VARCHAR(30)),

      ISS_CD (CHAR(2)),

      OPS_SALE_TYPE (CHAR(10)),

      ITM_DESC (VARCHAR(50)),

      PART_ITM_NBR (VARCHAR(30)),

      FISC_MTH_VAL (VARCHAR(10)),

      FMLY_PARNT_PROD_TYPE_DESC (VARCHAR(40)),

      SYS_FLG (CHAR(1)),

      CCN (CHAR(6)),

      DERIVED_SLS_CHNL_VAL (VARCHAR(16)),

      PART_ITM_QTY (BIGINT)

SELECT    a12.ANLST_ID  ANLST_ID,

                a11.BKLG_MSR_IND  BKLG_MSR_IND,

                a12.BUYER_ID  BUYER_NM,

                --a12.BUYER_ID  BUYER_NM1,

                a11.CNSMPN_FCLTY_CD  CNSMPN_FCLTY_CD,

                CASE 

WHEN a11.SSC_CD IN ('BTS','BTP') AND a11.FGA_ID IS NOT NULL  THEN 'FGA EMBEDDED'

WHEN a11.EMBDD_FLG = 'Y' THEN 'EMBEDDED'

WHEN a11.EMBDD_FLG = 'N' THEN 'NOT EMBEDDED'

ELSE NULL END  CustCol_3,

                a11.EMBDD_FLG  EMBDD_FLG,

                a11.SSC_CD  SSC_CD,

                a13.FULFLT_RGN_DESC  FULFLT_RGN_DESC,

                CASE WHEN (a15.SUB_RGN_DESC= 'Inter-Company') THEN 'United States and Canada' ELSE a15.SUB_RGN_DESC END  SUB_RGN_DESC,

                a12.ISS_CD  ISS_CD,

                a11.SYS_PROD_DEPENDENCY_CD  OPS_SALE_TYPE,

                a12.ITM_DESC  ITM_DESC,

                a11.PART_ITM_NBR  PART_ITM_NBR,

                a17.FISC_WEEK_VAL  FISC_MTH_VAL,

                a16.FMLY_PARNT_PROD_TYPE_CD  FMLY_PARNT_PROD_TYPE_DESC,

                a11.SYS_FLG  SYS_FLG,

                a11.CCN  CCN,

                CASE 

WHEN a11.ARB_FLG = 'Y' THEN 'ARB' ELSE a11.DERIVED_SLS_CHNL_VAL END  DERIVED_SLS_CHNL_VAL,

                SUM(a11.PART_ITM_QTY)  (FLOAT, NAMED ITEM_QTY )

FROM      ACTL_SALE_PART_SMRY_VW a11

                JOIN        MFG_PART_ATTR       a12

                  ON         (a11.CCN = a12.CCN)

                JOIN        FULFLT_RGN               a13

                  ON         (a11.BUILD_FCLTY_CD = a13.FCLTY_CD AND 

                a11.INTER_CO_BU_ID = a13.INTER_CO_BU_ID)

                JOIN        CHNL_HIER    a14

                  ON         (a11.INTER_CO_BU_ID = a14.BU_ID)                JOIN        PHYS_GEO_HIER         a15

                  ON         (a14.RPTG_BU_ID = a15.BU_ID)

                JOIN        COMB_PROD_HIER    a16

                  ON         (a11.BASE_SYS_CD = a16.COMB_HIER_CD)

                JOIN        CORP_CLDR            a17

                  ON         (a11.PART_SLS_DT = a17.CLDR_DATE)

GROUP BY              a12.ANLST_ID,

                a11.BKLG_MSR_IND,

                a12.BUYER_ID,

                a12.BUYER_ID,

                a11.CNSMPN_FCLTY_CD,

                CASE 

WHEN a11.SSC_CD IN ('BTS','BTP') AND a11.FGA_ID IS NOT NULL  THEN 'FGA EMBEDDED'

WHEN a11.EMBDD_FLG = 'Y' THEN 'EMBEDDED'

WHEN a11.EMBDD_FLG = 'N' THEN 'NOT EMBEDDED'

ELSE NULL END,

                a11.EMBDD_FLG,

                a11.SSC_CD,

                a13.FULFLT_RGN_DESC,

                CASE WHEN (a15.SUB_RGN_DESC= 'Inter-Company') THEN 'United States and Canada' ELSE a15.SUB_RGN_DESC END,

                a12.ISS_CD,

                a11.SYS_PROD_DEPENDENCY_CD,

                a12.ITM_DESC,

                a11.PART_ITM_NBR,

                a17.FISC_WEEK_VAL,

                a16.FMLY_PARNT_PROD_TYPE_CD,

                a11.SYS_FLG,

                a11.CCN,

                CASE 

WHEN a11.ARB_FLG = 'Y' THEN 'ARB' ELSE a11.DERIVED_SLS_CHNL_VAL END;

SHOW;

BEGIN LOADING smartmigratecubes.AGG_PART_SALES_TEST_CUBE;

INSERT INTO smartmigratecubes.AGG_PART_SALES_TEST_CUBE(

ANLST_ID, 

BKLG_MSR_IND, 

BUYER_NM, 

CNSMPN_FCLTY_CD, 

CustCol_3, 

EMBDD_FLG,

SSC_CD, 

FULFLT_RGN_DESC,                

SUB_RGN_DESC, 

ISS_CD, 

OPS_SALE_TYPE, 

ITM_DESC, 

PART_ITM_NBR, 

FISC_MTH_VAL, 

FMLY_PARNT_PROD_TYPE_DESC, 

SYS_FLG, CCN, 

DERIVED_SLS_CHNL_VAL, 

PART_ITM_QTY) 

VALUES 

                (:ANLST_ID,

:BKLG_MSR_IND,

:BUYER_NM,

:CNSMPN_FCLTY_CD,

:CustCol_3,

:EMBDD_FLG,

:SSC_CD,

:FULFLT_RGN_DESC,

:SUB_RGN_DESC,

:ISS_CD,

:OPS_SALE_TYPE,

:ITM_DESC,

:PART_ITM_NBR,

:FISC_MTH_VAL,

:FMLY_PARNT_PROD_TYPE_DESC, 

:SYS_FLG,

:CCN,

:DERIVED_SLS_CHNL_VAL,

:PART_ITM_QTY;

END LOADING;

LOGOFF;

Thanks & Regards

saumil sanghvi

Junior Contributor

Re: FLOAD Script throws error "TPT_INFRA: At "select" missing SEMICOL_ in rule: Job Definition Body"

FastLoad is for loading data into TD from an external source. 

But your script doesn't load any data, you try to use a Select as source, of course this will not work.

Why don't you simply write a SQL Insert/Select?

Enthusiast

Re: FLOAD Script throws error "TPT_INFRA: At "select" missing SEMICOL_ in rule: Job Definition Body"

Hi fienholz/Dieter,

Thanks much for your replies.

I created a script containing delete and insert statement which is working fine.

The above created script I run using tbuild -f file.txt -v Variable.txt.

Then I used the fastload utility and got the script generated, I updated that file with script changes and use file as input for tbuild which also worked fine.

I am having doubts when I run using tbuild -f A.txt (Is it calling FLOAD)?

when I run a txt file generated from FLOAD utility and running a tbuild -f A.txt(Is it fload)?

Thanks again

saumil sanghvi

Junior Contributor

Re: FLOAD Script throws error "TPT_INFRA: At "select" missing SEMICOL_ in rule: Job Definition Body"

The script you showed is a FastLoad script, you can't run that with TPT/tbuild.

There's no FLOAD utility, just MLOAD.

Neither FastLoad nor MLoad generate scripts, only FastExport can create a matching MLoad script.