High tech without high touch does not work. Stephen Covey
SQL: WHEN CASE NULL Fails
Imagine you want to find all the null values in a column in a database table (SQL Server).
| x |
|---|
| 1 |
| 2 |
| NULL |
| 4 |
| 5 |
Here is the SQL that performs the task as required:
[sourcecode language=”sql”]
SELECT x,
CASE x
WHEN NULL THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]
The result he expected was:
| x | result |
|---|---|
| 1 | no |
| 2 | no |
| NULL | yes |
| 4 | no |
| 5 | no |
But that isn’t what he got. His result was like this:
| x | result |
|---|---|
| 1 | no |
| 2 | no |
| NULL | no |
| 4 | no |
| 5 | no |
Curiously, if you run this:
[sourcecode language=”sql”]
SELECT x,
CASE x
WHEN 1 THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]
You do get this:
| x | result |
|---|---|
| 1 | yes |
| 2 | no |
| NULL | no |
| 4 | no |
| 5 | no |
So, why didn’t the original work?
-
Because NULL means unknown
-
Because NULL does not equal NULL
-
Because NULL is just weird, weird, weird
Anyway, here is the SQL that gives the expected answer:
[sourcecode language=”sql”]
SELECT x,
CASE
WHEN x IS NULL THEN ‘yes’
ELSE ‘no’
END AS result
FROM
someTable
[/sourcecode]
SQL Server: Change Owner of an Object
Like this:
[sourcecode language=”sql”]
sp_changeobjectowner ”, ”
[/sourcecode]
Problem Starting Application Using VBScript?
Today, someone asked me about a problem he had running an application from VBScript. Here is the text of my reply:
Adding the parameters to the end of the .exe name should work. Often, however, the problem is spaces / quotes in the command line. The following recipe should deal with all such problems.
Write out the command you want to execute in full, e.g.
C:Program FilesJavaThingyJava.exe -silent -otherParameters:”value” -finalParameter
If there is a space in the path to the executable, put this bit in quotes.
“C:Program FilesJavaThingyJava.exe” -silent -otherParameters:”value” -finalParameter
Then, double all quotes in the whole line
“”C:Program FilesJavaThingyJava.exe”” -silent -otherParameters:””value”” -finalParameter
Put another set of quotes around the whole thing, and add the WshShell.Run gubbins:
WshShell.Run “””C:Program FilesJavaThingyJava.exe”” -silent -otherParameters:””value”” -finalParameter”, 1
Useful Web Development Plugins for IE
Info on a couple of IE plugins that I find useful:
First, I couldn’t live without the Internet Explorer Developer Toolbar. It is a collection of useful little tool for all kinds of web page development, free from Microsoft. Great for digging into the workings (or failings) of a web page. Follow the link for more information:
http://www.microsoft.com/downloads/details.aspx?familyid=e59c3964-672d-4511-bb3e-2d5e1db91038&displaylang=en
Second, the Web Development Helper useful. It was written by independent developer,Nikhil Kothari, and is also free. Not as useful as the Microsoft one, but still handy to have around. Its strength is its ability to log the traffic between the browser and the server: good if you want to know what a form is sending to the server, or what a server is sending back.
http://projects.nikhilk.net/Projects/WebDevHelper.aspx
SQL Server: Case Sensitive Query
Someone at work sent this out to the team.
From LW:
Dear All, you might find this bit of code quite useful. I was trying to find data within a table where the data was like lower case letters e.g a, b as opposed to A, B. TSQL was not differentiating between lower and upper case so the following line forced it to do this substring(hradminblockcode,1,1) COLLATE SQL_Latin1_General_CP1_CS_AS
I used it in the following query
SELECT hrrefno FROM hrrepairmaster WHERE substring(hradminblockcode,1,1) COLLATE SQL_Latin1_General_CP1_CS_AS�
IN (‘a’,’b’,’c’,’d’,’e’,’f’,’g’,’h’,’i’,’j’,’k’,’l’,’m’,’n’,’o’,’p’,’q’,’r’,’s’,’t’,’u’,’v’,’w’,’x’,’y’,’z’)
That might be useful. There is more information here: http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm