Link to home
Start Free TrialLog in
Avatar of ljhodgett
ljhodgett

asked on

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
Avatar of Rimvis
Rimvis
Flag of Lithuania image

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;
Avatar of ljhodgett
ljhodgett

ASKER

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
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;

No PartIdentity  is not a function....
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
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;
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
ASKER CERTIFIED SOLUTION
Avatar of Rimvis
Rimvis
Flag of Lithuania image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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