February 8, 2012 at 7:21 am
One thing I would love to be able to do in SQL is this:
(Simple example)
select
A,
B,
A * B as C,
C * 2 as D,
C + D as E
from SomeTable
instead of this which SQL forces you to do:
select
A,
B,
A * B as C,
(A * B) * 2 as D,
(A * B) + ((A * B) * 2) as E
from SomeTable
Usually I end up with either CTE's or similar to try and make the SQL more readable.
Are there any better work arounds?
February 8, 2012 at 8:00 am
I don't know if it's "better" than a CTE, but you can use CROSS APPLY
select
A,
B,
c.C,
d.D,
c.C + d.D as E
from SomeTable
CROSS APPLY ( SELECT A * B AS C ) AS c
CROSS APPLY ( SELECT C * 2 AS D ) AS d
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 8, 2012 at 8:07 am
drew.allen (2/8/2012)
I don't know if it's "better" than a CTE, but you can use CROSS APPLY
select
A,
B,
c.C,
d.D,
c.C + d.D as E
from SomeTable
CROSS APPLY ( SELECT A * B AS C ) AS c
CROSS APPLY ( SELECT C * 2 AS D ) AS d
Drew
Trying that now... maybe sometime, trying to tidy up a query which is currently over 1500 lines :pinch:
February 8, 2012 at 8:35 am
Samuel Vella (2/8/2012)
Are there any better work arounds?
More detail on the APPLY pattern:
http://sqlblog.com/blogs/rob_farley/archive/2011/04/12/the-power-of-t-sql-s-apply-operator.aspx
http://www.sqlservercentral.com/articles/APPLY/69953/
http://www.sqlservercentral.com/articles/APPLY/69954/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 9, 2012 at 2:11 am
CELKO (2/8/2012)
your mindset is still stuck in procedural languages.
A huge assumption and very condescending! (You can insert your own words of choice to give that sentence some extra flavour)
CELKO (2/8/2012)
Instead of working from left to right, an entire row comes into existence ALL AT ONCE. This is not magnetic tapes or punch cards. Likewise, all the row in a table come into existence ALL AT ONCE. Think parallelism, not sequential procesing.In SQL, "SELECT a,b,c FROM Foobar;" is the same data as "SELECT b,c,a FROM Foobar;" because things are in sets and sets have no ordering.
Just because something is not supported at the lowest level does not mean it can not be supported in one of the abstraction layers.
SQL Server 2012 introduces a few concepts from MDX, lead and lag to name two, which both behave as if a row is positioned absolutely in a table. SQL Server hasn't broken any relational rules to make lead and lag work (this is quite clear as the order needs to be specified), but still; there is now the ability to reference the row above or the row below without having to work around with a self join.
Similarly, it is not beyond the ken of microsoft to add some functionality which will allow a calculated column to be reused later in the some select list.
CELKO (2/8/2012)
Now try to figure out the meaning of SELECT (a+b) AS c, (d+e) AS a; and change the ordering --- garbage!
Ironically, that makes perfect sense. Try the same thing in Excel, it will work out fine. As long as everything is named and referenced properly there is no reason why a "SQL" query languange cannot work out what is meant by that.
February 9, 2012 at 2:38 am
Samuel Vella (2/9/2012)
CELKO (2/8/2012)
your mindset is still stuck in procedural languages.A huge assumption and very condescending! (You can insert your own words of choice to give that sentence some extra flavour)
Some people find it best to simply ignore the Joe Celko forum attitude (he's apparently very personable in real life). He posts this sort of thing regularly; sometimes he makes some valid points, alas not so on this occasion.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
February 9, 2012 at 3:58 am
I miss arrays a lot in SQL. Like this:
create procedure ImissArrayParam(@array Array(Integer))
as
begin
select * from myTable where myId in @array
end
GO
February 9, 2012 at 4:18 am
jcb (2/9/2012)
I miss arrays a lot in SQL. Like this:
create procedure ImissArrayParam(@array Array(Integer))
as
begin
select * from myTable where myId in @array
end
GO
you can do this:
CREATE TYPE DemoTableType AS TABLE
(
[ID] int not null
)
GO
create procedure ImissArrayParam(@array DemoTableType readonly)
as
begin
select * from
(
select 1 as myID, 'a' as col1 union all
select 2 as myID, 'b' as col1 union all
select 3 as myID, 'c' as col1
) myTable
where myId in (select id from @array)
end
GO
declare @mytablevar DemoTableType
insert into @mytablevar values (2);
insert into @mytablevar values (3);
exec ImissArrayParam @mytablevar
go
drop procedure ImissArrayParam
go
drop type DemoTableType
February 9, 2012 at 4:29 am
Thanks Samuel,
It can be achieved from many ways and for dynamic arrays we can use any kind of split function and strings.
But I miss the simplicy arrays can give for coding these kind of thing.
February 9, 2012 at 4:39 am
jcb (2/9/2012)
Thanks Samuel,It can be achieved from many ways and for dynamic arrays we can use any kind of split function and strings.
But I miss the simplicy arrays can give for coding these kind of thing.
Agreed, having to define the type first is not exactly elegant.
February 9, 2012 at 5:15 am
Samuel Vella (2/9/2012)
...Agreed, having to define the type first is not exactly elegant.
Defining the type before or after the variable is just language flavor.
February 9, 2012 at 5:33 am
SQL Kiwi (2/9/2012)
Some people find it best to simply ignore the Joe Celko forum attitude (he's apparently very personable in real life). He posts this sort of thing regularly; sometimes he makes some valid points, alas not so on this occasion.
Thanks 🙂
It's a shame that someone with so much experience and talent in both database systems and writing has to express themselves in such a blunt manner :hehe:
February 9, 2012 at 6:27 am
Samuel Vella (2/9/2012)
SQL Kiwi (2/9/2012)
Some people find it best to simply ignore the Joe Celko forum attitude (he's apparently very personable in real life). He posts this sort of thing regularly; sometimes he makes some valid points, alas not so on this occasion.Thanks 🙂
It's a shame that someone with so much experience and talent in both database systems and writing has to express themselves in such a blunt manner :hehe:
It's not a question of bluntness. It's adherence to a teaching method that was originally designed to keep lower caste workers compliant and is specifically built to stiffle creativity, inovation, and initiative. He just doesn't know that, because he's spent far too little time studying the subject of education, but still views himself as an educator.
It's a common problem. People with little education and skill on a subject tend to overrate their own ability very heavily. Doesn't just apply to Joe and his online persona, applies all over the place to millions of people. Easy enough to spot if you take the time to study up on this kind of thing.
- 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
February 10, 2012 at 5:58 am
GSquared (2/9/2012)
It's not a question of bluntness. It's adherence to a teaching method that was originally designed to keep lower caste workers compliant and is specifically built to stiffle creativity, inovation, and initiative. He just doesn't know that, because he's spent far too little time studying the subject of education, but still views himself as an educator.It's a common problem. People with little education and skill on a subject tend to overrate their own ability very heavily. Doesn't just apply to Joe and his online persona, applies all over the place to millions of people. Easy enough to spot if you take the time to study up on this kind of thing.
Wow -I've had a look through some of his recent posts and see what you mean!
"Deafened by his own shouting" I think sums it up
February 11, 2012 at 3:32 am
CELKO (2/8/2012)
your mindset is still stuck in procedural languages. Instead of working from left to right, an entire row comes into existence ALL AT ONCE. This is not magnetic tapes or punch cards. Likewise, all the row in a table come into existence ALL AT ONCE. Think parallelism, not sequential procesing.In SQL, "SELECT a,b,c FROM Foobar;" is the same data as "SELECT b,c,a FROM Foobar;" because things are in sets and sets have no ordering.
Now try to figure out the meaning of SELECT (a+b) AS c, (d+e) AS a; and change the ordering --- garbage!
BwAA-HAAA!!!! The "row" depicted by a punch card comes into existence all at once. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply