If statment in sql query

Hi,

This is a bit of a follow on question to my previous post on a sql statement. I currently have the following statement: -

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;

The area of concern is where I join the two columns together one being the SSDNumber and the other being the Description. The problem I have got is that if the SSDNumber is blank then I want to use routingstage almost as if an if statement needs to be used.

I know its not the correct syntax but an example,

If SSDNumber =  then
cast(SSDNumber as varchar) + ' ' + Description as SSDNumber_Description
else
cast(Routingstage as varchar) + ' ' + Description as SSDNumber_Description
end if

Many Thanks
Lee
ljhodgettAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OtanaCommented:
Try this:
SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, CASE WHEN cast(SSDNumber as varchar) = '' THEN cast(Routingstage as varchar) + ' ' + Description as SSDNumber_Description
ELSE cast(SSDNumber as varchar) + ' ' + Description END as SSDNumber_Description FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;
ljhodgettAuthor Commented:
Hi,

I'm afraid it comes up with the following message: -

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'as'.


Best Regards
Lee
OtanaCommented:
I see, small mistake, this should be better:

SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, CASE WHEN cast(SSDNumber as varchar) = '' THEN cast(Routingstage as varchar) + ' ' + Description ELSE cast(SSDNumber as varchar) + ' ' + Description END as SSDNumber_Description FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ljhodgettAuthor Commented:
Hi,

Thank you for that. Unfortunatly I've made a bit of an error in my original question. I have placed the following code: -

SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, PartIdentity,
CASE WHEN cast(PartIdentity as varchar) = 'NULL' THEN
      cast(SSDNumber as varchar) + ' ' + Description
ELSE
      cast(PartIdentity as varchar) + ' ' + Description
END
as SSDNumber_Description

FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber;

What I need to do is when the PartIdentity is NULL then use the SSDNumber instead.  cast(PartIdentity as varchar) + ' ' + Description  works but cast(SSDNumber as varchar) + ' ' + Description doesn't.

I've tried the condition '', NULL and 'NULL' with no joy.

Best regards
Lee
ljhodgettAuthor Commented:
Sorry,

When I say it doesn't work it just places NULL into the SSDNumber_Description  column.

Regards
Lee
SandeepratanCommented:
SELECT TestRigID,SSDNumber, Description, CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,
bPCBTester,bDriveTester, bObsolete,
case when ltrim(rtrim(cast(SSDNumber as varchar))) = '' then cast(RoutingStage as varchar)
else cast(SSDNumber as varchar) end + ' ' + Description as SSDNumber_Description
FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber
OtanaCommented:
SELECT TestRigID,SSDNumber, Description,CalibrationDue,bUserLock,DocNum,JobReference,RoutingStage,bPCBTester,bDriveTester, bObsolete, PartIdentity,
CASE WHEN PartIdentity IS NULL THEN
      cast(SSDNumber as varchar) + ' ' + Description
ELSE
      cast(PartIdentity as varchar) + ' ' + Description
END
as SSDNumber_Description

FROM Admin_SSDNumber WHERE bLogicalDelete=0 ORDER BY SSDNumber

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
Instead of CASE statement you can use ISNULL() function:

ISNULL(PartIdentity, SSDNumber);

this function returns the first argument if is not NULL and the second else (if the first is NULL). Both argument have to be of the same type, if not CAST one of them to the other's type. I asume both PartIdentity and SSDNumber are integers. If not you have to cast one of them inside the function.

Your statement becomes:

SELECT
       TestRigID
      ,SSDNumber
      ,Description
      ,CalibrationDue
      ,bUserLock
      ,DocNum
      ,JobReference
      ,RoutingStage
      ,bPCBTester
      ,bDriveTester
      ,bObsolete
      ,PartIdentity
      ,cast(ISNULL(PartIdentity,SSDNumber) AS varchar) + ' ' + Description AS SSDNumber_Description
FROM
      Admin_SSDNumber
WHERE
      bLogicalDelete=0
ORDER BY
      SSDNumber;
ZberteocCommented:
No offense to anyone but the solution to problems like this is the use of ISNULL() function and not CASE WHEN.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.