Fixed text in SQL being truncated

Hi

This might be an obvious question to some of you, but I'm stumped and any help would be appreciated! I used to work in SQL Server, and am new to Teradata SQL. I'm not sure if this query is not working because of limitation differences between the two, or if it is a pure SQL problem. (Seeing how I couldn't find a teradata forum, I posted this problem here).

This is an excerpt of a query I'm trying to run in Teradata SQL Assistant:
SELECT
'TYLastRollUpCompWSSI' as WSSISet,
'FcastClosing Stock' as Measure,
sum(ZEROIFNULL(a.CCP_Closing_Stk_SV)) as Amt
FROM VWI0GPP_WKLY_SGRP_RPAS_PLAN a

In the resultset, however, the fixed text 'FcastClosing Stock' is being truncated to just 10 characters (i.e. 'FcastClosi) and the fixed text 'TYLastRollUpCompWSSI' is being truncated to just 20 characters (i.e. 'TYLastRollUpCompW').

It might seem silly that I need this ridiculously long names in the first place, but my resultset needs to conform with what is currently in the table.OK ... I managed to come up with a solution that seems to work: I have used "cast" in the select:

SELECT
cast('TYLastRollUpCompWSSI' as CHAR(30)) as WSSISet,
cast('FcastClosing Stock' AS CHAR(25)) as Measure, ... etc.

First 4 symbols of a string truncated when inserting typed XML

Dear SQL experts, is it a bug or I'm understanding something wrong?

Please look at the script below. Is it a bug? If yes, when can we expect it to be fixed and released? Are there any workarounds apart from just not using xsd:string type?

IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[TestSchema]')

DROP XML SCHEMA COLLECTION [dbo].[TestSchema]

CREATE XML SCHEMA COLLECTION [dbo].[TestSchema] AS N'

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="TEST" elementFormDefault="qualified">

<xsd:element name="Root" type="xsd:string"/>

</xsd:schema>'

GO

DECLARE @.doc xml (TestSchema)

SET @.doc = ''

-- Inserting string of 64 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234</Root>

into (/)')

-- Inserting string of 70 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234567890</Root>

into (/)')

-- Inserting string of 10 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a</Root>

into (/)')

SELECT @.doc

Here is the result:

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234</Root>

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234567890</Root>

<Root xmlns="TEST">123456789a</Root>

You can see that first two strings are truncated of 4 characters at the start.

The problem occurs in case if:

1. The variable or column is typed XML.

2. Type of the string is xsd:string or derived.

3. The string is longer than 63 characters (no matter how much longer, what is truncated are alwasy first 4 characters).

Thanks a lot in advance!

This is known issue. We have fixed this bug in Yukon SP2.

Jinghao Liu

|||

Hello Jinghao,

Thanks for the prompt answer!

And do you know approximate term - when the SP2 is going to be released to public?

Best regards,

Yuriy

|||

You can ask your SQL support contact about the schedule of Yukon Service Pack 2. They usually know better than us about the date. If you are important customer, you might get the early version of it.

|||Thanks, we'll try to.

First 4 symbols of a string truncated when inserting typed XML

Dear SQL experts, is it a bug or I'm understanding something wrong?

Please look at the script below. Is it a bug? If yes, when can we expect it to be fixed and released? Are there any workarounds apart from just not using xsd:string type?

IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[TestSchema]')

DROP XML SCHEMA COLLECTION [dbo].[TestSchema]

CREATE XML SCHEMA COLLECTION [dbo].[TestSchema] AS N'

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="TEST" elementFormDefault="qualified">

<xsd:element name="Root" type="xsd:string"/>

</xsd:schema>'

GO

DECLARE @.doc xml (TestSchema)

SET @.doc = ''

-- Inserting string of 64 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234</Root>

into (/)')

-- Inserting string of 70 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234567890</Root>

into (/)')

-- Inserting string of 10 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a</Root>

into (/)')

SELECT @.doc

Here is the result:

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234</Root>

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234567890</Root>

<Root xmlns="TEST">123456789a</Root>

You can see that first two strings are truncated of 4 characters at the start.

The problem occurs in case if:

1. The variable or column is typed XML.

2. Type of the string is xsd:string or derived.

3. The string is longer than 63 characters (no matter how much longer, what is truncated are alwasy first 4 characters).

Thanks a lot in advance!

This is known issue. We have fixed this bug in Yukon SP2.

Jinghao Liu

|||

Hello Jinghao,

Thanks for the prompt answer!

And do you know approximate term - when the SP2 is going to be released to public?

Best regards,

Yuriy

|||

You can ask your SQL support contact about the schedule of Yukon Service Pack 2. They usually know better than us about the date. If you are important customer, you might get the early version of it.

|||Thanks, we'll try to.