UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Coalesce() With Nullif, Data Type Error, Any Versions    
 
   
CaptainMilly
post Dec 2 2019, 11:51 AM
Post#1



Posts: 153
Joined: 13-August 13



Hi all

I am running some old tutorials from Celko's "Advanced SQL programming" and encoutered an error which made me doubt I understand the tutorial.

Test table definition:

CODE
CREATE TABLE tut.Customers(
custNo INTEGER NOT NULL,
purchaseNo INTEGER NOT NULL,
category CHAR(1) CHECK(Category in ('A','B','C')),
amt DECIMAL(8,2) NOT NULL);


Inserted test data:

CODE
INSERT INTO tut.customers(custNo, PurchaseNo, category,amt)
VALUES
(1,2,'A',1),
(1,2,'A',1),
(3,3,'A',1),
(2,5,'B',2),
(1,2,'B',2),
(1,2,'B',2),
(2,5,'C',3),
(5,1,'C',3),
(7,9,'C',3)


Test query to demonstrate use of COALESCE with NULLIF for summing things:

CODE
SELECT SUM(amt),
        (SUM(amt)-SUM(COALESCE(NULLIF(category,'B'),amt))) AS CatB,
        (SUM(amt)-SUM(COALESCE(NULLIF(category,'C'),amt))) AS CatC
FROM tut.Customers


As far as I understand the query he claims should get the total ammount from whole table, then find the total ammount without the specified category in NULLIF (filtered by fact that sum ignores nulls), then you end up with the difference sum for the required category.

but if I run the query as in his book, I get an error "conversion of varchar to numeric"

I checked the definitions of the functions again.

NULLIF(var1,var2) definition: if var1 = var2, then returns NULL, if var1=/= var2 then returns var1.
COALESCE (var12,...,var22) returns the first not null value.

The results of this is: rows where the category does not equal 'B', I get return of 'A' or 'C', which means NULLIF returns a VARCHAR, not NULL. So COALESE resulting table (if it ran) for the SUM() with COALESE would look like this:

CODE
'A',
'A',
'A',
2,
2,
2,
'C',
'C',
'C',


The error would make sence if it ocured for the SUM() because you can't sum categories.

However, the point at where I get the data type conversion error does not have SUM() function:

CODE
SELECT COALESCE(NULLIF(category,'B'),amt) from tut.customers


Why would COALESCE care that there are different types of data in the result?
Why does my "thought" table include categories and not just ammounts for the sum?

What am I missing here?

Thank you!
milly
Go to the top of the page
 
BruceM
post Dec 3 2019, 11:17 AM
Post#2


UtterAccess VIP
Posts: 7,993
Joined: 24-May 10
From: Downeast Maine


I'll take a shot at it since nobody else has, although to tell the truth I have been monitoring the post because I was curious about the answer.

Apparently in SQL Server there is something known as data type precedence. int has a higher precedence than text types, so the COALESCE expression casts everything as the highest precedence data type. More here. The Microsoft documentation for COALESCE includes a comment about data type precedence, but doesn't really expand on the subject to explain how an error could result.

To get the expression to work you would need to CAST (or CONVERT) the integer value as a text type.
Go to the top of the page
 
CaptainMilly
post Dec 3 2019, 01:15 PM
Post#3



Posts: 153
Joined: 13-August 13



Thank you for the explanation Bruce. I guess because it is returning a single column, it has to return a single type of data, so something needs to be converted to other data type.


However, even if there wasn't a data type error, I still don't get how his query would work. It really does not make sense to me. The list coalesce should return would have to be nulls for category specified in NULLIF and amount for other categories. However, since NULLIF returns first variable I end up with list of categories mixed with amounts for the category specified.
CODE
'A',
'A',
'A',
2,
2,
2,
'C',
'C',
'C',


There are a few steps missing here, had to add 2 Nullif and 2 Cast to fix:

