Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Finding combinations of values


Finding combinations of values

Author
Message
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3938 Visits: 6669
CELKO (12/14/2012)
.. Total failure.. Besides which, I believe my earlier post was the same idea, except coded correctly :-) .


Yeah, it was awful! Crazy

What I wanted to do was avoid the CASE expression solution and go for something using functions that compile to simple assembly language.


DELETE FROM Client_Shares
WHERE COALESCE (SIGN(ABS(lm_id)+1), 0)
+ COALESCE (SIGN(ABS(ml_id) +1), 0)
+ COALESCE (SIGN(ABS(is_id) +1), 0)
+ COALESCE (SIGN(ABS(t_id)+1), 0) >= 2;



Yiikes, that's incredibly convoluted. What a royal pita to try to understand, much less change or debug later!!

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3938 Visits: 6669
CELKO (12/14/2012)
[ Yiikes, that's incredibly convoluted. What a royal pita to try to understand, much less change or debug later!!


But it is fast and portable :-D

On a more serious note, that kind of function nesting used to be standard idioms in early SQL. If you can find some articles and books by David Rozenshtein, he did a lot of stuff with it to create the Characteristic function, etc.



Yeah, and we used to use punch cards and write our own bubble sorts too. For me to use something that indecipherable, there'd have to be a noticeable performance diff.

As to "portable", that really is a myth. There are so many custom approaches in each variant of SQL you can never port anything easily.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
CELKO (12/14/2012)
.. Total failure.. Besides which, I believe my earlier post was the same idea, except coded correctly :-) .


Yeah, it was awful! Crazy

What I wanted to do was avoid the CASE expression solution and go for something using functions that compile to simple assembly language.


DELETE FROM Client_Shares
WHERE COALESCE (SIGN(ABS(lm_id)+1), 0)
+ COALESCE (SIGN(ABS(ml_id) +1), 0)
+ COALESCE (SIGN(ABS(is_id) +1), 0)
+ COALESCE (SIGN(ABS(t_id)+1), 0) >= 2;


