Solved

sql query more than two tables

Posted on 2009-05-19
9
189 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
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…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

20 Experts available now in Live!

Get 1:1 Help Now