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

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

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 an
d
> that you would like a space between the strings, you might use something
> like:
> LTRIM(LTRIM(Te.equipmentName) + ' ' + (Select [Color] from Color W
here
> ID = E.Color_ID))
> I see that you are doing a subselect to get [Color] inside the case, b
ut
> 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...
>
>

How to add an SQL to report?

I am using Crystal Reports 9.0.

I would like to link to tables together with fields "unitID" and "PropUnit"

I links the fields but when I add more then one field to the report the data disappears in the preview.

I looked at Crystals sample report. It uses a SQL statement.

How do I add an SQL statement if that's what I need to get this to work.

Thanks,

SteveIs it possible that there are no records satisfying this criteria/join ?

I don't think displaying 3 fields would suppress display of records from preview!

Thanks|||Thanks. This is my 1st try at using CR and that didn't even cross my mine.

Steve

How to add 2 columns together

SQL2K
I have 2 columns, one is numeric and 2nd is TEXT or memo column.
I have been trying for the last few days with no success.
Here is the statement that I have been using:
UPDATE MYTABLE
SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
When I run it, I get data type error.
I would appreciate if someone please help me here.
Thx
Hi,
You need to convert the TEXT data type to Varchar for using normal updates.
UPDATE MYTABLE
SET MYMEMO = ltrim(rtrim(convert(char,PERCENTAGE,7)))+'
'+ltrim(rtrim(convert(varchar(8000),MYMEMO)))
Thanks
Hari
SQL Server MVP
"Mac" wrote:

> SQL2K
> --
>
> I have 2 columns, one is numeric and 2nd is TEXT or memo column.
> I have been trying for the last few days with no success.
> Here is the statement that I have been using:
>
> UPDATE MYTABLE
> SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
> When I run it, I get data type error.
> I would appreciate if someone please help me here.
> Thx
>
>
|||Hi,
Feedback below..
SELECT MAX(DATALENGTH(mymemo)) FROM mytable
-- If the above is < 8000
UPDATE MYTABLE
SET MYMEMO = RTRIM(LTRIM(STR(PERCENTAGE,7)))+' '+
CONVERT(VARCHAR(8000), MYMEMO)
GO
-- If it isn't < 8000 lookup UPDATETEXT clause in SQL Server BOL
Greg

How to add 2 columns together

SQL2K
--
I have 2 columns, one is numeric and 2nd is TEXT or memo column.
I have been trying for the last few days with no success.
Here is the statement that I have been using:
UPDATE MYTABLE
SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
When I run it, I get data type error.
I would appreciate if someone please help me here.
ThxHi,
You need to convert the TEXT data type to Varchar for using normal updates.
UPDATE MYTABLE
SET MYMEMO = ltrim(rtrim(convert(char,PERCENTAGE,7)))+'
'+ltrim(rtrim(convert(varchar(8000),MYMEMO)))
Thanks
Hari
SQL Server MVP
"Mac" wrote:
> SQL2K
> --
>
> I have 2 columns, one is numeric and 2nd is TEXT or memo column.
> I have been trying for the last few days with no success.
> Here is the statement that I have been using:
>
> UPDATE MYTABLE
> SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
> When I run it, I get data type error.
> I would appreciate if someone please help me here.
> Thx
>
>|||Hi,
Feedback below..
SELECT MAX(DATALENGTH(mymemo)) FROM mytable
-- If the above is < 8000
UPDATE MYTABLE
SET MYMEMO = RTRIM(LTRIM(STR(PERCENTAGE,7)))+' '+
CONVERT(VARCHAR(8000), MYMEMO)
GO
-- If it isn't < 8000 lookup UPDATETEXT clause in SQL Server BOL
Greg

How to add 2 columns together

SQL2K
---

I have 2 columns, one is numeric and 2nd is TEXT or memo column.

I have been trying for the last few days with no success.

Here is the statement that I have been using:

UPDATE MYTABLE
SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO

When I run it, I get data type error.

I would appreciate if someone please help me here.

ThxHi,

Feedback below..

SELECT MAX(DATALENGTH(mymemo)) FROM mytable
-- If the above is < 8000
UPDATE MYTABLE
SET MYMEMO = RTRIM(LTRIM(STR(PERCENTAGE,7)))+' '+
CONVERT(VARCHAR(8000), MYMEMO)
GO
-- If it isn't < 8000 lookup UPDATETEXT clause in SQL Server BOL

Greg

How to add 2 columns together

SQL2K
--
I have 2 columns, one is numeric and 2nd is TEXT or memo column.
I have been trying for the last few days with no success.
Here is the statement that I have been using:
UPDATE MYTABLE
SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
When I run it, I get data type error.
I would appreciate if someone please help me here.
ThxHi,
You need to convert the TEXT data type to Varchar for using normal updates.
UPDATE MYTABLE
SET MYMEMO = ltrim(rtrim(convert(char,PERCENTAGE,7)))
+'
'+ltrim(rtrim(convert(varchar(8000),MYME
MO)))
Thanks
Hari
SQL Server MVP
"Mac" wrote:

> SQL2K
> --
>
> I have 2 columns, one is numeric and 2nd is TEXT or memo column.
> I have been trying for the last few days with no success.
> Here is the statement that I have been using:
>
> UPDATE MYTABLE
> SET MYMEMO = STR(PERCENTAGE,7)+' '+MYMEMO
> When I run it, I get data type error.
> I would appreciate if someone please help me here.
> Thx
>
>|||Hi,
Feedback below..
SELECT MAX(DATALENGTH(mymemo)) FROM mytable
-- If the above is < 8000
UPDATE MYTABLE
SET MYMEMO = RTRIM(LTRIM(STR(PERCENTAGE,7)))+' '+
CONVERT(VARCHAR(8000), MYMEMO)
GO
-- If it isn't < 8000 lookup UPDATETEXT clause in SQL Server BOL
Greg