Follow-up To Previous Post - I forbid you from showing me an outer join!

At the risk of repeating myself, and again quoting from Quora … I came across this today:

https://www.quora.com/What-are-the-pros-and-cons-of-having-complex-SQL-queries-vs-simple-SQL-queries-but-using-other-programming-languages-to-handle-the-complexity-Should-I-write-a-complex-SQL-query-to-handle-everything/answer/Bill-Karwin

It still baffles me that even some senior, hands-on and so called technical people in IT, still do not know how to write efficient SQL.

Here is the full quote: - - - - - - - - - - - - - - - - - - - - - - - - - -

What are the pros and cons of having complex SQL queries vs. simple SQL queries, but using other programming languages to handle the complexity? Should I write a complex SQL query to handle everything?
Bill Karwin, author of "SQL Antipatterns: Avoiding the Pitfalls of Database Programming" 

I saw a coworker, a senior Java programmer (with a PhD), struggling to get some code written. He was behind on his deadline. I asked him what he was trying to do and if I could help.

He said he needed data from two tables. He needed all data from the first table and only matching data from the second table. Where there was no matching data, he still wanted the data from the first table, but with nulls for the objects where there was no match in the second table.

“What’s the problem? That’s an outer join in SQL,” I replied.

He said he didn’t know how to use an outer join, so he had spent many hours trying to code the task from scratch using Java. He was fetching all the data from both tables, matching it up, and discarding data from the second dataset that didn’t match. He was trying to make the matching more efficient by implementing some sophisticated sorting algorithm and a B-tree data structure.

“No, really, the database can do that easily, it’ll run faster, require less code, and you’ll only fetch the results of the match. I’ll show you how,” I offered.

His face grew dark, and he literally said to me, “I forbid you from showing me an outer join!”

He had invested so much in implementing his data structure that he couldn’t bear to admit he had wasted his time. He would rather finish his pages of Java code, and never know how to do the same task in one line of SQL.

Don’t be that guy. Use the right tool for the job.

Don't put business logic in the presentation layer!

I'm currently working on a short-term contract in Sydney ... and I've already had several run-ins with the IT Manager. Essentially, it can be summarised by the answer below. 

The problem is, that my client wants to do calculations on the data at the presentation layer, in this specific case, in Qlik! 

Now don't get me wrong, Qlik is a great tool, with wonderful looking charts. But as a place to do complex joining of all the data, not right.  What happens when we want to display the same data on something else? Say, for example, a native App? 

The data, and the calcs, need to be copied somewhere else. Currently all the data is being copied into Qlik and then the joins, IF/ELSE statements, date calculations etc .... are all in Qlik. 

As per the answer in the link article below, there is no documentation, and no one else to support it. 

I've suggested building a proper data warehouse using Microsoft SQL Server. And then linking Qlik to the data in the warehouse, as it is very good at doing. But unfortunately, it's not my call.

I think it's mad. But .... the customer is always right, so I'm doing the best I can. 

What do you think? Before you answer ... be sure to read what Andrew wrote below: 

Read Andrew Weishan's answer to Why is SQL in such high demand when I can use Excel and do half of the work, and never have to enter code again? I can literally write a macro and have a button. on Quora

Visual Basic ... I predict a comeback!

I was recently at a fund-raiser for my son's school, and after a few bottles of wine, one of the other dad's struck up a conversation with me. Turns out he is also a programmer, and one of the all-too-common MVC full stack guys who writes in C#, Java, Angular etc ...

When I told him I was a SQL MCSE he was genuinely impressed and asked me interesting questions on SQL. I then mentioned that I am loving my hobby of writing Apps using Basic.

At the mere mention of the words "Visual Basic" ... he nearly fell off his chair!

I know this type ... I used to work with one at a former job ... He HATED Basic too. He is what I call a "code snob". Even though Microsoft Visual Basic .Net compiles to the same machine code as C#, there is still this stigma surrounding the language. Maybe they shouldn't have used the acronym "basic" ... ?

