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

T-SQL How to Get Rid of NULL Through COALESCE But within CAST

Hello:

I need to put COALESCE(... , '') in the CAST clause below, so that NULLs are not returned.  

What's the syntax to do so?

CASE WHEN p.RowNumber = 1 THEN CAST(p.[SUMQTY] + n.[SUMQTY] AS VARCHAR(20)) ELSE '' END AS [Quantity Remaining]

Thanks!

TBSupport
0
TBSupport
Asked:
TBSupport
1 Solution
 
ravikantninaveCommented:
Try like this:
CASE WHEN p.RowNumber = 1 THEN CAST(COALESCE(p.[SUMQTY],0) + COALESCE(n.[SUMQTY],0) AS VARCHAR(20)) ELSE '' END AS [Quantity Remaining]
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If we're talking NULL's in SUMQTY, and assuming they are numeric, then...

CASE WHEN p.RowNumber = 1
   THEN CAST(ISNULL(p.[SUMQTY],0) + ISNULL(n.[SUMQTY],0) AS VARCHAR(20))
   ELSE '' END AS [Quantity Remaining]

If we're talking only two values then ISNULL will work.
COALESCE does it too, but can also handle more than two values.
0
 
PadawanDBAOperational DBACommented:
Coalesce is ANSI standard, so it has my vote =)
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now