January 29, 2009 at 1:19 pm
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.
-------------------------------------------------------------------------------------
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 29, 2009 at 1:23 pm
Why not use IsNull for that?
sum(isnull(soi.ItemValue, 0))
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2009 at 1:37 pm
Hi, I tried:
(SELECT SUM(isnull(soi.ItemValue,0))
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteValue',
CVWinProb.CustomFieldValueInteger AS WinProbability,
QuoteValue still returns NULL?
Basically what I am trying to do is sum the ItemValue where a field 'Option' is false. When the value is true NULL is returned. When the value is false the ItemValue is correctly aggregated.
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 29, 2009 at 2:14 pm
QuoteValue still returns NULL?
The reason caused null value is not by ISNULL function, it might come with tables join.
Post you complete codes here, it may help.
January 29, 2009 at 2:18 pm
Code as requested.
SELECT
so.SalesOrderId AS BidNumber,
u.FirstName,
u.LastName,
so.EffectiveDate AS Date,
c.CustomerName AS Customer,
Countries.CountryId AS DestinationSite,
soi.ItemDescription,CVOption.CustomFieldValueBit AS 'SystemOption',
(SELECT SUM(isnull(soi.ItemValue,0))
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteValue',
CVWinProb.CustomFieldValueInteger AS WinProbability,
(SELECT SUM(soi.ItemValue * CVWinProb.CustomFieldValueInteger)/100
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteFactoredValue',
soi.ItemValue AS 'IncludesOptionValue',
SUM(soi.ItemValue * CVWinProb.CustomFieldValueInteger)/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 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
I have been experimenting so excuse the mess!
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 29, 2009 at 2:21 pm
I think richard is onto something here... without the entire query I'm guessing, but it looks like this could be some sort of correlated subquery directly in the subject line, but there's no join to make it correlated so it could just be returning null because of that...
-Luke.
January 29, 2009 at 2:25 pm
I can see three WHERE's and only one FROM. You can only have one WHERE per FROM.
SELECT...
FROM...
WHERE...
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
January 29, 2009 at 2:29 pm
Hi guys. I just had a play changing the joins. It did make a difference. Changing to
LEFT JOIN CustomValues AS CVOption
LEFT JOIN CustomFields AS CFOption ON CVOption.CustomField = CFOption.CustomField
AND CFOption.CustomFieldId = 'Option'
AND CFOption.CustomFieldTable = 'SalesOrderItems'
ON soi.SalesOrderItem = CVOption.RecordNumber
returned more NULLS.
Still a novice when it comes to code so any values returned are a bonus 🙂
Thanks,
Phil
Update: I have attached screen dump of sample output. Posting table structures would take me a while.
-------------------------------------------------------------------------------------
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 29, 2009 at 2:36 pm
ON so.SalesOrder = CVWinProb.RecordNumber
ON soi.SalesOrderItem = CVOption.RecordNumber
are you sure the logic here are correct?
By the way, the more left or right join you have, the more nulls you may have.
January 29, 2009 at 2:45 pm
Yes. The app allows users to create custom fields that are then stored in tables CustomFields, CustomValues. The Order Header (SalesOrders) has a custom field 'WinProb', the Order Detail (SalesOrderDetails) has a custom field 'Option'. They link via the record number. So WinProb record number = SalesOrders.SalesOrder. Option record number = SalesOrderItems.SalesOrderItem.
Maybe I have to live with the NULLS?
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 30, 2009 at 7:18 am
Hi Philip
The code you posted doesn't run, it generates the error message:
'Msg 8124, Level 16, State 1, Line 38
Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.'
If you comment out these two items from the SELECT list...
/*
(SELECT SUM(isnull(soi.ItemValue,0))
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteValue',
*/
CVWinProb.CustomFieldValueInteger AS WinProbability,
/*
(SELECT SUM(soi.ItemValue * CVWinProb.CustomFieldValueInteger)/100
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteFactoredValue',
*/
...then it runs.
Here's a sample data script, it would be right handy if you could put some more rows in and perhaps make the data a little more representative:
CREATE TABLE #SalesOrderItems (SalesOrder INT, SalesOrderItem INT, ItemDescription varchar(30), ItemValue MONEY)
INSERT INTO #SalesOrderItems (SalesOrder, SalesOrderItem, ItemDescription, ItemValue)
SELECT 1, 1, 'laser assemblers', 1.99
CREATE TABLE #SalesOrders (SalesOrderId INT, SalesOrder INT, Customer INT, CreatedUser VARCHAR(15), SystemType VARCHAR(1), EffectiveDate DATETIME, DueDate DATETIME)
INSERT INTO #SalesOrders (SalesOrderId, SalesOrder, Customer, CreatedUser, SystemType, EffectiveDate, DueDate)
SELECT 9, 1, 1, 'Philip Horan', 'Q', '06 dec 2008', GETDATE() UNION ALL
SELECT 9, 2, 1, 'Philip Horan', 'Q', '06 dec 2008', GETDATE()
CREATE TABLE #Customers (Customer INT, CustomerName VARCHAR(20), Country VARCHAR(15))
INSERT INTO #Customers (Customer, CustomerName, Country)
SELECT 1, 'MyBestCustomer', 'Poland'
CREATE TABLE #Countries (CountryId INT, Country VARCHAR(15))
INSERT INTO #Countries (CountryId, Country)
SELECT 1, 'Poland'
CREATE TABLE #Users (UserName VARCHAR(15), FirstName VARCHAR(15), LastName VARCHAR(15))
INSERT INTO #Users (UserName, FirstName, LastName)
SELECT 'Philip Horan', 'Philip', 'Horan'
CREATE TABLE #CustomValues (CustomField INT, RecordNumber INT, CustomFieldValueBit INT, CustomFieldValueInteger INT)
INSERT INTO #CustomValues (CustomField, RecordNumber, CustomFieldValueBit, CustomFieldValueInteger)
SELECT 1, 1, 1, 7
CREATE TABLE #CustomFields (CustomField INT, CustomFieldId VARCHAR(15), CustomFieldTable VARCHAR(15)) -- CustomField
INSERT INTO #CustomFields (CustomField, CustomFieldId, CustomFieldTable)
SELECT 1, 'WinProb', 'SalesOrders'
Now, where were we?
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
January 30, 2009 at 7:33 am
I think you are getting NULLs because the you don't have any records where value of CustomFieldValueBit is 0...
REPLACE this
(SELECT SUM(isnull(soi.ItemValue,0))
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteValue',
WITH
SUM( ( CASE WHEN CVOption.CustomFieldValueBit = 0 THEN ISNULL( soi.ItemValue, 0 ) ELSE 0 END ) ) AS 'QuoteValue',
The same way you have replace the other columns as well....
--Ramesh
January 31, 2009 at 2:07 am
Chris my code runs without error? What can I do to help? Let me explain further. The user may enter multiple custom fields on the app screens (SalesOrders / SalesOrderDetails / PurchaseOrders etc..). The custom field value is related to a specific screen (program). If they enter multiple fields that are require Yes/No then these values are all saved to CustomFieldValueBit in table CustomValues. So if you want to return values for a specific CustomField you have to do a little bit more work in the query:
--Sales Order Screen has multiple custom fields that are boolean Option + WinProb
--So to return only these values from table CustomValues I must do the following:
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
Not sure that helps....
Ramesh your code did the job.
(SELECT SUM(isnull(soi.ItemValue,0))
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteValue1',
SUM( ( CASE WHEN CVOption.CustomFieldValueBit = 0 THEN ISNULL( soi.ItemValue, 0 ) ELSE 0 END ) ) AS 'QuoteValue2',
Is there away to return just one column 'QuoteValue'?
I could make it a CTE? Then SELECT QuoteValue2 FROM CTE but there is probably a better way 🙂
Thanks for your efforts guys.
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 2:58 am
Guys my code now looks as follows:
WITH BidReport AS
(
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,
(SELECT SUM(isnull(soi.ItemValue,0))
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteValue1',
SUM( ( CASE WHEN CVOption.CustomFieldValueBit = 0 THEN ISNULL( soi.ItemValue, 0 ) ELSE 0 END ) ) AS 'QuoteValue2',
(SELECT SUM(soi.ItemValue * CVWinProb.CustomFieldValueInteger)/100
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteFactoredValue1',
SUM((CASE WHEN CVOption.CustomFieldValueBit = 0 THEN ISNULL (soi.ItemValue * CVWinProb.CustomFieldValueInteger,0)/100 ELSE 0 END)) AS 'QuoteFactoredValue2',
soi.ItemValue AS 'IncludesOptionValue',
SUM(soi.ItemValue * CVWinProb.CustomFieldValueInteger)/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 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
GROUP BY so.SalesOrderId, u.LastName, u.FirstName, so.EffectiveDate, c.CustomerName, Countries.CountryId,
soi.ItemDescription,soi.ItemValue, so.DueDate,CVWinProb.CustomFieldValueInteger,
CVOption.CustomFieldValueBit
)
SELECT BidNumber,FirstName,LastName,SystemOption,QuoteValue2,WinProbabilityPercent,QuoteFactoredValue2,IncludesOptionValue,FactoredValue,
Date,Customer,DestinationSite
FROM BidReport
I have attached screen dump of output.
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 3:46 am
Philip Horan (1/31/2009)
Chris my code runs without error? What can I do to help? Let me explain further. The user may enter multiple custom fields on the app screens (SalesOrders / SalesOrderDetails / PurchaseOrders etc..). The custom field value is related to a specific screen (program). If they enter multiple fields that are require Yes/No then these values are all saved to CustomFieldValueBit in table CustomValues. So if you want to return values for a specific CustomField you have to do a little bit more work in the query:
--Sales Order Screen has multiple custom fields that are boolean Option + WinProb
--So to return only these values from table CustomValues I must do the following:
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
Not sure that helps....
Ramesh your code did the job.
(SELECT SUM(isnull(soi.ItemValue,0))
WHERE CVOption.CustomFieldValueBit = 0) AS 'QuoteValue1',
SUM( ( CASE WHEN CVOption.CustomFieldValueBit = 0 THEN ISNULL( soi.ItemValue, 0 ) ELSE 0 END ) ) AS 'QuoteValue2',
Is there away to return just one column 'QuoteValue'?
I could make it a CTE? Then SELECT QuoteValue2 FROM CTE but there is probably a better way 🙂
Thanks for your efforts guys.
Phil.
Phil, I ran the code against a SQL2k server, my bad.
So are QuoteValue1 and QuoteValue2 supposed to generate the same values?
What about QuoteFactoredValue1 and QuoteFactoredValue2?
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
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply