sql query more than two tables

I am trying to run a report in Crystal that looks at two Databases, Sage and SQL.

I need to link a text field in Sage to a Filed in SQL during the creation of the Crystal Report which is a unique ID.

I therfore thought I would create the uniqe ID by Concatentating two Fileds and Creatiing a new column then linking that filed in the crystal query. I found out that one of the fileds i was trying to Concat was an Interger data type and would not go over to the Newly created column/filed.

SO I though I would try to create text columns and update with part of the unique id and then CONcat that.

ALTER TABLE Tours
ADD NewColumn1 text;
ALTER TABLE Tours
ADD NewColumn2 text;
UPDATE `Tours` SET `NewColumn1` = `TourSerialNo`;
SELECT Tourid + ' ' + NewColumn1 AS NewColumn2
FROM Tours

Have I gone barking mad?
LVL 1
Envisage-TechAsked:
Who is Participating?
 
Chris LuttrellSenior Database ArchitectCommented:
oh, you posted while I was typing. (and that was supposed to be "AS NewColumn2" not "and NewColumn2")
then you want to update the Tours table again.


Update Tours
SET NewColumn2 = convert(varchar,Tourid)+' '+NewColumn1;

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to "concat" in sql server a varchar and a numerical value, you have to force/cast the numerical value into varchar, for example:
SELECT cast(Tourid as varchar(20)) + ' ' + NewColumn1 AS NewColumn2

Open in new window

0
 
Envisage-TechAuthor Commented:
Thank You that seems to work nicely , however it is not putting the data into the NewColumn2 all I am seeing there is NULL values.

Can you help further?

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Chris LuttrellSenior Database ArchitectCommented:
Not sure why you added NewColumn2 since you are just concatinating values and aliasing as NewColumn2, no need for the actual column.  Otherwise, as angelIII has said, you have to cast or convert the numeric value to a string to concat with another string.  He showed you cast, here is convert:
SELECT convert(varchar,Tourid)+' '+NewColumn1 and NewColumn2

Open in new window

0
 
Envisage-TechAuthor Commented:
Thanks , Making sense and getting there now however I get the following error when running the command (slight changed)

Update Tours
Set NewColumn2 = convert(varchar(20),TourSerialNo)+''+ TourID;


Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>String or binary data would be truncated.
that means that the field NewColumn2 is too small for the data. you have to review the length of it, and eventually make sure the UPDATE does not try to put a value too long in there...
0
 
Envisage-TechAuthor Commented:
CRACKED IT!

Thank you just needed to make the varchar able to take a longer value.

Thank You very Muc
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
glad we could help!
0
 
Chris LuttrellSenior Database ArchitectCommented:
Thanks, glad to help, you should have split this and given angelIII credit also though.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.