Filtering results of concatenation


Filtering results of concatenation

Hi Everyone!

I'm having trouble with filtering concatenation results in Teradata. I'm using the following syntax "EXTRACT(YEAR from saledate)||EXTRACT(MONTH from saledate) AS date_num" to combine the year number with the month number for a variety of dates. This works great and the results are like the following "2005 4", "2008 9" etc.

However, when I try to select only a certain date pairing in a WHERE clause (WHERE date_num = '2005 4') for example, Teradata tells me no data is available. Why is it not recognizing the '2005 4'? Am I doing something wrong, or is there a workaround?

Thanks for any help!

Tags (1)
Teradata Employee

Re: Filtering results of concatenation

It's the extra spaces introduced by implicit typecast from INTEGER result of extract. The default is FORMAT -(10)9 so the year, for example is 2005 with 5 leading spaces.

Explicitly CAST(EXTRACT(YEAR from saledate) as VARCHAR(4)), which trims the spaces, or rely on implicit cast but explicitly TRIM the result. You may want to explicitly concatenate a single space between year and month.

Or use FORMAT with CAST to explicitly control the result, e.g. CAST(CAST(EXTRACT(YEAR from saledate) AS FORMAT '9999') as VARCHAR(4)). This method would also allow you to have months less than 10 appear as two digits with a leading zero.

Re: Filtering results of concatenation

Thanks, Fred! I used this approach: "CAST(EXTRACT(YEAR from saledate) as VARCHAR(4))||' '||CAST(EXTRACT(MONTH from saledate) as VARCHAR(4)) AS date_num" and it worked perfectly.