January 31, 2009 at 4:06 pm
Hi Chris, yes the same values. The query is for a report. Currently the report returns a blank for NULL's, I thought it would look better if it displayed 0.00.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 31, 2009 at 10:42 pm
Hi Philip,
To get rid of the null values, one way is to replace left join(s) with inner join(s).
If the business logic requires left join, then you may have to apply second way, which format like this:
select ..., isnull(QuoteValue,0),...
from
(
--here is your original query
) as aView
By the way, I don't think it is necessary to have isnull in your original query if you apply the second method. Try just say SUM(soi.ItemValue) instead.
Good Luck.
Richard
February 1, 2009 at 6:04 am
Thanks Richard.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
February 2, 2009 at 8:09 am
Philip Horan (1/31/2009)
Hi Chris, yes the same values. The query is for a report. Currently the report returns a blank for NULL's, I thought it would look better if it displayed 0.00.Many Thanks,
Phil.
Give this a whirl Phil...
SELECT
so.SalesOrderId AS BidNumber,
u.FirstName AS FirstName,
u.LastName AS LastName,
so.EffectiveDate AS Date,
c.CustomerName AS Customer,
Countries.CountryId AS DestinationSite,
soi.ItemDescription,
CVOption.CustomFieldValueBit AS 'SystemOption',
CVWinProb.CustomFieldValueInteger AS WinProbabilityPercent,
SUM(CASE WHEN ISNULL(CVOption.CustomFieldValueBit, 0) = 0 THEN isnull(soi.ItemValue,0) ELSE 0 END) AS [QuoteValue],
SUM(CASE WHEN ISNULL(CVOption.CustomFieldValueBit, 0) = 0 THEN ISNULL(soi.ItemValue, 0) * ISNULL(CVWinProb.CustomFieldValueInteger, 0)/100 END) AS [QuoteFactoredValue],
soi.ItemValue AS 'IncludesOptionValue',
SUM(soi.ItemValue * ISNULL(CVWinProb.CustomFieldValueInteger, 0))/100 AS 'FactoredValue',
so.DueDate AS OrderExpectedDate
FROM SalesOrderItems AS soi
INNER JOIN SalesOrders AS so ON soi.SalesOrder = so.SalesOrder
INNER JOIN Customers AS c ON so.Customer = c.Customer
INNER JOIN Countries AS Countries ON c.Country = Countries.Country
INNER JOIN Users AS u ON so.CreatedUser = u.UserName
LEFT OUTER JOIN CustomValues AS CVWinProb
INNER JOIN CustomFields AS CFWinProb ON CVWinProb.CustomField = CFWinProb.CustomField
AND CFWinProb.CustomFieldId = 'WinProb'
AND CFWinProb.CustomFieldTable = 'SalesOrders'
ON so.SalesOrder = CVWinProb.RecordNumber
LEFT OUTER JOIN CustomValues AS CVOption
INNER JOIN CustomFields AS CFOption ON CVOption.CustomField = CFOption.CustomField
AND CFOption.CustomFieldId = 'Option'
AND CFOption.CustomFieldTable = 'SalesOrderItems'
ON soi.SalesOrderItem = CVOption.RecordNumber
WHERE so.SystemType = 'Q'
AND so.EffectiveDate BETWEEN '01 dec 2008' AND '08 dec 2008' --@StartDate AND @EndDate
AND c.CustomerName NOT LIKE 'FERNAU%'
GROUP BY so.SalesOrderId, u.LastName, u.FirstName, so.EffectiveDate, c.CustomerName, Countries.CountryId,
soi.ItemDescription,soi.ItemValue, so.DueDate, CVWinProb.CustomFieldValueInteger, CVOption.CustomFieldValueBit
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 2, 2009 at 8:20 am
Philip Horan (1/29/2009)
Hi. I have would like to substitute NULL in query output with 0 (if possible).I have tried:
(SELECT
SUM(CASE WHEN soi.ItemValue = NULL THEN 0 ELSE soi.ItemValue END)
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteValue',
= ISNULL returns error
An expression of non-boolean type specified in a context where a condition is expected, near 'ISNULL'
If I run as code then NULL is still returned not 0. I also tried
SUM(CASE WHEN soi.ItemValue >= 0 THEN soi.ItemValue ELSE 0 END)
The above also returns NULL.
Am I barking up the wrong tree?
Thanks,
Phil.
While you have to use the = sign to set a value to NULL, you cannot use the = sign to compare a value against NULL.
You have to use
(SELECT
SUM(CASE WHEN soi.ItemValue IS NULL THEN 0 ELSE soi.ItemValue END)
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteValue',
February 2, 2009 at 11:48 am
Good work guys. Chris your code worked a treat.
Many thanks to all those who posted I appreciate your efforts.
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply