SQL syntax error

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
ljhodgettAsked:
Who is Participating?
 
RimvisCommented:
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
 
RimvisCommented:
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
 
ljhodgettAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
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
 
Ved Prakash AgrawalDatabase Consultant/Performance ArchitectCommented:
No PartIdentity  is not a function....
0
 
ljhodgettAuthor Commented:
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
 
RimvisCommented:
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
 
ljhodgettAuthor Commented:
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
 
ljhodgettAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.