Oracle join (+) notation

December 12, 2013

Oracle join (+) notation

select author_last_name, book_key
from author left outer join book_author using (author_key)
order by author_last_name;   

In the example above, the AUTHOR table is on the left, and we are using a left outer join, so we get all the rows in the AUTHOR table and the matching rows in the book_author table.  Notice that both authors clark and mee now are listed, and the book_key column is NULL.  In the standard Oracle format, outer joins can be confusing.  Below is the same query in the standard Oracle format.

select
  author_last_name,
  book_key
from
  author,
  book_author
where
  author.author_key = book_author.author_key(+)
order by author_last_name;

Notice the (+) in the WHERE clause.  This indicates a left outer join.  If we were using a right outer join, the WHERE clause would be:

author.author_key(+) = book_author.author_key

Advantages of JOIN notation

–The AND clause blocks the outer join
SELECT A.ml_street, L.ml_name, L.f_tr_status
FROM EN_TR_COMP_ADRESS A, EN_TR_LOCATION L
WHERE l.f_tr_comp_adress = a.row_id(+)
AND L.f_tr_status IS NULL

–The AND clause blocks the outer join (JOIN equivalant)
SELECT A.ml_street, L.ml_name, L.f_tr_status
FROM EN_TR_COMP_ADRESS A LEFT JOIN EN_TR_LOCATION L
ON l.f_tr_comp_adress = a.row_id
WHERE L.f_tr_status IS NULL

–The AND clause does not block the outer join (this notation is only possible with JOIN)
SELECT A.ml_street, L.ml_name, L.f_tr_status
FROM EN_TR_COMP_ADRESS A LEFT JOIN EN_TR_LOCATION L
ON l.f_tr_comp_adress = a.row_id AND L.f_tr_status IS NULL

Advertisements

ASP.NET AJAX MaskedEditExtender

September 25, 2013
  • MaskType – Type of validation to perform:
    None – No validation
    Number – Number validation
    Date – Date validation
    Time – Time validation
    DateTime – Date and time validation
  • Mask Characters and Delimiters
    9 – Only a numeric character
    L – Only a letter
    $ – Only a letter or a space
    C – Only a custom character (case sensitive)
    A – Only a letter or a custom character
    N – Only a numeric or custom character
    ? – Any character

Linq operators on null Linq collection

September 25, 2013

You cannot apply the Linq operators (First, Single, Any, …) when the query returns null.

We have to add an extension method:

public static IEnumerable<TSource> NullToEmpty<TSource>(
    this IEnumerable<TSource> source)
{
    if (source == null)
        return Enumerable.Empty<TSource>();

    return source;
}

And now you can write:

public void Should_handle_nulls()
{
    List<int> ints = null;

    ints.NullToEmpty().Any();
}

LINQ parameter == null with SQL CE

August 6, 2013

SQL CE cannot handle parameters so, the so standard construction

.Where(p => p.Category == category || category == null)

fails because LINQ needs to use a parameter to do that value comparison in-query.  But it is perfectly valid SQL to pass something like this:

.Where(p => p.Category == category || true)

because ‘true’ is always…well…true.  So if you parse your variable to a bool before the LINQ statement and then pass that variable to SQL CE, you’re in good shape:

bool bCategory = (string.IsNullOrEmpty(category)) ? true : false;
Products = repository.Products.Where(p => p.Category == category || bCategory);

This allows you do to all the filtering in the query before returning data, and you can use this trick on as many conditions as you want without it getting messy.

Office Development

July 4, 2013

Office development libraries:

http://msdn.microsoft.com/en-us/library/office/bb726434.aspx

Interesting are the articles about the Open XML format SDKs

http://msdn.microsoft.com/en-gb/office/aa905496

 

CSS3 Overview + Navigation menus

January 18, 2013

All info about css3:
http://www.w3schools.com/css/

A cool navigation menu generator
http://css3menu.com/

Images for GUI design

December 19, 2012

Visual Studio Images

for MSVC 2005 in C:\Program Files\Microsoft Visual Studio 8\Common7\VS2005ImageLibrary

Windows Images

%SystemRoot%\system32\SHELL32.dll

%SystemRoot%\system32\imageres.dll

Stock Photo Companies

Stock photos are a great way to get more generic photos for use on your Web pages. They provide photos of people, products, places, and animals and are well lit and shot. And while most stock photo companies are not free, there are a few free ones and there are also some that provide high quality photos for a low price. And remember, since you’re buying photos for a Web page, you don’t need to pay for resolutions that would print well. This usually lowers the price. Some stock photo companies include:

Public Images

Finally, you can use public images on your website. Most photos taken by the government can be freely used. Be sure to check the copyright before you use them. Some public domain image sites include:

IOC Container

October 19, 2012
Autofac
 
http://code.google.com/p/autofac/

Server Tags

September 24, 2012

Server Tags:

<% %> An embedded code block is server code that executes during the page’s render phase. The code in the block can execute programming statements and call functions in the current page class. http://msdn2.microsoft.com/en-gb/library/ms178135(vs.80).aspx

<%= %> most useful for displaying single pieces of information. http://msdn2.microsoft.com/en-us/library/6dwsdcf5(VS.71).aspx

<%# %> Data Binding Expression Syntax. http://msdn2.microsoft.com/en-us/library/bda9bbfx.aspx

<%$ %> ASP.NET Expression. http://msdn2.microsoft.com/en-us/library/d5bd1tad.aspx

<%@ %> Directive Syntax. http://msdn2.microsoft.com/en-us/library/xz702w3e(VS.80).aspx

<%– –%> Server-Side Comments. http://msdn2.microsoft.com/en-US/library/4acf8afk.aspx

<%: %> Like <%= %> But HtmlEncodes the output (new with Asp.Net 4). http://weblogs.asp.net/scottgu/archive/2010/04/06/new-lt-gt-syntax-for-html-encoding-output-in-asp-net-4-and-asp-net-mvc-2.aspx

Visual Studio Editor Reformat code

August 24, 2012

Realign code:

Ctrl K+D will format the entire document.
Ctrl K+F will format the selection.