how to append 2 fields together

Hi I am inserting some data into a temp @.table and trying to combine two
different fields with the + character so in the insert statement I have,
Case when E.ColorID IS NOT Null then TE.equipmentName +
(Select [Color] from Color Where ID = E.Color_ID) END AS EquipmentName
seems to result in an error when running the query.
string or binary data would be truncated!
If I remove the TE.equipment_Type_Name_VC it runs fine.
I am trying to combine the TE.equipmentName and Color
thanks
--
Paul G
Software engineer.Paul,
Your concatenated strings are too long for the column declaration in your
temp @.table. A couple of ways to fix it are:
1. Increase the size of the column
2. Trim the text of the concatenation. For example, assuming (perhaps
incorrectly) that equipmentName and Color are both fixed length columns and
that you would like a space between the strings, you might use something
like:
LTRIM(LTRIM(Te.equipmentName) + ' ' + (Select [Color] from Color Where
ID = E.Color_ID))
I see that you are doing a subselect to get [Color] inside the case, but
this is probably not necessary. If you are only doing this to avoid
problems where there is no usable ColorID, then perhaps.
SELECT LTRIM(LTRIM(Te.equipmentName) + ' ' + COALESCE([Color],''))
FROM TechEquipment Te LEFT OUTER JOIN Color C
ON Te.ColorID = C.ID
The sample code aliases don't make sense to me, so this above is just an
outline. Be sure to plug in your tables and alias properly, etc.
RLF
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:4548E03A-B87F-43BE-ABDD-05C6766A4565@.microsoft.com...
> Hi I am inserting some data into a temp @.table and trying to combine two
> different fields with the + character so in the insert statement I have,
> Case when E.ColorID IS NOT Null then TE.equipmentName +
> (Select [Color] from Color Where ID = E.Color_ID) END AS EquipmentName
>
> seems to result in an error when running the query.
> string or binary data would be truncated!
> If I remove the TE.equipment_Type_Name_VC it runs fine.
> I am trying to combine the TE.equipmentName and Color
> thanks
> --
> Paul G
> Software engineer.|||Hi Russell, thanks for the response. I fixed it about 5 minutes ago, had to
increase the size on the column of the results table, one of the solutions
you specified!
--
Paul G
Software engineer.
"Russell Fields" wrote:
> Paul,
> Your concatenated strings are too long for the column declaration in your
> temp @.table. A couple of ways to fix it are:
> 1. Increase the size of the column
> 2. Trim the text of the concatenation. For example, assuming (perhaps
> incorrectly) that equipmentName and Color are both fixed length columns and
> that you would like a space between the strings, you might use something
> like:
> LTRIM(LTRIM(Te.equipmentName) + ' ' + (Select [Color] from Color Where
> ID = E.Color_ID))
> I see that you are doing a subselect to get [Color] inside the case, but
> this is probably not necessary. If you are only doing this to avoid
> problems where there is no usable ColorID, then perhaps.
> SELECT LTRIM(LTRIM(Te.equipmentName) + ' ' + COALESCE([Color],''))
> FROM TechEquipment Te LEFT OUTER JOIN Color C
> ON Te.ColorID = C.ID
> The sample code aliases don't make sense to me, so this above is just an
> outline. Be sure to plug in your tables and alias properly, etc.
> RLF
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:4548E03A-B87F-43BE-ABDD-05C6766A4565@.microsoft.com...
> > Hi I am inserting some data into a temp @.table and trying to combine two
> > different fields with the + character so in the insert statement I have,
> >
> > Case when E.ColorID IS NOT Null then TE.equipmentName +
> > (Select [Color] from Color Where ID = E.Color_ID) END AS EquipmentName
> >
> >
> > seems to result in an error when running the query.
> > string or binary data would be truncated!
> > If I remove the TE.equipment_Type_Name_VC it runs fine.
> > I am trying to combine the TE.equipmentName and Color
> > thanks
> > --
> > Paul G
> > Software engineer.
>
>