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!
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.
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!
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