Teradata parser issue

Question

I am trying to execute the below query but I get the error

INSERT INTO TABLEA(
CUSTOMER_CT_KEY,
CUSTOMER_ST_KEY,
CUSTOMER_TEST_KEY,
JAN_AMT,
FEB_AMT, 
MAR_AMT)
SELECT
A.CUSTOMER_CT_KEY,
A.CUSTOMER_ST_KEY,
A.CUSTOMER_TEST_KEY,
SUM(CASE WHEN EXTRACT(MONTH FROM A.DATECOL) = '01'
            THEN A.AAA_AMT
            ELSE 0
           END),
SUM(CASE WHEN EXTRACT(MONTH FROM A.DATECOL) = '02'
            THEN A.BBB_AMT
            ELSE 0
           END),
SUM(CASE WHEN EXTRACT(MONTH FROM A.DATECOL) = '03'
            THEN A.CCC_AMT
            ELSE 0
           END)
FROM TABLEB B, TABLEC C
WHERE B.DATECOL<= C.DATECOL
AND B.CUSTOMER_CT_KEY NOT IN
(SELECT FS_CUSTOMER_CT_KEY FROM TABLED WHERE REF_NBR = 'VALUE')
GROUP BY 1,2,3;

Insert failed. 3899: Internal Error in Teradata SQL parser

Output:

CUSTOMER_CT_KEY CUSTOMER_ST_KEY CUSTOMER_TEST_KEY JAN_AMT FEB_AMT MAR_AMT
123456789       541245812       541245812         114.00  524.00   62.00
658412457       632514257       632514257           0.00   12.00  214.00     

Total number of rows in

TABLEA - EMPTY
TABLEB - 420,098,323
TABLEC - 1
TABLED - 218,074
INNER SUBQUERY - 5

When I tried hardcoding the value of the subquery it worked. Number of rows inserted : 105,615,541

Please guide me as how to proceed further. Thanks

Answer 1

Just an update on this issue. I have got the clarification from the Teradata Support. Before executing the Insert query, the following statement has to be executed.

diagnostic evlinterp on for session;

Answer 2

Basically the original query wanted to get some data from TABLEB and TABLEC, with a filter condition that you don't want records that are in TABLED, when REF_NBR='VALUE'

As Tim proposed, a possible workaround (while you raise a ticket to Teradata, as dnoeth suggested), could be to write the IN filter condition with a join.

My proposal for such a query is the following:

SELECT
 A.CUSTOMER_CT_KEY,
 A.CUSTOMER_ST_KEY,
 A.CUSTOMER_TEST_KEY,
 SUM(CASE WHEN EXTRACT(MONTH FROM A.DATECOL) = '01'
        THEN A.AAA_AMT
        ELSE 0
       END),
 SUM(CASE WHEN EXTRACT(MONTH FROM A.DATECOL) = '02'
        THEN A.BBB_AMT
        ELSE 0
       END),
 SUM(CASE WHEN EXTRACT(MONTH FROM A.DATECOL) = '03'
        THEN A.CCC_AMT
        ELSE 0
       END)
  FROM TABLEB B
       JOIN TABLEC C
         ON B.DATECOL<= C.DATECOL
       LEFT JOIN (SELECT FS_CUSTOMER_CT_KEY FROM TABLED
                   WHERE REF_NBR = 'VALUE' ) AS D
         ON B.CUSTOMER_CT_KEY=D.FS_CUSTOMER_CT_KEY
 WHERE D.FS_CUSTOMER_CT_KEY is null
 GROUP BY 1,2,3;

The D subquery holds all the records of the filter condition (the ones you want filtered out). The "D.FS_CUSTOMER_CT_KEY is null" condition makes sure that just records whose CUSTOMER_CT_KEY doesn't appear in subquery D will appear in the resultset.

Testing the solution

Since there have been some misunderstanding on the proposed solution, and the actual OP request, I tried to prepare a basic test case, so that we can use it to better understand each other solutions and the corner cases.

create multiset volatile table TABLEB (
 CUSTOMER_CT_KEY BIGINT,
 CUSTOMER_ST_KEY BIGINT,
 CUSTOMER_TEST_KEY BIGINT,
 AAA_AMT decimal(38,18),
 DATECOL date
 )
no primary index
on commit preserve rows;

create multiset volatile table TABLEC (
 DATECOL date
 )
no primary index
on commit preserve rows;

create multiset volatile table TABLED (
 FS_CUSTOMER_CT_KEY bigint,
 REF_NBR  VARCHAR(5) 
 )
no primary index
on commit preserve rows;


insert into tableb (CUSTOMER_CT_KEY, CUSTOMER_ST_KEY, CUSTOMER_TEST_KEY, AAA_AMT, DATECOL)
                values (123456789, 541245812, 541245812, 111, '2016-01-15');
insert into tableb (CUSTOMER_CT_KEY, CUSTOMER_ST_KEY, CUSTOMER_TEST_KEY, AAA_AMT, DATECOL)
                values (123456789, 541245812, 541245812, 524, '2016-02-15');
insert into tableb (CUSTOMER_CT_KEY, CUSTOMER_ST_KEY, CUSTOMER_TEST_KEY, AAA_AMT, DATECOL)
                values (123456789, 541245812, 541245812, 63, '2016-03-15');          