Anyway, as a happy co-incidence I stumbled upon this blog on Code Project. So to save me typing it all out, I'm just going to paste the link here ... If you're a hater of anything Basic ... please read this before calling me a beginner! 😄

https://www.codeproject.com/Articles/1257592/BASIC-Why-The-Answer-Might-Surprise-You-Part-1?msg=5548980

And if you're looking for a really easy way to write apps for BOTH Android and iOS without owning a Mac ... try this:
https://www.idevaffiliate.com/33168/128-0-3-1.html

Live your life as though everything you do is being recorded ... because it probably is!

With so much #data out there on us, there is now a profession of "data cleaners"! 

To paraphase Gary Vaynerchuk - "live your life as though everything you do is being recorded ... because it probably is"

Mobile Apps New Website Launched -- www.SSApps.com.au

Hello,

Just an addition to the previous post entitled "iOS and Android App Development!" ... we've been busy working on our new business and it's going better than we expected.

We've just launched our new website, with more information on it. To see it, please go to:

https://www.ssapps.com.au

We've decided that for the next 12 months we're going to focus 100% on delivering high quality Data driven and database applications for both iOS and Android ... and only with businesses in the Sutherland Shire. This will mean we can develop good relationships with our business partners, and exceed their expectations.

Please spread the word ...

Rodney & Lisa!

iOS and Android App Development!

Hi,

It's been a while since I've posted on this blog, but there is a reason for it (all be it a very poor one).

As if I needed anything else to do, I've been writing apps in my spare time, and am proud to say that I am getting pretty good at it.

So good in fact, that I've now released my first commercial app for both iOS and Android. It's an app which ties in to my popular Gym & Martial Arts Software package.

I'm now keen to do more development work and have put up this page on the company website. Please feel free to contact me if you have a million dollar idea!  😄

https://stingrae.com.au/app-development.aspx

The Basics - Float or Dec?

This is my blog on databases, and as such I try and it keep it confined to IT and business. But there are parallels to my other job as a martial arts coach.

In jiu-jitsu, if you do not have solid basics, then you will lose. All the flashy moves in the world won't help if you don't know the finish.

And it's the same in the world of databases. I've worked on a few systems where the front end had all sorts of neat wizardry, but the engine was terrible. Here is one such example:

A financial controller came to be one day asking why some of her reports were out by one cent. Everything added up, but the final figure on the report was $0.01!

After looking at the database definitions, I saw the obvious (to me). The original designer had created the currency fields as floating point decimals!

And sadly, this is common. Because it's so easy to create a table (in for example Microsoft SQL server), then people who have not really grasped the intricacies of data types will do it, and quite often get it wrong.

Here is a brief example to demonstrate the issue of using Floats compared to Decimals:

(Copy paste the following into SSMS)

DECLARE @Num1 decimal(10,2), @Num2 decimal(10,2), @Num3 decimal(10,2);
SET @Num1 = 54; 
SET @Num2 = 3.1; 
SET @Num3 = 0 + @Num1 + @Num2; 
SELECT @Num3 AS TotalAsDecimal
SELECT @Num3 - @Num1 - @Num2 AS "Should be 0";

GO

DECLARE @Num1 float, @Num2 float, @Num3 float;
SET @Num1 = 54; 
SET @Num2 = 3.1; 
SET @Num3 = 0 + @Num1 + @Num2; 
SELECT @Num3 AS TotalAsFloat
SELECT @Num3 - @Num1 - @Num2 AS "Should be 0";

Both these two blocks do the same thing, the only difference is in the datatypes.

Here are the results:

To understand why this simple mathematical example returns a "wrong" answer, I suggest you watch this great video put out by Computerphile. The presenter does a great job of explaining how floating point numbers are stored on disk, and how this error actually happens.

So, until next time .... study the basics, and get them right! If you get the basics wrong, it will be an expensive problem to fix later down the road.