I am trying to create new variables using a combination of text and numbers. I thought this was possible but do not know for sure. I am getting the following error dont know if its because im missing something simple or the way im trying to concatenate is fundementally incorrect. The query ran exactly as desired prior to adding the concatenated fields. For reference the store and ID variables are numerical:
'SELECT Failed. 3707: Syntax error, expected something like an 'END' keyword between a string or a Unicode character literal and '|'.
Below is my Select statement:
WHEN Z.Store < 10 THEN '00' | | Z.Store
ELSE Z.Store END AS Store
,'MY_ST_' | | Z.Store AS Loc
,'MY_' | | Z.ID | | '_FCSTCHNGE_2WK_TARGETED' AS Promotion
Seems you got a space character between after the first "|", remove it to get "||" instead of "| |"
And instead of directly concatenating Store/ID which might result it additional leading blanks you better use TRIM(Store).
Thanks again dieter,
one more question; is there a way to make it return leading 0s instead of removing the blanks. For example store 10 returned as 010 or possibly 0010
You can't store leading blanks in a numeric column, but every column in Teradata has a default format applied when casted. You you can change it in the CREATE TABLE:
Store INTEGER FORMAT '9(8)'
or on the fly using
trim(Store (format '9(8)'))
Both will return a 8 digit string with leading zeroes, simply adust it to your needs.