Why overcomplicate it? Why not the solution I posted, that simply uses Where <col> Is Null? Your solution doesn't improve anything on this, makes it impossible for SQL Server to use indexes on the columns, makes it unclear to anyone reading the code what it's supposed to do (you'll need to add a lot of documentation to your version), and seems to be complexity for the sake of "cleverness".

Edit: And, so far as I know, "Where <col> Is Null" is valid in every version of SQL I've ever seen, so your solution isn't even "more portable".

- 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
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
CELKO (12/14/2012)
[quote] .. Total failure.. Besides which, I believe my earlier post

Yeah, it was awful! Crazy

What I wanted to do was avoid the CASE expression solution and go for something using functions that compile to simple assembly language.


DELETE FROM Client_Shares
WHERE COALESCE (SIGN(ABS(lm_id)+1), 0)
+ COALESCE (SIGN(ABS(ml_id) +1), 0)
+ COALESCE (SIGN(ABS(is_id) +1), 0)
+ COALESCE (SIGN(ABS(t_id)+1), 0) >= 2;


Yep, and this one is total failure too (OP wasn't interested in code obfuscation;-))!
Can you understand the simple fact that around 99% of projects/clients/developers/etc. DO NOT NEED PORTABILITY! Especially, as portability of SQL does not exist for now.

If you really want to avoid CASE expression, it can be achieved in much more elegant way:


DELETE t
FROM trefClientShares t
CROSS APPLY (SELECT COUNT(v) nnv
FROM (VALUES (intLMid),(intMLid),(intISid),(intFRid)) v(v)) a
WHERE a.nnv < 2



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
CELKO (12/17/2012)
As to "portable", that really is a myth. There are so many custom approaches in each variant of SQL you can never port anything easily.


Then how I have earned a living writing portable SQL for 25+ years? Why does the US Government have FIPS Flaggers? Why did NIST set up the FIPS-127 conformance test suite?

Portable code has to be designed from the start with this goal. Ever read Jerry Weinberg's classic: The Psychology of Computer Programming: Silver Anniversary Edition (ISBN-13: 978-0932633422). Yes, it is so good it gets a Silver anniversary.

Part of the book deals with setting goals and how that affects the code. If you start off like a cowboy coder with "Larry the Cable Guy" philosophy of "Git'er done!", you get code written fast. If you ask for smallest possible code or the fastest execution, you get other kinds of programs.

We know that 80-95% of the total lifetime cost of a system is in maintaining it. DoD, the largest user of software on Earth, found that code that can port from release to release of the same product or to other products saves you tens of billions of dollars.

Some of my consulting work is for VC's who want to know if the applicant's databases can scale up and port (usually to a mainframe, but more and more, we scale down to mobile devices). Sometimes a Venture Capitalist gets a winner! Winners have to grow. The way to grow is to port.

I have killed projects with a bad recommendation. In one case, two start-ups in the same market space, a POS based on a particular chip, needed a decision. One of them had a really neat, flashy interface and used a proprietary language tuned for that chip. We had a lot of these high level, structured assemblers in those days.

The competition was in ANSI Standard C and had a dull interface. They could have lapsed into assembler from the C and gotten a real boost on the particular chip they had, but they stuck to Standard C.

We went with the dull guys. A few months later, the proprietary language and the chip were discontinued. The flashy, non-portable, guys learned one of the laws of ecology. "The better a species is adapted to one environments, the worse it is adapted to all other environments." Or "there are no snakes at the North Pole and no polar bears in the Sahara Desert"


Yes, there's a point where portability is the primary factor. Large bureaucracies often need everything to follow published standards in order to get anything done at all. On the other hand, DoD and other large bureaucracies are more infamous for the projects they waste vast amounts of money and effort on and never finish (in some cases due to overemphasis on obsolete standards) than they are famous for getting things done efficiently or even well.

Another point is that, within its proper environment, a specialized organism is often vastly superior to a generalized one. Rats are highly generalized organisms, capable of surviving in a wide range of environments, while methanopyrus (http://en.wikipedia.org/wiki/Methanopyrus_kandleri) is an extremely specialized one capable of surviving only in a very specific environment. But if you put a rat in a pot of boiling sufuric acid, it won't survive as well as some mehanopyrus would in the same pot.

Keep in mind that some extremophile organisms are thought to be essentially unmodified over about 4 billion years. So, long-term survivability of these extremely specialized organisms isn't really a question.

So your point doesn't actually prove your point, if you get my point. :-D

On the other hand, code portability has certainly worked out well for you. You obviously have survived well by becoming a specialized organism in a specialized environment that fits your particular survival adaptations and assertions.

- 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
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
To SELKO:
GSquared (12/18/2012)

...
So your point doesn't actually prove your point, if you get my point. :-D

On the other hand, code portability has certainly worked out well for you. You obviously have survived well by becoming a specialized organism in a specialized environment that fits your particular survival adaptations and assertions.


I do like your point and the way you pointed it out! Hehe

There is actually one interesting example in IT world too.
There was recently one successful start-up IT company in UK, specialising in the Mobile phone games. Their initial idea was to develop games in portable code, so it could be played on different devices without much changes. They were very successful in this idea and they found venture capitalist to finance them. But after initial success and getting finance, they stopped concentrating on code portability, as to develop the best possible product for particular device (eg. OS) is often impossible (or financially unsustainable) without utilising of the proprietary device (OS) features.
With available financial power and relevant expertise, this company found that it's more appropriate and profitable to develops games for each OS separately using the most appropriate proprietor features of the OS. Yeah, they are not government department, so they cannot allow themselves to waste time and money as they are not founded by people taxes...
And on another hand, does Mr. CELKO, by providing the example of some "vendor" software disappearance in "few months time", implies that SQL Server is going to disappear soon and all SQL Server developers should prepare themselves to write code so it can be ported to DB2? ;-)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
GSquared
GSquared
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14375 Visits: 9729
Eugene Elutin (12/18/2012)
To SELKO:
GSquared (12/18/2012)

...
So your point doesn't actually prove your point, if you get my point. :-D

On the other hand, code portability has certainly worked out well for you. You obviously have survived well by becoming a specialized organism in a specialized environment that fits your particular survival adaptations and assertions.


I do like your point and the way you pointed it out! Hehe

There is actually one interesting example in IT world too.
There was recently one successful start-up IT company in UK, specialising in the Mobile phone games. Their initial idea was to develop games in portable code, so it could be played on different devices without much changes. They were very successful in this idea and they found venture capitalist to finance them. But after initial success and getting finance, they stopped concentrating on code portability, as to develop the best possible product for particular device (eg. OS) is often impossible (or financially unsustainable) without utilising of the proprietary device (OS) features.
With available financial power and relevant expertise, this company found that it's more appropriate and profitable to develops games for each OS separately using the most appropriate proprietor features of the OS. Yeah, they are not government department, so they cannot allow themselves to waste time and money as they are not founded by people taxes...
And on another hand, does Mr. CELKO, by providing the example of some "vendor" software disappearance in "few months time", implies that SQL Server is going to disappear soon and all SQL Server developers should prepare themselves to write code so it can be ported to DB2? ;-)


Not exactly, on the part I added emphasis to.

He's made the point before, more clearly than he did here, that writing "portable code" in SQL, also includes writing code that can easily be ported to the next version of SQL Server.

If you write ANSI/ISO standard SQL, instead of specialized T-SQL extensions, then you can expect that SQL Server 2015 will support your queries, even if you wrote them on SQL Server 2000 originally.

It's another case of being "partially correct". Part of this is that "standards compliant SQL" is actually a moving target, as with other standards, since the standards themselves evolve over time.

Then there's the silliness of some of the standards, compared to what's expected by those who actually use the technology. For example, Truncate wasn't accepted into the standard until 2008. Before that, someone like Joe could go off on you for violating standards if you used Truncate. Since then, it's provable that you actually were complying with the what the standard should have been, you were just ahead of your time.

Innovation depends on violation of accepted practices and standards. But innovation doesn't necessarily imply improvement. Sometimes it does improve things, and sometimes it's just the Edsel (http://en.wikipedia.org/wiki/Edsel).

- 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
Eugene Elutin
Eugene Elutin
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3042 Visits: 5478
... you were just ahead of your time.


Yeah, I'm always! But "they" don't understand me.
That is common among us, great painters... Crying

█████████████
█████████████
█████████████
█████████████
█████████████
█████████████
█████████████

But, last time it was sold for $1,000,000 Hehe

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search