insert into tableb (CUSTOMER_CT_KEY, CUSTOMER_ST_KEY, CUSTOMER_TEST_KEY, AAA_AMT, DATECOL)
                values (777777777, 111222333, 444555666, 42, '2016-03-15');          


insert into tableb (CUSTOMER_CT_KEY, CUSTOMER_ST_KEY, CUSTOMER_TEST_KEY, AAA_AMT, DATECOL)
                values (658412457, 632514257, 632514257, 0, '2016-01-15');
insert into tableb (CUSTOMER_CT_KEY, CUSTOMER_ST_KEY, CUSTOMER_TEST_KEY, AAA_AMT, DATECOL)
                values (658412457, 632514257, 632514257, 12, '2016-02-15');
insert into tableb (CUSTOMER_CT_KEY, CUSTOMER_ST_KEY, CUSTOMER_TEST_KEY, AAA_AMT, DATECOL)
                values (658412457, 632514257, 632514257, 214, '2016-03-15');                                                              

insert into tableb (CUSTOMER_CT_KEY, CUSTOMER_ST_KEY, CUSTOMER_TEST_KEY, AAA_AMT, DATECOL)
                values (777777777, 999888777, 666555444, 42, '2016-03-15');          

insert into tablec ( DATECOL)                
                values ( '2016-04-01');                                                              

insert into tabled ( FS_CUSTOMER_CT_KEY, REF_NBR)                
                values ( 777777777, 'VALUE');      
insert into tabled ( FS_CUSTOMER_CT_KEY, REF_NBR)                
                values ( 658412457, 'OK1');                                                                              
insert into tabled ( FS_CUSTOMER_CT_KEY, REF_NBR)                
                values ( 658412457, 'OK2');                                                                              
insert into tabled ( FS_CUSTOMER_CT_KEY, REF_NBR)                
                values ( 658412457, 'OK3');                                                                              

select 
 B.CUSTOMER_CT_KEY,
 B.CUSTOMER_ST_KEY,
 B.CUSTOMER_TEST_KEY,
 SUM(case when extract(MONTH from b.DATECOL)='01' then b.aaa_amt else 0 end) jan_amt,
SUM(case when extract(MONTH from b.DATECOL)='02' then b.aaa_amt else 0 end) feb_amt,
SUM(case when extract(MONTH from b.DATECOL)='03' then b.aaa_amt else 0 end) mar_amt
 from tableb b, tablec c
 where b.datecol<=c.datecol
 and b.CUSTOMER_CT_KEY not in (select FS_CUSTOMER_CT_KEY from TABLED WHERE REF_NBR='VALUE')
 group by 1,2,3;

 select 
 B.CUSTOMER_CT_KEY,
 B.CUSTOMER_ST_KEY,
 B.CUSTOMER_TEST_KEY,
 SUM(case when extract(MONTH from b.DATECOL)='01' then b.aaa_amt else 0 end) jan_amt,
SUM(case when extract(MONTH from b.DATECOL)='02' then b.aaa_amt else 0 end) feb_amt,
SUM(case when extract(MONTH from b.DATECOL)='03' then b.aaa_amt else 0 end) mar_amt
 from tableb b 
   inner join tablec c
   on  b.datecol<=c.datecol
   left join TABLED D
      ON B.CUSTOMER_CT_KEY=D.FS_CUSTOMER_CT_KEY
             AND D.REF_NBR<>'VALUE'
 group by 1,2,3;

 select 
 B.CUSTOMER_CT_KEY,
 B.CUSTOMER_ST_KEY,
 B.CUSTOMER_TEST_KEY,
 SUM(case when extract(MONTH from b.DATECOL)='01' then b.aaa_amt else 0 end) jan_amt,
SUM(case when extract(MONTH from b.DATECOL)='02' then b.aaa_amt else 0 end) feb_amt,
SUM(case when extract(MONTH from b.DATECOL)='03' then b.aaa_amt else 0 end) mar_amt
 from tableb b 
   inner join tablec c
   on  b.datecol<=c.datecol
   left join (select FS_CUSTOMER_CT_KEY from  TABLED where REF_NBR='VALUE' ) as D
      ON B.CUSTOMER_CT_KEY=D.FS_CUSTOMER_CT_KEY
WHERE D.FS_CUSTOMER_CT_KEY is null
 group by 1,2,3;

With the (biased and totally fictitious :-) data I've got th attached results.

Simulation of the original query

Simulation of the initial proposal

Simulation of my current proposal (I already told you that the data was biased and fictitious, right? :-) )

Could the OP give some feedback or some fake data to insert in the volatile tables?

Answer 3

If you get an error like this you should open an incident with Teradata support. This is from the Messages manual:

3899 Internal error in the Teradata SQL Parser.
Explanation: The Teradata SQL Parser erred.
Generated By: CON, LEX, PAR, SYN, RES and OPT modules.
For Whom: System Support Representative.
Notes: This is usually caused by a request that the Teradata SQL Parser could not correctly process, yet it did not detect
an error.
Remedy: Save all relevant information and notify your support representative.