SQL Error

[iwin]

Hi,
I am trying to concatenate three columns of same data type (3,3,4) into one column of same data type (12) by giving the following statement, but i am getting an error.

Select
(PHONE.area_code || PHONE.prefix || PHONE.number) as Ph_nbr AS CHAR(12) from abc;

Please guide me where i am going wrong.

[pnchowdary]

Hi Iwin,

Why dont you read the values as individual columns using SQL and then merge all the three columns into one big column in a transformer?

[Sainath.Srinivasan]

Cast individual cols before concat.

[snassimr]

Use

PHONE.area_code : PHONE.prefix : PHONE.number

[logic]

Use ":" if doing in trans else use"||"
Try the following

Code: Select all

SELECT (t.area_code || t.prefix || t.number)  as ph_nbr  FROM abc t

cheers,
ash.

[iwin]

Hi,
Thanks guys, it finally worked.

[aartlett]

[quote="iwin"]Hi,
Thanks guys, it finally worked.[/quote]

And what was it that finally worked? Ash's idea?

[ray.wurlod]

You can't do "AS CHAR(12)". It's not legal as an alias. You use a CAST specification (if necessary) to change data type temporarily. The result of concatenation is necessarily string, so it's probably not necessary in that case.