Solved

sql query more than two tables

Posted on 2009-05-19
9
191 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:Envisage-Tech
  • 3
  • 3
  • 3
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24426907
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
 
LVL 1

Author Comment

by:Envisage-Tech
ID: 24429054
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24429065
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 26

Accepted Solution

by:
Chris Luttrell earned 500 total points
ID: 24429083
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
 
LVL 1

Author Comment

by:Envisage-Tech
ID: 24429158
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24429169
>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
 
LVL 1

Author Comment

by:Envisage-Tech
ID: 24429188
CRACKED IT!

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

Thank You very Muc
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24429197
glad we could help!
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 24429282
Thanks, glad to help, you should have split this and given angelIII credit also though.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question