• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

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
0
ljhodgett
Asked:
ljhodgett
  • 3
  • 3
  • 2
  • +1
1 Solution
 
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;
0
 
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
0
 
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;
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.

 
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
0
 
ljhodgettAuthor Commented:
Sorry,

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

Regards
Lee
0
 
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
0
 
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
0
 
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;
0
 
ZberteocCommented:
No offense to anyone but the solution to problems like this is the use of ISNULL() function and not CASE WHEN.
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.

Join & Write a Comment

Featured Post

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.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now