August 8, 2020 at 10:48 pm
It also works without the tally table and just using CROSS APPLY
select
po.ProductCategory, po.ProductSubCategory, v.Region, v.OrdersPlaced,
(v.OrdersPlaced*100.0/SUM(v.OrdersPlaced) over (partition by po.ProductCategory)) PercentPlaced
from #po po
cross apply
(values ('North America', [Total Orders Placed in North America]),
('South America', [Total Orders Placed in South America]),
('North Africa', [Total Orders Placed in North Africa]),
('South Africa', [Total Orders Placed in South Africa])) v(Region, OrdersPlaced);
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 10, 2020 at 1:26 am
Certainly this question will sound stupid: is IIF a new SQL command? I do not see it any of my (somewhat old) SQL books. I looked at the MS Doc, and it doesn't say when it was introduced, just that the article was written in 2017. Of course, I know of CASE but never seen IIF before.
Also, thank you so much everyone for your help and input, I am trying your suggestions now. I am guessing that there is no performance or strategic advantage in choosing any of above approaches. I personally dislike creation of a table (temp table), so i prefer to do without.
August 10, 2020 at 1:59 am
FYI, the IIF function became available in SQL Server 2012. I don't recall hearing much about it back then, so I would imagine it may not be all that well known, although by now it really should be...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 10, 2020 at 11:55 am
Imo IIF gets hated on because it was available first in MS Access. IIF gets interpreted back to "CASE WHEN" so it's really the same (which is a shame because it's limited). I like IIF vs "CASE WHEN" for 2 reasons: 1) it's more compact so less code to look at, and 2) all 3 parameters are required so you must specify the ELSE condition.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 10, 2020 at 2:06 pm
IIF is a corruption of SQL syntax to make other developers feel more comfortable.
A terrible idea, in my opinion. Oracle's done the same thing to PL/SQL (Oracle equivalent of T-SQL), except in an even more extreme way, such that PL/SQL is now such a mish-mash of different languages syntaxes that it's horribly inconsistent and virtually unreadable.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 10, 2020 at 2:22 pm
Ha I think's the other way around. CASE WHEN is an abomination of over wordiness that never should've been created. First, back in the day you used to have to end each nested condition with END. If you nested 10 levels that 10 END's. Also by not forcing people to actually enter the full condition (if you leave off ELSE NULL, you still get ELSE NULL) it makes for sloppy code. Also, 10 levels is the maximum nesting I think? It's never been an issue for my projects but that seems like an arbitrarily low restriction imo. They should've started with IIF and expanded the nesting capabilities.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 10, 2020 at 2:42 pm
IIF is what leads to wordiness and complexity. Here's a simple CASE, what wouldn't this take embedded IIFs?
SELECT status_code, CASE status_code
WHEN 'A' THEN 'Allocated'
WHEN 'C' THEN 'Complete'
WHEN 'D' THEN 'Disabled'
WHEN 'F' THEN 'Failed'
WHEN 'R' THEN 'Retry'
WHEN 'X' THEN 'Cancelled'
ELSE 'Unknown' END AS status
FROM ( VALUES('A'),('D'),('R') ) AS data(status_code)
Moreover, CASE is ANSI SQL syntax and is thus available across DB2, Oracle, MySQL, etc.. IIF is MS only, AFAIK. And it's not T-SQL, as proven by the fact that it gets converted to a CASE(s). If you want to write SQL, don't use the crutch of IIF.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 10, 2020 at 2:59 pm
Here's a simple CASE, what wouldn't this take embedded IIFs?
Idk why don't you give it a try and let us know
... since I write SQL now not a general programming language.]
Moreover, CASE is ANSI SQL syntax and is thus available across DB2, Oracle, MySQL, etc.. IIF is MS only, AFAIK
So you write cross-rdbms SQL according to ANSI standard? That's not a serious pov imo.
If you want to write SQL, don't use the crutch of IIF
Ready, fire, aim. That sounds about right.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 10, 2020 at 6:13 pm
Here's a simple CASE, what wouldn't this take embedded IIFs?
Idk why don't you give it a try and let us know
I never use IIF, so wouldn't waste my time on that. If IIF is as clear as you claim it is, why wouldn't YOU want to demonstrate that?
... since I write SQL now not a general programming language.]
Moreover, CASE is ANSI SQL syntax and is thus available across DB2, Oracle, MySQL, etc.. IIF is MS only, AFAIK
So you write cross-rdbms SQL according to ANSI standard? That's not a serious pov imo.
I don't mind language extensions. But I don't use extensions that don't provide a genuine value, i.e., I don't use an extension just because it's more familiar to me if there's an equally good ANSI-standard way to it. I think that's a professional pov.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 10, 2020 at 7:20 pm
Horses for courses and everybody thinks their way is the best. Why not have a sense of humor about it? I agree Pivot and Unpivot are kludge compared to conditional aggregation and CROSS APPLY.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
August 12, 2020 at 3:01 am
What I dislike about PIVOT is that you have to repeat UNIONs several times depending on the metric. This makes it difficult to change and error-prone as you may forget to update something somwhere.
August 12, 2020 at 2:06 pm
What I dislike about PIVOT is that you have to repeat UNIONs several times depending on the metric. This makes it difficult to change and error-prone as you may forget to update something somwhere.
Back when I used to use PIVOT the first I'd do when it was needed is open BOL. Lol
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply