[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

If statment in sql query

Posted on 2007-08-01
9
Medium Priority
?
249 Views
Last Modified: 2010-03-20
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
Comment
Question by:ljhodgett
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 19607750
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
 

Author Comment

by:ljhodgett
ID: 19607793
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
 
LVL 11

Expert Comment

by:Otana
ID: 19607820
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:ljhodgett
ID: 19607852
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
 

Author Comment

by:ljhodgett
ID: 19607861
Sorry,

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

Regards
Lee
0
 
LVL 2

Expert Comment

by:Sandeepratan
ID: 19607890
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
 
LVL 11

Accepted Solution

by:
Otana earned 2000 total points
ID: 19607907
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 19608042
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
 
LVL 27

Expert Comment

by:Zberteoc
ID: 19608331
No offense to anyone but the solution to problems like this is the use of ISNULL() function and not CASE WHEN.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

868 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