Solved

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

Posted on 2014-01-24
3
883 Views
Last Modified: 2014-01-25
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
Comment
Question by:TBSupport
3 Comments
 
LVL 4

Accepted Solution

by:
ravikantninave earned 500 total points
ID: 39807682
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39807684
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
 
LVL 10

Expert Comment

by:PadawanDBA
ID: 39807709
Coalesce is ANSI standard, so it has my vote =)
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
the whoisactive update 12 39
MS SQL - Rotating Values in SQL 9 53
Sql Query with datetime 3 12
Replace Dates in query 14 18
Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

895 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now