I am using SQL server 2000.
While using query analyzer I am facing problem.
If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
Please give me solution as soon as possible, a kind request.
Facing problem for the below mentioned query:-
select IsD.ItemCode,
case
when sum(IsD.IssuedQty) is null then 0
else sum(IsD.IssuedQty)
end as IssuedToday
from Inv_IssueMaster IsM, Inv_IssueDetail IsD
where IsM.IssueNo=IsD.IssueNo
group by IsD.ItemCode
In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.use the CASE operator for these columns where you need conditional values.
Hope this helps.|||Please give me solution as soon as possible, a kind request.please show your query|||I am using SQL server 2000.
While using query analyzer, I am facing problem.
If a query has group by clause and if that query is not fetching any record (i.e. query is returning nothing), then in this situation, I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
Please give me solution as soon as possible.
Facing problem for the below mentioned query:-
select IsD.ItemCode,
case
when sum(IsD.IssuedQty) is null then 0
else sum(IsD.IssuedQty)
end as IssuedToday
from Inv_IssueMaster IsM, Inv_IssueDetail IsD
where IsM.IssueNo=IsD.IssueNo
group by IsD.ItemCode
In the above mentioned query, datatype of IssuedQty is int and for ItemCode it's varchar.|||I want zero to be displayed where datatype of field is integer and "-" if datatype of field is varchar.
I'm having trouble understanding your objective.
You have 2 columns from 2 tables, the 2nd is 0 to many relationship: first Varchar, 2nd Int
If the input is:
A 10, 20, 25
B null
C 15
You want results of
A 55
- 0
C 15
Is that correct? Why are you saying you want to check datatype
Note: Are you aware of the "ISNULL" function? It's a poor-man's DECODE (Oracle's powerhouse fuction).
I'm wondering if your question about datatype was just a confusion and this is all you want (I took the liberty of adding an outer join in case you have no detail records, you may still want to show a zero).
SELECT IsD.ItemCode,
sum(isnull(IsD.IssuedQty,0)) as IssuedToday
FROM Inv_IssueMaster IsM
LEFT OUTER JOIN Inv_IssueDetail IsD
ON IsM.IssueNo=IsD.IssueNo
GROUP BY IsD.ItemCode
This would return:
A 55
B 0
C 15
If you really want the "-" to be output in the first column, then this would work:
SELECT
CASE WHEN IssuedToday = 0 then '-'
ELSE SubQry.ItemCode end as ItemCode,
IssuedToday
FROM
(
SELECT IsD.ItemCode,
sum(isnull(IsD.IssuedQty,0)) as IssuedToday
FROM Inv_IssueMaster IsM
LEFT OUTER JOIN Inv_IssueDetail IsD
ON IsM.IssueNo=IsD.IssueNo
GROUP BY IsD.ItemCode
) SubQry
This will return:
A 55
- 0
C 15|||select IsD.ItemCode
, coalesce(sum(IsD.IssuedQty),0) as IssuedToday
from Inv_IssueMaster IsM
inner
join Inv_IssueDetail IsD
on IsD.IssueNo = IsM.IssueNo
group
by IsD.ItemCodethe only way that this query "is not fetching any record (i.e. query is returning nothing)" is when there are no rows in the Inv_IssueMaster table, which doesn't seem likely|||select IsD.ItemCode
, coalesce(sum(IsD.IssuedQty),0) as IssuedToday
from Inv_IssueMaster IsM
inner
join Inv_IssueDetail IsD
on IsD.IssueNo = IsM.IssueNo
group
by IsD.ItemCodethe only way that this query "is not fetching any record (i.e. query is returning nothing)" is when there are no rows in the Inv_IssueMaster table, which doesn't seem likely
Interesting use of COALESCE instead of ISNULL. Somehow I sense that I'm unaware of some subtlity. Partell.:shocked: (eagerly awaiting lesson)
As for no records in the Master - that would normally be true in a typical table structure, but it is possible to set up such a relationship. It would be a strange and badly normalized design, but SQL would allow it.
For example; let's say a school has the guy's SSN for student's that earn money from them and instead of linking the payment file into the student-ID, they have to link it to the SSN because the payment file doesn't have a "Student ID" column. They should create an intermediate XREF table, but they could also get lazy and just add the SSN to the STUDENT table. So this report would show "-" in the SSN column when there isn't one.
Somehow; given the OP's inconclusive wording of the requirement I'm wondering if that's really what s/he ment though. I think his problem is solved and s/he may or may not return to clarify.|||Interesting use of COALESCE instead of ISNULL. Somehow I sense that I'm unaware of some subtlity. Partell.:shocked: (eagerly awaiting lesson)interesting? how about standard sql ;)
coalesce is standard, isnull isn't
perhaps too subtle...|||interesting? how about standard sql ;)
coalesce is standard, isnull isn't
perhaps too subtle...
Thanks for the response.
ISNULL is just a trimmer version (ie: smaller Object) of COALESCE so is likely a little faster. However it's propietary so not portable.
So; sounds like a combination of habit, your need for portability, and maybe a general distain for propietary deviations from ANSI. Not some performance or reliability (within SQL Server) trick.
I'll have to rethink some of those things if I ever write something that has to be portable. For now, in a SQL Server only shop, I'll opt for using the trimmer version, considering it gets used so many times.
I see your point and it's a good one. I'm sure I'll continue using ISNULL, and when using Oracle I'll use DECODE, but I'll better appreciate why a Sr. SQL Consultant might do otherwise.
(Quite honestly; I had forgotten that ISNULL wasn't standard. The danger of being a single shop guy - need to get out more. Ergo my sudden appearence on this forum. :) )|||COALESCE blows the socks off ISNULL when there are more than two terms in the list ;)
likely faster? you cannot say that without extensive benchmarking, can you
and what's a "smaller Object" -- is that some kind of object-oriented thingy? in which context would you need to measure this object size?
i wasn't aware that the compiled execution plan would actually be bigger for one function in a query as compared to another|||COALESCE blows the socks off ISNULL when there are more than two terms in the list ;)
likely faster? you cannot say that without extensive benchmarking, can you
and what's a "smaller Object" -- is that some kind of object-oriented thingy? in which context would you need to measure this object size?
i wasn't aware that the compiled execution plan would actually be bigger for one function in a query as compared to another
Yes, object oriented thingy. General overhead thing. Use the minimum.
Extensive benchmarking - I'd rather just take an educated guess and say "probably".
Why would MS make it if not to optimize things a little? If they just did it for readability, and they actually made it slower, well ... that's just very unlikely. If I said "probably blow's it's socks off performance wise", now that would be an irresponsible statement. I'd stand by my original statmenet and say that common sense (and many years of programming experience) suffice for a "probably".
"more than two terms in the list", well, that would be a different capability, more in line what that extra code is ment to handle. I think "blow socks off" is a misnomer. It's more binary than that, like SELECT vs. SET, since ISNULL does not accept multiple terms.
Anyway; I didn't mean to start a shouting match. I acknowledge that you're probably far senior to myself in such matters and was ernestly looking for an insight. Your point is well taken and I believe it's a valid one.
Cheers :beer:|||Why would MS make it if not to optimize things a little? oh! oh! i know this one! to be compatible with sql standards, maybe?
but thanks for the followup, and rest assured, i wasn't shouting
:cool:|||oh! oh! i know this one! to be compatible with sql standards, maybe?
but thanks for the followup, and rest assured, i wasn't shouting
:cool:
See, now I'm curious about the overhead.
Indeed, why would MS deviate from ANSI standard on this?
My general project management experience tells me someone made a hit-list of "how to optimize, how to simplify". However; was it part of a marketing conspiracy to prevent SQL Server shops from migrating out? haha.
If the former, then the gain would have to be substantial to justify it - or at least very easy to implement with a modest gain.
Guess someone with an IN to the MS development team, or a very in depth book on SQL Server "improvements" would have to answer that one. Or like you say, develop a benchmark. Honestly; I'm not that curious.|||Another deviation from ANSI SQL is the GROUP BY ALL (see BOL). Works OK if all you need to deal with is SQL Server.|||"more than two terms in the list", well, that would be a different capability, more in line what that extra code is ment to handle. I think "blow socks off" is a misnomer.i should have been a bit more explicit
COALESCE is easier to write, to understand, and to maintain ( = "blows the socks off") than a series of nested ISNULLs, when what you need to do is select the first non-null value in a series of values|||Which also means, thaty you would have to use ISNULL many time to mimic the COALESCE function, so performance point goes out the window
Also, I stay as ANSI as possible to because I work on many different database platforms...it's tough enough changing gears as it is
Man I hate DB2 OS/390 a lot these days|||ooohhhh sigh.....
always stick to the ANSI-92 as close as possible. My hands are not clean in this matter. I have wrtten some proprietary junk.
screw having to port something to oracle or mysql or whatever. ask people what they think about moving to MS SQL 2005 that have a bunch of *= and =* in their code instead of nice and proper OUTER JOIN statements.|||I absolutely love COALESCE!!! My shop here (and pretty much all new SQL coders I have seen in the past 4 years) take to ISNULL as if it were a heated jockstrap on a backpacking trip to Eagle Lake (Sierras) in early March!!!
I just love COALESCE. If I wasn't married, I would marry COALESCE. I have cleaned up much stacked, squeezed, haywired, and Elmer's Glued ISNULL nests with a single COALESCE that, in it's own sublime beauty, brings tears to the eyes of the SQL youngsters.
Put simply, I feel strongly that COALESCE is cleverer than ISNULL. I think COALESCE could whip ISNULL's butt in a towel-snapping fight, and in most recognized games of chance available in the world today.
I had no idea that ISNULL is not ANSII standard though, so I am thrilled to have read this thread. It's really about time that COALESCE got the press it deserves.
If I have not mentioned it, it is, in fact, probably my favorite verb in MS SQL.|||Guess someone with an IN to the MS development team, or a very in depth book on SQL Server "improvements" would have to answer that one.
Friend of friend was a dev on the sql server team <wink>. Didn't code up the ISNULL feature though, so can't speak with authority on its origins :)
Indeed, why would MS deviate from ANSI standard on this?
I am pretty sure that ISNULL did not come about because of some MS conspiracy to try to get people to move away from existing ANSI standards.
More likely is that ISNULL was the pet feature of some long lost PM. Fair bet that he/she wasn't even aware of the existence of COALESCE when they came up with it. :)|||I don't know of any benchmarks showing one is faster than the other. The difference would be less than negligible anyway.
Why did MS create ISNULL()? I don't know that they did. It was probably a part of SYBASE before Microsoft got a hold of it. Also suspicious that ISNULL() in MSSQL operates completely differently than ISNULL() in MSAccess, making it even more unlikely that it was Microsoft's idea.
If you have a choice between two methods and one is ANSI while the other is not, use the ANSI standard.|||Love finding such passion. Tall dude, careful what you wish for (marriage wise), lol.
Oracle has "nvl", SQL Server "isnull".
I'm opting for using stuff as appropriate. COALESCE just seems more like a parser of a string of potentially null values than a simple replacment converter. Like a conditional branch statement that has a conversion method as a side benifit. Just my impression.
Using it with a single arguement just seemed odd to me, but then, I've only used Microsoft and Oracle platforms.
Nesting ISNULL does seem messy. Wattya wanna bet (ok, nothing over 50 cents) that COALESCE actually uses ISNULL internally. Would certianly make sense, programming wise. Why repeat all that coding?
Anyway, hat's off to the "ANSI-92 Only" camp. I sure can't keep up with it.
Shame when we get spoiled with some Propietary feature. Main one I sorely miss is the PL SQL support for a "Cursor Loop". All the cursor's fields get pseudo created with a scope only within the loop, no extra definition required, and more importantly all the definitions and naming inhereted at compile time and therefore self-maintaining if someone comes along and changes a column definition. Yeah; what a time and code saver! I'll deviate any day for that one. I'm just an ignorant "only solve what's in front of me" guy so I have no idea if that's in the ANSI standard or not - or if SQL Server 2005 has something similar.|||SQL Server supports cursors?
I'm not the best one to answer that one...as the Great Sage has been saying for incalcuable millennia, "Nature Abhors A Cursor" (or something to that effect, anyway).
I have managed to use only two in the past 4 years...and both of those to demonstrate why they should NOT be used ;)
yeah, yeah, yeah...sometimes they are necessary (or it's not worth it to do it correctly for a one-off cursor solution), but I like to pretend they don't exist. Similar to the methodology I use to address the existence of the devil himself.|||COALESCE just seems more like a parser of a string of potentially null values than a simple replacment converter.
Nesting ISNULL does seem messy. Wattya wanna bet (ok, nothing over 50 cents) that COALESCE actually uses ISNULL internally. Would certianly make sense, programming wise. Why repeat all that coding?
Anyway, hat's off to the "ANSI-92 Only" camp. I sure can't keep up with it.
I wouldn't muddy ISNULL() the function with the concept of something being null. COALESCE and ISNULL are funtionally identical at the conceptual level for evaluating the first argument. COALESCE is not a parser by any stretch of the imagination. COALESCE just has infinantly more flexibility with a very small perceived performance hit.
Assume you have:
ISNULL(myScalarVal, myScalarResult)
and
COALESCE(myScalarVal, myScalarResult)
logically speaking, both functions must perform the exact same scalar comparison for null against myScalarVal. The difference is COALESCE will now have to perform the same scalar comparison against myScalarResult to determine if it is also null. This could be viewed as a minor performance hit, although scalar comparisons are about as trivial of an action as I can imagine.
Now assume we have:
ISNULL(myScalarVal, ISNULL(mySecondScalar, ISNULL(myThirdScalar, myScalarResult)))
and
COALESCE(myScalarVal, mySecondScalar, myThirdScalar, myScalarResult)
Now we have to execute three seperate statements, returning the result of each nested statement to its parent statement. There is inherint overhead in having multiple levels of recursion waiting on a return value. COALESCE by comparison does this intrinsically, not much caring how many values it's been given. It simply plods along looking for the first non-null value and throws away anything that doesn't satisfy this criteria as opposed to passing the failed results back to a calling function.
So all in all, I don't think there's much of a difference between the two for testing and substituting a single null value. However there are great benefits in performance, readibility and flexibility in using COALESCE for multi-element comparisons, so why not use COALESCE out of hand all the time?|||SQL Server supports cursors?
...
I have managed to use only two in the past 4 years...and both of those to demonstrate why they should NOT be used ;)
They're sure slow hua? Here I'm arguing over trivia and start talking about using something about 10 times slower. sigh. And 10 times clunkier to program.
Usually it's a conversion program or integrity check - but in production ... there's always some kind of alternative. Sometimes however, what the heck's the difference between .1 second and 2 seconds for a once a month process.
... That said, why not use COALESCE out of hand all the time?
Well, I'm sold. Quite honestly (I admit :eek: ), the answer is "harder to type and spell".|||Well, I'm sold. Quite honestly (I admit :eek: ), the answer is "harder to type and spell".Ahhh...but there is an advantage to that as well. Noob developers looking over shoulder think it is some mystical database arcana. Abra-cadabrac-coalesce...it helps keep up the reputation.|||My Main problem is still unanswered.
Problem statement:
If a select query without group by clause, returns null under column headers, if select query doesnt fetch any record.
If a select query with group by clause, not returns null under column headers, if select query doesnt fetch any record.
I want select query should return zero for int datatype and - for varchar datatype, if select query doesnt fetch any record.|||sorry, umeshm_patil, i don't think that's possible|||Indeed, why would MS deviate from ANSI standard on this?I [b]like[b] the easy questions... When Sybase created the IsNull function, Coalesce was still about seven years from coming into existance as a proposed addition to the standard. Microsoft inherited the SQL Server product with IsNull already well established.
Actually, starting in SQL 7.0 the IsNull function is now implemented internally as a call to the same routine that serves Coalesce. There might be minute difference in the Transact-SQL parsing time (only because Coalesce has more letters than IsNull), but that difference isn't material to the function execution. If there are more than two values (implying nested calls to IsNull), then a single call to Coalesce will win hands down.
-PatP|||My Main problem is still unanswered.
Problem statement:
If a select query without group by clause, returns null under column headers, if select query doesnt fetch any record.
If a select query with group by clause, not returns null under column headers, if select query doesnt fetch any record.
I want select query should return zero for int datatype and - for varchar datatype, if select query doesnt fetch any record.What you seem to be requesting is a formatter for "non values" in your result set. These are by definition application specific, they are not truly issues for the server to manage for you. They really ought to be handled in your application code to avoid pushing application presentation issues into the realm of your SQL Server.
-PatP|||I don't know of any benchmarks showing one is faster than the other. I know of three! One I have bookmarked (good old Adam) with links to the other two:
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/30/5311.aspx
The difference would be less than negligible anyway.Bingo!
Sorry for another <ot> post umeshm_patil. FWIW - I agree with Pat. Well done Pat!|||excellent link, pootle flump|||Nice!
A 4th test, (http://jerrytech.blogspot.com/2006/05/sql-2k-performance-isnull-vs-coalesce.html) linked by someone in the comment section of pootle flump's link, showed very surprising results.
The time for a nested ISNULL was only MARGINALLY longer than a single ISNULL.
Example:
9280ms : Set @.x = IsNull(Jerry, Nixon)
9296ms : Set @.x = IsNull(Jerry, IsNull(Nixon, Value))
9500ms : Set @.x = Coalesce(Jerry, Nixon)
9563ms : Set @.x = Coalesce(Jerry, Nixon, Value)
This test showed ISNULL to be slightly faster.
Hardly an arguement for using ISNULL given it's propietary nature, but it is an arguement for not rolling one's eyes quite so loudly when some programmer nests ISNULLs.|||btw, while we are on the topic of coalesce, C# has a coalesce operator now. I just learned about it following some of the perf testing links pootle_flump provided: http://weblogs.sqlteam.com/mladenp/archive/2006/03/27/9425.aspx
with it you can do this:
string foo = bar ?? "default value";
very neat! I love the operator because when I first saw it I thought "what??"|||<cough>
drop table testproducts
create table testproducts
(col1 int,
col2 varchar(10))
insert into testproducts
select 1, 'red'
union
select 2, 'yellow'
union
select 3, 'blue'
create table testorders
(col1 int,
col2 int)
insert into testorders
select 1, 10
union
select 2, 30
union
select 1, 50
union
select 2, 1
union
select 1, 5
-- Hey! No blue orders??
select sum(o.col2), count(*), p.col2
from testproducts p left outer join
testorders o on p.col1 = o.col1
group by all p.col2
Yeah, yeah. Group by all is non-ANSI, but it gets the OP through the day.|||<cough>something wrong with your throat?
guess what results you get for this query --select sum(o.col2), count(*), p.col2
from testproducts p left outer join
testorders o on p.col1 = o.col1
group by p.col2
so your point was... ?|||Mistaken, apparently.....|||Not to stray off topic, but anyone ever run COALESCE with NULL as the final value?
Interesting...|||Not to stray off topic, but anyone ever run COALESCE with NULL as the final value? i haven't
i don't think it's ever necessary, is it ;)|||i haven't
i don't think it's ever necessary, is it ;)
Of course not. It made for an interesting academic exercise in poking around behind how coalesce works though...|||Not to stray off topicI think off topic is the new on topic.
Of course not. It made for an interesting academic exercise in poking around behind how coalesce works though...Presumably this is where you got a lot of your conclusions from ealier? Do you have any code to hand or do we have to jig up our own? :)|||My earlier conclusions were nothing more than logical conjecture as a developer. It wasn't until all those contradictory perf studies were posted that I really wanted to start monkeying with this stuff.
Anyways, try these:
SELECT CASE WHEN 1=1 THEN NULL END
SELECT CASE WHEN 1=1 THEN NULLIF(1,1) END
SELECT COALESCE (NULL, NULL)
SELECT ISNULL(NULL, NULL)
This seems to suggest that ISNULL is using a different method of comparison then COALESCE. One that could very well be faster.|||Yeah, but this works...
DECLARE @.x int
SELECT COALESCE (NULL, @.x)
So perhaps it's just syntax and/or the use of the keyword NULL?|||The query engine has no way of knowing the runtime value of @.x when compiling the query plan. That's the same reason COALESCE(NULL, NULLIF(1,1)) will function properly; NULLIF() isn't evaluated until runtime. You can do the same trick using Case.
The conclusion I'm driving at is COALESCE() is nothing more than a wrapper for CASE whereas ISNULL() is doing something entirely different. This may explain why what would appear to be the logically superior structure for evaluating multiple elements isn't always the fastest. Now the question becomes:
Which is faster?
ISNULL(myScalar1, ISNULL(myScalar2, result))
or
CASE
WHEN myScalar1 <> NULL THEN myScalar1
WHEN myScalar2 <> NULL THEN myScalar2
ELSE result
END
Edit: A bit more co-noodling with another member here brought up an interesting opinion. COALESCE, being newer than ISNULL simply implements more stringent front-end validation as it is an ANSI standard. Now I'm all messed up... :(|||here's an example that came up today on another forum
scenario: there are two tables, persons and contacts, a person can have multiple contacts, each contact is a separate row with a contact type column, and the query is supposed to return each person with at most one contact -- return the person's email contact, and if email contact doesn't exist, return the work number, and if work number doesn't exist, return the home number
select p.name
, coalesce(e.contact
,w.contact
,h.contact) as contact
from persons as p
left outer
join contact as e
on e.personid = p.id
and e.contacttype = 'E' /* email */
left outer
join contact as w
on w.personid = p.id
and w.contacttype = 'W' /* work # */
left outer
join contact as h
on h.personid = p.id
and h.contacttype = 'H' /* home # */
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment