Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 956
  • 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
 
PadawanDBACommented:
Coalesce is ANSI standard, so it has my vote =)
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

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