Syntax when concatenating string and number

Database
Enthusiast

Syntax when concatenating string and number

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:

SELECT

CASE

WHEN Z.Store < 10 THEN '00' | | Z.Store

ELSE Z.Store END AS Store

,Z.ID

,Z.Item

,Z.Desc

,Z.Sales

,Z.Forecast

,Z.Change_Type

,Z.Suggested_Change

,'MY_ST_' | | Z.Store AS Loc

,'MY_' | | Z.ID | | '_FCSTCHNGE_2WK_TARGETED' AS Promotion

Tags (2)
4 REPLIES
Junior Contributor

Re: Syntax when concatenating string and number

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

Enthusiast

Re: Syntax when concatenating string and number

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

Junior Contributor

Re: Syntax when concatenating string and number

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.

Enthusiast

Re: Syntax when concatenating string and number

Thanks that is a lot cleaner than my origional code