Teradata error 2513 A data row is too long

Database
Teradata Employee

Teradata error 2513 A data row is too long

While running this query it gives the above error:

SELECT 

MAX(AMPCPUTime) MAX_CPU

,MIN(AMPCPUTime) MIN_CPU

,MIN(TotalIOCount) MIN_IO

,MAX(TotalIOCount) MAX_IO

,MIN((firstresptime - firstSteptime) HOUR(2) TO SECOND) MIN_EXT

,MAX((firstresptime - firstSteptime) HOUR(2) TO SECOND) MAX_EXT

,SUM(AMPCPUTime) SUM_CPU

,StartTime

,abortflag abrt

,GetQueryBandValue(q.queryband, 0 , 'Report_Name') Rpt

,((firstresptime - firstSteptime) HOUR(2) TO SECOND) (NAMED ExTm)

--,TDWMEstTotalTime

,AMPCPUTime CPU

,TotalIOCount IOCnt

,SpoolUsage SplUsg

--,EstResultRows EstRow

,NumResultRows ActRow

--,((firstresptime - starttime) HOUR(2) TO SECOND) (NAMED ElaTm)

--,((firstSteptime - starttime) HOUR(2) TO SECOND) (NAMED DLYTm)

--,DelayTime  DLYSc

--,MaxAMPCPUTime

--,MinAmpCPUTime

--,MaxAmpIO

--,MinAmpIO

--, CASE WHEN MaxAMPCPUTime > 0 THEN (1-(MinAmpCPUTime/MaxAMPCPUTime))*100 ELSE 100 END CPuSkew

--, CASE WHEN MaxAmpIO > 0 THEN (1-(MinAmpIO/MaxAmpIO))*100 ELSE 100 END IOSkew

,ERRORCODE Err

,Errortext

--,Queryhash

,sessionid 

,q.queryband

,Q.Queryid

--,statementtype

,username

--,ClientAddr

--,sessionid

,CAST(sq.SqlTextInfo AS VARCHAR(2000)) 

FROM PDCRDATA.DBQLogTbl_HST q , PDCRDATA.DBQLSqlTbl_HST sq  

WHERE queryband IS NOT NULL

AND TRIM(ClientAddr) LIKE ANY ('10.196.14.22','10.196.14.52')

AND Q.LogDate = SQ.LogDate

AND q.ProcID = sq.ProcID 

AND q.Queryid = Sq.Queryid

AND SQ.SqlRowNo = 1

AND (AMPCPUTime > 10000 OR  ExTm > '01:00:00' or IOCnt > 15000000)

--AND sq.SqlTextInfo LIKE '%FROM tdb.Event_Status_Hist%'

AND Q.LogDate BETWEEN  '2015-01-15'  AND date

--and abortflag <> 't'

--AND sessionid IN ( 833249, 843268)

--AND Queryhash IN ('FAA1D22E'xb )

--AND username  IN ('U_PL','U_NRT','U_ETL','U_DI')

AND AcctString like '%dss1%'

--AND statementtype = 'delete'

--AND  Queryid IN (163821832224922699,163821832224843856)

--AND  ExTm > '0:01:00'

--AND StartTime > '2014-11-15 18:00:00.00'

GROUP BY 8,9,10,11,12,13,14,15,16,17,18,19,20,21,22

But if I removed the MAX,MIN and SUM and remove group by  it runs normally and provides result without even casting the SqlTextInfo 

SELECT 

StartTime

,abortflag abrt

,GetQueryBandValue(q.queryband, 0 , 'Report_Name') Rpt

,((firstresptime - firstSteptime) HOUR(2) TO SECOND) (NAMED ExTm)

--,TDWMEstTotalTime

,AMPCPUTime CPU

,TotalIOCount IOCnt

,SpoolUsage SplUsg

--,EstResultRows EstRow

,NumResultRows ActRow

--,((firstresptime - starttime) HOUR(2) TO SECOND) (NAMED ElaTm)

--,((firstSteptime - starttime) HOUR(2) TO SECOND) (NAMED DLYTm)

--,DelayTime  DLYSc

--,MaxAMPCPUTime

--,MinAmpCPUTime

--,MaxAmpIO

--,MinAmpIO

--, CASE WHEN MaxAMPCPUTime > 0 THEN (1-(MinAmpCPUTime/MaxAMPCPUTime))*100 ELSE 100 END CPuSkew

--, CASE WHEN MaxAmpIO > 0 THEN (1-(MinAmpIO/MaxAmpIO))*100 ELSE 100 END IOSkew

,ERRORCODE Err

,Errortext

--,Queryhash

,sessionid 

,q.queryband

,Q.Queryid

--,statementtype

,username

--,ClientAddr

--,sessionid

,sq.SqlTextInfo

FROM PDCRDATA.DBQLogTbl_HST q , PDCRDATA.DBQLSqlTbl_HST sq  

WHERE queryband IS NOT NULL

AND TRIM(ClientAddr) LIKE ANY ('10.196.14.22','10.196.14.52')

AND Q.LogDate = SQ.LogDate

AND q.ProcID = sq.ProcID 

AND q.Queryid = Sq.Queryid

AND SQ.SqlRowNo = 1

AND (AMPCPUTime > 15000 OR  ExTm > '01:00:00' or IOCnt > 20000000)

--AND sq.SqlTextInfo LIKE '%FROM tdb.Event_Status_Hist%'

AND Q.LogDate BETWEEN  '2014-12-21'  AND date

--and abortflag <> 't'

--AND sessionid IN ( 833249, 843268)

--AND Queryhash IN ('FAA1D22E'xb )

--AND username  IN ('U_PL','U_NRT','U_ETL','U_DI')

AND AcctString like '%dss1%'

--AND statementtype = 'delete'

--AND  Queryid IN (163821832224922699,163821832224843856)

--AND  ExTm > '0:01:00'

--AND StartTime > '2014-11-15 18:00:00.00'


1 REPLY
Teradata Employee

Re: Teradata error 2513 A data row is too long

When you GROUP BY or ORDER BY, the grouping / ordering keys are appended to the spool row - with any variable length fields padded to max length.