Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sql query more than two tables

Posted on 2009-05-19
9
Medium Priority
?
197 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 143

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 27

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 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 143

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 143

Expert Comment

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

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

596 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