Data getting truncated in select query with an outer cast and large number of concatenated columns.

Database
Enthusiast

Data getting truncated in select query with an outer cast and large number of concatenated columns.

Hi All,

 

We are trying to execute a query similar to the one below(Column names changed to dummy values) in SQL assistant in Teradata:

select cast('"'||COALESCE(TRIM(cast((Value1 (FORMAT '-Z(3)')) as VARCHAR(5))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast((Value2 (FORMAT '-Z(5)')) as VARCHAR(6))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value3 as VARCHAR(14))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value4 as VARCHAR(25))),'')||'"'||'|'
||'"'||OREPLACE(COALESCE(TRIM(cast((Value5 (FORMAT '-Z(34)9.99')) as VARCHAR(40))),''),' ','')||'"'||'|'
||'"'||OREPLACE(COALESCE(TRIM(cast((Value6 (FORMAT '-Z(34)9.99')) as VARCHAR(40))),''),' ','')||'"'||'|'
||'"'||OREPLACE(COALESCE(TRIM(cast((Value7 (FORMAT '-Z(34)9.99')) as VARCHAR(40))),''),' ','')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value8 as VARCHAR(12))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value9 as VARCHAR(18))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value99 as VARCHAR(25))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value10 as VARCHAR(30))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value12 as VARCHAR(36))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value13 as VARCHAR(5))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value14 as VARCHAR(10))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value15 as VARCHAR(5))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value16 as VARCHAR(5))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value17 as VARCHAR(12))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value18 as VARCHAR(15))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value19 as VARCHAR(22))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value20 as VARCHAR(5))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value21 as VARCHAR(12))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value22 as VARCHAR(22))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value23 as VARCHAR(5))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value24 as VARCHAR(20))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(Value25 as VARCHAR(12))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(periodvalue26 as VARCHAR(30))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(periodvalue27 as VARCHAR(40))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(periodvalue28 as VARCHAR(55))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(periodvalue29 as VARCHAR(65))),'')||'"'||'|'
||'"'||COALESCE(TRIM(cast(periodvalue30 as VARCHAR(78))),'')||'"'||'|'
||'"'||COALESCE(TRIM(value31_name),'')||'"'||'|'
||'"'||COALESCE(TRIM(value32_dep),'')||'"'
||'|'||'"'||hash_md5(COALESCE(TRIM(cast((Value1 (FORMAT '-Z(3)')) as VARCHAR(5))),'')
||COALESCE(TRIM(cast((Value2 (FORMAT '-Z(5)')) as VARCHAR(6))),'')
||COALESCE(TRIM(cast(Value3 as VARCHAR(14))),'')
||COALESCE(TRIM(cast(Value4 as VARCHAR(25))),'')
||OREPLACE(COALESCE(TRIM(cast((Value5 (FORMAT '-Z(34)9.99')) as VARCHAR(40))),''),' ','')
||OREPLACE(COALESCE(TRIM(cast((Value6 (FORMAT '-Z(34)9.99')) as VARCHAR(40))),''),' ','')
||OREPLACE(COALESCE(TRIM(cast((Value7 (FORMAT '-Z(34)9.99')) as VARCHAR(40))),''),' ','')
||COALESCE(TRIM(cast(Value8 as VARCHAR(12))),'')
||COALESCE(TRIM(cast(Value9 as VARCHAR(18))),'')
||COALESCE(TRIM(cast(Value99 as VARCHAR(25))),'')
||COALESCE(TRIM(cast(Value10 as VARCHAR(30))),'')
||COALESCE(TRIM(cast(Value12 as VARCHAR(36))),'')
||COALESCE(TRIM(cast(Value13 as VARCHAR(5))),'')
||COALESCE(TRIM(cast(Value14 as VARCHAR(10))),'')
||COALESCE(TRIM(cast(Value15 as VARCHAR(5))),'')
||COALESCE(TRIM(cast(Value16 as VARCHAR(5))),'')
||COALESCE(TRIM(cast(Value17 as VARCHAR(12))),'')
||COALESCE(TRIM(cast(Value18 as VARCHAR(15))),'')
||COALESCE(TRIM(cast(Value19 as VARCHAR(22))),'')
||COALESCE(TRIM(cast(Value20 as VARCHAR(5))),'')
||COALESCE(TRIM(cast(Value21 as VARCHAR(12))),'')
||COALESCE(TRIM(cast(Value22 as VARCHAR(22))),'')
||COALESCE(TRIM(cast(Value23 as VARCHAR(5))),'')
||COALESCE(TRIM(cast(Value24 as VARCHAR(20))),'')
||COALESCE(TRIM(cast(Value25 as VARCHAR(12))),'')
||COALESCE(TRIM(cast(periodvalue26 as VARCHAR(30))),'')
||COALESCE(TRIM(cast(periodvalue27 as VARCHAR(40))),'')
||COALESCE(TRIM(cast(periodvalue28 as VARCHAR(55))),'')
||COALESCE(TRIM(cast(periodvalue29 as VARCHAR(65))),'')
||COALESCE(TRIM(cast(periodvalue30 as VARCHAR(78))),'')
||COALESCE(TRIM(value31_name),'')
||COALESCE(TRIM(value32_dept),''))
||'"' AS CHAR(2000)) from testtable

 

 

The structure of the table is as given below:

Value1 BYTEINT,
Value2 SMALLINT,
Value3 INTEGER,
Value4 BIGINT,
Value5 FLOAT,
Value6 FLOAT,
Value7 FLOAT,
Value8 DATE FORMAT 'YYYY-MM-DD',
Value9 TIME(2),
Value99 TIME(2) WITH TIME ZONE,
Value10 TIMESTAMP(6),
Value12 TIMESTAMP(6) WITH TIME ZONE FORMAT 'YYYY-MM-DDBHH:MI:SS.S(6)BZ',
Value13 INTERVAL YEAR(2),
Value14 INTERVAL YEAR(2) TO MONTH,
Value15 INTERVAL MONTH(2),
Value16 INTERVAL DAY(2),
Value17 INTERVAL DAY(2) TO HOUR,
Value18 INTERVAL DAY(2) TO MINUTE,
Value19 INTERVAL DAY(2) TO SECOND(6),
Value20 INTERVAL HOUR(2),
Value21 INTERVAL HOUR(2) TO MINUTE,
Value22 INTERVAL HOUR(2) TO SECOND(6),
Value23 INTERVAL MINUTE(2),
Value24 INTERVAL MINUTE(2) TO SECOND(6),
Value25 INTERVAL SECOND(2,6),
periodvalue26 PERIOD(DATE),
periodvalue27 PERIOD(TIME(2)),
periodvalue28 PERIOD(TIME(2) WITH TIME ZONE),
periodvalue29 PERIOD(TIMESTAMP(6)),
periodvalue30 PERIOD(TIMESTAMP(6) WITH TIME ZONE),
value31_name VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
value32_dep CHAR(222) CHARACTER SET LATIN NOT CASESPECIFIC

 

The issue is the first 2 characters are getting truncated:

Expected result(sample):

"98"|"12627"|"214483647"|"23337203654775808"|"123.40"|"44.50"|"555.66"|"1999-07-01"|"11:37:58.00"|"11:37:58.14+08:00"|"1999-01-01 23:59:59.000000"|"2000-01-01 11:37:58.140000 +08:00"|"3"|"12-04"|"88"|"33"|"1 23"|"9 23:59"|"19 23:59:59.900000"|"5"|"29:59"|"69:59:59.900000"|"55"|"19:59.000000"|"9.000000"|"('2000-09-09', '2001-10-11')"|"('09:33:33.00', '10:45:22.00')"|"('09:33:33.00+08:00', '10:15:22.00+08:00')"|"('1999-01-17 09:33:33.000000', '1999-01-27 10:15:22.000000')"|"('1999-01-17 09:33:33.000000+08:00', '1999-01-27 10:15:22.000000+08:00')"|"dummy"|"dummy1"|"2F3BC56F3A52B60D26B0397C4E2E78E2"

 

Result we are getting:

8"|"12627"|"214483647"|"23337203654775808"|"123.40"|"44.50"|"555.66"|"1999-07-01"|"11:37:58.00"|"11:37:58.14+08:00"|"1999-01-01 23:59:59.000000"|"2000-01-01 11:37:58.140000 +08:00"|"3"|"12-04"|"88"|"33"|"1 23"|"9 23:59"|"19 23:59:59.900000"|"5"|"29:59"|"69:59:59.900000"|"55"|"19:59.000000"|"9.000000"|"('2000-09-09', '2001-10-11')"|"('09:33:33.00', '10:45:22.00')"|"('09:33:33.00+08:00', '10:15:22.00+08:00')"|"('1999-01-17 09:33:33.000000', '1999-01-27 10:15:22.000000')"|"('1999-01-17 09:33:33.000000+08:00', '1999-01-27 10:15:22.000000+08:00')"|"dummy"|"dummy1"|"2F3BC56F3A52B60D26B0397C4E2E78E2"

 

The portion highlighted in bold i.e first 2 characters   "9   are getting truncated.

In case we remove a column or 2 from the concatenation operator it is coming properly.

Also removing the outer cast gives the correct output.

Anyone who faced a similar problem??Is there an limitation.

 

 

Please Note:We are using some many cast and concatenation because this result has to be exported in an Fastexport script.So we need an outer cast to remove the junk characters at the beginining.

 

4 REPLIES
Enthusiast

Re: Data getting truncated in select query with an outer cast and large number of concatenated colum

Wellll that is a long text! I saw a few OREPLACE() functions without a cast. OREPLACE has an output of 8000 characters and 3x OREPLACE comes close to the max with that is allowed for the final spool. Normally you get an error, but maybe this is related?

Junior Contributor

Re: Data getting truncated in select query with an outer cast and large number of concatenated colum

Don't know why thoe output is truncated, but imho it's not due to the Select, it's probably the FastExport script (like an Outmod removing the first two bytes).

 

But your approach is quite bad, FastExport actually exports trailing blanks and you cast your result as a CHAR(2000).

 

There's a much simpler approach using the CSV function like this:

WITH cte AS
 (
   SELECT 
       Trim(Value1 (Format '-Z(3)'))                            AS c1
      ,Trim(Value2 (Format '-Z(5)'))                            AS c2
      ,Trim(Cast(Value3 AS VARCHAR(14)))                        AS c3
      ,Trim(Cast(Value4 AS VARCHAR(25)))                        AS c4
      ,Trim(Cast((Value5 (Format '-(35)9.99')) AS VARCHAR(40))) AS c5 -- no oReplace
      ,Trim(Cast((Value6 (Format '-(35)9.99')) AS VARCHAR(40))) AS c6
      ,Trim(Cast((Value7 (Format '-(35)9.99')) AS VARCHAR(40))) AS c7
      ,Trim(Cast(Value8 AS VARCHAR(12)))                        AS c8
      ,Trim(Cast(Value9 AS VARCHAR(18)))                        AS c9
      ,Trim(Cast(Value99 AS VARCHAR(25)))                       AS c10
      ,Trim(Cast(Value10 AS VARCHAR(30)))                       AS c11
      ,Trim(Cast(Value12 AS VARCHAR(36)))                       AS c12
      ,Trim(Cast(Value13 AS VARCHAR(5)))                        AS c13
      ,Trim(Cast(Value14 AS VARCHAR(10)))                       AS c14
      ,Trim(Cast(Value15 AS VARCHAR(5)))                        AS c15
      ,Trim(Cast(Value16 AS VARCHAR(5)))                        AS c16
      ,Trim(Cast(Value17 AS VARCHAR(12)))                       AS c17
      ,Trim(Cast(Value18 AS VARCHAR(15)))                       AS c18
      ,Trim(Cast(Value19 AS VARCHAR(22)))                       AS c19
      ,Trim(Cast(Value20 AS VARCHAR(5)))                        AS c20
      ,Trim(Cast(Value21 AS VARCHAR(12)))                       AS c21
      ,Trim(Cast(Value22 AS VARCHAR(22)))                       AS c22
      ,Trim(Cast(Value23 AS VARCHAR(5)))                        AS c23
      ,Trim(Cast(Value24 AS VARCHAR(20)))                       AS c24
      ,Trim(Cast(Value25 AS VARCHAR(12)))                       AS c25
      ,Trim(Cast(periodvalue26 AS VARCHAR(30)))                 AS c26
      ,Trim(Cast(periodvalue27 AS VARCHAR(40)))                 AS c27
      ,Trim(Cast(periodvalue28 AS VARCHAR(55)))                 AS c28
      ,Trim(Cast(periodvalue29 AS VARCHAR(65)))                 AS c29
      ,Trim(Cast(periodvalue30 AS VARCHAR(78)))                 AS c30
      ,Trim(value31_name)                                       AS c31
      ,Trim(value32_dep)                                        AS c32
   FROM testtable
 )
SELECT op, hash_md5(op)
FROM
TABLE(Csv(NEW VARIANT_TYPE(
    cte.c1 
   ,cte.c2
   ,cte.c3
   ,cte.c4
   ,cte.c5
   ,cte.c6
   ,cte.c7
   ,cte.c8
   ,cte.c9
   ,cte.c10
   ,cte.c11
   ,cte.c12
   ,cte.c13
   ,cte.c14
   ,cte.c15
   ,cte.c16
   ,cte.c17
   ,cte.c18
   ,cte.c19
   ,cte.c20
   ,cte.c21
   ,cte.c22
   ,cte.c23
   ,cte.c24
   ,cte.c25
   ,cte.c26
   ,cte.c27
   ,cte.c28
   ,cte.c29
   ,cte.c30
   ,cte.c31
   ,cte.c32
), '|',
'"')
RETURNS (op VARCHAR(2000) CHARACTER SET Latin)) AS dt

There's no need to CAST/TRIM/COALESCE all columns as CSV takes care of it (unless you need a different format than the default), only Periods are not known to CSV, thus must be casted to strings. 

 

There's also no need for oReplace, simply specify a format with a floating minus sign 

 

Enthusiast

Re: Data getting truncated in select query with an outer cast and large number of concatenated colum

@dnoeth

Changing the Oreplace function in the Query and specifying a floating minus sign as you suggested solved the truncation issue as mentioned above.

 

But regarding the use of CSV function and the code snippet that you provided below:

WITH cte AS
 (
   SELECT 
       Trim(Value1 (Format '-Z(3)'))                            AS c1
      ,Trim(Value2 (Format '-Z(5)'))                            AS c2
      ,Trim(Cast(Value3 AS VARCHAR(14)))                        AS c3
      ,Trim(Cast(Value4 AS VARCHAR(25)))                        AS c4
      ,Trim(Cast((Value5 (Format '-(35)9.99')) AS VARCHAR(40))) AS c5 -- no oReplace
      ,Trim(Cast((Value6 (Format '-(35)9.99')) AS VARCHAR(40))) AS c6
      ,Trim(Cast((Value7 (Format '-(35)9.99')) AS VARCHAR(40))) AS c7
      ,Trim(Cast(Value8 AS VARCHAR(12)))                        AS c8
      ,Trim(Cast(Value9 AS VARCHAR(18)))                        AS c9
      ,Trim(Cast(Value99 AS VARCHAR(25)))                       AS c10
      ,Trim(Cast(Value10 AS VARCHAR(30)))                       AS c11
      ,Trim(Cast(Value12 AS VARCHAR(36)))                       AS c12
      ,Trim(Cast(Value13 AS VARCHAR(5)))                        AS c13
      ,Trim(Cast(Value14 AS VARCHAR(10)))                       AS c14
      ,Trim(Cast(Value15 AS VARCHAR(5)))                        AS c15
      ,Trim(Cast(Value16 AS VARCHAR(5)))                        AS c16
      ,Trim(Cast(Value17 AS VARCHAR(12)))                       AS c17
      ,Trim(Cast(Value18 AS VARCHAR(15)))                       AS c18
      ,Trim(Cast(Value19 AS VARCHAR(22)))                       AS c19
      ,Trim(Cast(Value20 AS VARCHAR(5)))                        AS c20
      ,Trim(Cast(Value21 AS VARCHAR(12)))                       AS c21
      ,Trim(Cast(Value22 AS VARCHAR(22)))                       AS c22
      ,Trim(Cast(Value23 AS VARCHAR(5)))                        AS c23
      ,Trim(Cast(Value24 AS VARCHAR(20)))                       AS c24
      ,Trim(Cast(Value25 AS VARCHAR(12)))                       AS c25
      ,Trim(Cast(periodvalue26 AS VARCHAR(30)))                 AS c26
      ,Trim(Cast(periodvalue27 AS VARCHAR(40)))                 AS c27
      ,Trim(Cast(periodvalue28 AS VARCHAR(55)))                 AS c28
      ,Trim(Cast(periodvalue29 AS VARCHAR(65)))                 AS c29
      ,Trim(Cast(periodvalue30 AS VARCHAR(78)))                 AS c30
      ,Trim(value31_name)                                       AS c31
      ,Trim(value32_dep)                                        AS c32
   FROM testtable
 )
SELECT op, hash_md5(op)
FROM
TABLE(Csv(NEW VARIANT_TYPE(
    cte.c1 
   ,cte.c2
   ,cte.c3
   ,cte.c4
   ,cte.c5
   ,cte.c6
   ,cte.c7
   ,cte.c8
   ,cte.c9
   ,cte.c10
   ,cte.c11
   ,cte.c12
   ,cte.c13
   ,cte.c14
   ,cte.c15
   ,cte.c16
   ,cte.c17
   ,cte.c18
   ,cte.c19
   ,cte.c20
   ,cte.c21
   ,cte.c22
   ,cte.c23
   ,cte.c24
   ,cte.c25
   ,cte.c26
   ,cte.c27
   ,cte.c28
   ,cte.c29
   ,cte.c30
   ,cte.c31
   ,cte.c32
), '|',
'"')
RETURNS (op VARCHAR(2000) CHARACTER SET Latin)) AS dt

When I try to execute this Query in FastExport/SQLAssitant I am getting an error as below
SELECT Failed. [3706] Syntax error:expected something between the work 'cte' and the 'AS' keyword.

I am using Teradata 13.00 and FEXP 15.00.00.00.Also could you provide some information/links for the CSV function in Teradata as you are using
coz I am unable to find any information about the function in Teradata?


Regards,
Indranil Roy

 

Junior Contributor

Re: Data getting truncated in select query with an outer cast and large number of concatenated colum

TD13 is an ancient version, the CSV Table Function has been added later (13.10 or 14), similar to a CTE without column list.