?
Solved

SQL syntax error

Posted on 2007-08-01
9
Medium Priority
?
218 Views
Last Modified: 2010-03-20
Hi,

I am trying to join 2 columns togather to make one. I have been given the sql statement: -

SET NOCOUNT ON; SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, PartIdentity SSDNumber + Description as SSDNumber_Description FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;

but it comes up Incorrect syntax near '+'. What am I doing wrong please.

Many Thanks
Lee
0
Comment
Question by:ljhodgett
  • 4
  • 3
  • 2
9 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 19607547
Try this:

SET NOCOUNT ON; SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, [PartIdentity SSDNumber] + Description as SSDNumber_Description FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;
0
 

Author Comment

by:ljhodgett
ID: 19607556
Hi,

it comes up Invalid column name 'PartIdentity SSDNumber'. The two columns are SSDNumber and Description that I am trying to join together. I take it partIdentify is a function of sql is it?

Many Thanks
Lee
0
 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 19607557
SET NOCOUNT ON;
SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, PartIdentity SSDNumber + Description as SSDNumber_Description FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;


Here is the problem  PartIdentity SSDNumber
If these are column then place comma in between them or use + sign if you want concatenate...



SET NOCOUNT ON;
SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, PartIdentity, SSDNumber + Description as SSDNumber_Description FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;


or



SET NOCOUNT ON;
SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, Cast(PartIdentity as varchar) + cast(SSDNumber as varchar) + Description as SSDNumber_Description FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;

0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 11

Expert Comment

by:Ved Prakash Agrawal
ID: 19607563
No PartIdentity  is not a function....
0
 

Author Comment

by:ljhodgett
ID: 19607600
Hi,

Right, for some reason I was given partidentify for this. I haven't even got a column called part identify so I would forget about this. I have tried: -

SET NOCOUNT ON;
SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, SSDNumber + Description as SSDNumber_Description FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;

but it comes up : -

Msg 8114, Level 16, State 5, Line 2
Error converting data type varchar to numeric.

Best Regards
Lee
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 19607612
If SSDNumber is numeric, you should use this:

SET NOCOUNT ON;
SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, CAST(SSDNumber AS VARCHAR)+ Description as SSDNumber_Description FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;
0
 

Author Comment

by:ljhodgett
ID: 19607621
Hi,

I've managed to join them now using: -

SET NOCOUNT ON;
SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, cast(SSDNumber as varchar) + Description as SSDNumber_Description FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;

but I need it to put a space in between SSDNumber and Description as it places both together.

Many Thanks
Lee
0
 
LVL 19

Accepted Solution

by:
Rimvis earned 2000 total points
ID: 19607636
SET NOCOUNT ON;
SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, cast(SSDNumber as varchar) + ' ' + Description as SSDNumber_Description FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;
0
 

Author Comment

by:ljhodgett
ID: 19607727
Hi,

That works great thank you. I've posted a follow on question as it was sort of a new question but follows this one.

Best Regards
Lee
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

839 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