ERROR 5505 'DATA CALUSE'

Database

ERROR 5505 'DATA CALUSE'

Hi All,

 

When I tried to run the code below, I encountered error 5505 'data clause'.

CREATE VOLATILE MULTISET TABLE USER_SAMPLE_BEHA AS (
SEL A.GUID, 
	A.SESSION_SKEY, 
	A.SESSION_START_DT, 
	A.SEQNUM, 
	A.USER_ID, 
	A.ITEM_ID,
	A.SQR,
	CASE WHEN PAGE_FMLY4_NAME = 'XO' THEN TRYCAST(SOJLIB.SOJ_NVL(SOJ, 'itm') AS DECIMAL(18, 0)) END AS XO_ITEM_ID,
	CASE WHEN PAGE_FMLY4_NAME = 'XO' THEN TRYCAST(SOJLIB.SOJ_NVL(SOJ, 'itm_qty') AS INT) END AS XO_ITEM_QTY,
	CASE WHEN PAGE_FMLY4_NAME = 'XO' THEN TRYCAST(SOJLIB.SOJ_NVL(SOJ, 'cart_itm') AS VARCHAR(100)) END AS XO_CART_ITEM,
	CASE WHEN PAGE_FMLY4_NAME = 'XO' THEN TRYCAST(SOJLIB.SOJ_NVL(SOJ, 'cart_id') AS INT) END AS XO_CART_ID
FROM UBI_V.UBI_EVENT_1PCT  A
INNER JOIN USER_SURVEY_SAMPLE B
ON A.GUID = B.GUID
AND A.USER_ID = B.USER_ID
INNER JOIN P_SOJ_CL_V.PAGES C
ON A.PAGE_ID = C.PAGE_ID
WHERE A.SESSION_START_DT = '2017-03-01'
AND A.SITE_ID = 0
AND PAGE_FMLY4_NAME = 'XO'
) WITH DATA PRIMARY INDEX (GUID, SESSION_START_DT, SESSION_SKEY, SEQNUM) ON COMMIT PRESERVE ROWS;

When I run only the 'select' part instead of creating a volatile table, the code runs fine. When I tried to replace 'TRYCAST' to 'CAST', the code runs fine too.

 

Could anyone point me the reason of the error?

 

Thanks a lot!

 

Lorraine

Tags (1)
3 REPLIES
N/A

Re: ERROR 5505 'DATA CALUSE'

Seems to be a parser bug.

 

You should open an incident with Teradata support.

As workaround you might replace TryCast to INT/DEC with TO_NUMBER, which also returns NULL when the cast fails.

And the VarChar-cast doesn't need a TRY.

 

Re: ERROR 5505 'DATA CALUSE'

Hi Dieter,

 

Thanks for the reply, but I am still not very clear of the usecase of 'to_number' and 'trycast'.

Trycast (according to this reference ) is intended to cast strings, since varchar is also string, why 'VarChar-cast doesn't need a TRY'?

Can trycast be applied on int/dec? Or only to_number() is allowed for int/dec?

 

Could you elaborate more? Thanks a lot for your help!

 

Lorraine

 

N/A

Re: ERROR 5505 'DATA CALUSE'

Unless you run an ANSI-mode session a CAST which truncates a string cannot result in an error (and then might be replaced by a SUBSTRING).

 

Of course TRYCAST can be used for INT/DEC, your SELECT runs fine.

 

But there seems to be a parser bug when included in a CREATE TABLE AS, switching to TO_NUMBER simply avoids this error