CODE
SELECT    SUM(CAST(NULLIF((COALESCE(NULLIF(category,(NULLIF(category,'B'))),CAST(amt AS VARCHAR))),'B') AS DECIMAL)) FROM tut.Customers


1. NULLIF(category,'B') gets the other categories
2. NULLIF(category,(NULLIF(category,'B')) converts other categories to null, keeps category B
3. (COALESCE(NULLIF(category,(NULLIF(category,'B'))),CAST(amt AS VARCHAR))) selects ammount for other categories, because now they are null, keeps B, cast to avoid type conversion error
4. (NULLIF((COALESCE(NULLIF(category,(NULLIF(category,'B'))),CAST(amt AS VARCHAR))),'B') keeps ammount which we found for other categories and converts 'B' to null
5. final CAST to convert to numeric value
6. sum!

sample data change by the steps:

CODE
(1)    (2)    (3)    (4)
A    NULL    1.00    1.00
A    NULL    1.00    1.00
A    NULL    1.00    1.00
NULL    B    B    NULL
NULL    B    B    NULL
C    NULL    3.00    3.00
NULL    B    B    NULL
C    NULL    3.00    3.00
C    NULL    3.00    3.00




Interesting way to show how to use nullif, but at this point why not use subquery with where clause? Could not find online book correction notice anywhere published for this query, so was confused why is it completely different in the book's sample.

milly
Go to the top of the page
 
BruceM
post Dec 3 2019, 01:45 PM
Post#4


UtterAccess VIP
Posts: 7,993
Joined: 24-May 10
From: Downeast Maine


QUOTE
I guess because it is returning a single column, it has to return a single type of data,

I'm not quite sure I follow you, but according to the Microsoft documentation of COALESCE the expression "Returns the data type of expression with the highest data type precedence". It is the way COALESCE works.

QUOTE
However, since NULLIF returns first variable I end up with list of categories mixed with amounts for the category specified.

That's the result I would have expected with the NullIf(category,'B') expression. That NullIf returns Null if the category is B, so COALESCE moves on to the next value (amt). For any category other than 'B', no match, so the first value in NullIf, as you said.

I don't think I will be able to sort out your expression with the various NullIf and CAST statements. I tried for a while, but I would need to draw out a flow chart to keep track of it all, I think.

I have a sense you are taking the long way around, although I am unclear about your objective.
Go to the top of the page
 
CaptainMilly
post Dec 3 2019, 01:59 PM
Post#5



Posts: 153
Joined: 13-August 13



Hi Bruce

Edit: My goal is just to learn some SQL from an old book. The sample I found did not work, and i could not find correction/explanation online so decided to ask for help.

I did solve it in the end (as in prev comment), but it is quite convoluted way to just say 'where' - maybe it is the age of the book... Will teach me to go through old tutorials!
This post has been edited by CaptainMilly: Dec 3 2019, 02:02 PM
Go to the top of the page
 
BruceM
post Dec 3 2019, 03:46 PM
Post#6


UtterAccess VIP
Posts: 7,993
Joined: 24-May 10
From: Downeast Maine


I am fairly recent to SQL Server, and have not looked much at older versions, but I do know that there have been significant changes including new functions. It may be that along the way some older techniques were changed or deprecated. I do agree that you seemed to be taking a circuitous route, but at least there's lots of scenery when you do that smile.gif
Go to the top of the page
 
DougY
post Yesterday, 06:16 PM
Post#7


Utterly Abby-Normal
Posts: 15,774
Joined: 30-June 04
From: Seattle, WA [USA]


You are correct. The code you posted is trying to combine char (category) and decimal (amt).
There are other, more common ways to get the dataset you want to get from the table. It sounds like you worked your way through it, but if you haven't, please chime in and we'll take a look at it with you.

Best of success with learning SQL!


--------------------
- Doug

When I said the program was fool proof, I hadn’t realized there were so many fools.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th December 2019 - 08:34 AM