Solved

sql query more than two tables

Posted on 2009-05-19
9
190 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
 
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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Isolation level in SQL server 3 47
configure service broker on all databases 2 77
SSRS page break on parameter values 1 38
SQL Query with Sum and Detail rows 2 42
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now