Page 1 of 1
SQL Error
Posted: Wed Sep 14, 2005 2:39 pm
by 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.
Posted: Wed Sep 14, 2005 2:42 pm
by 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?
Posted: Wed Sep 14, 2005 3:08 pm
by Sainath.Srinivasan
Cast individual cols before concat.
Posted: Wed Sep 14, 2005 3:25 pm
by snassimr
Use
PHONE.area_code : PHONE.prefix : PHONE.number
Posted: Wed Sep 14, 2005 3:38 pm
by 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.
Posted: Thu Sep 15, 2005 7:27 pm
by iwin
Hi,
Thanks guys, it finally worked.
Posted: Thu Sep 15, 2005 8:07 pm
by aartlett
[quote="iwin"]Hi,
Thanks guys, it finally worked.[/quote]
And what was it that finally worked? Ash's idea?
Posted: Thu Sep 15, 2005 8:23 pm
by 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.