[SQL] Reuse query with different filters in same SELECT

Say you need to create one of those queries that produce a report or some statistics about the data stored in your DB tables. You will most likely need to reuse the same query but with different filters every time and gather the results in a single SELECT.

You will have a base query which gathers all the data you're interested into (the "total" one) and need to filter it multiple times (even on the same column) with different values.

[SQL] WITH clause query alias

In SQL you can easily define an alias for a table or column and just as easily for a query or subquery. This can be useful when joining two datasets (SQL ANSI-92 standard):

SELECT *
FROM (SELECT column1, column2
    FROM table1
    [WHERE]) query1
JOIN (SELECT column1, column3
    FROM table2
    [WHERE]) query2
ON query1.column1=query2.column1

[SQL] Oracle function return table dataset multiple rows

In Oracle, procedures may return multiple values as OUT parameters while functions should always return a single value. Functions CAN however return multiple values, but they must be enclosed in a TABLE object which is the "single value" to be returned.

[SQL] SQL Server change collation during query

When working with multiple Microsoft SQL Server databases, you may encounter an error which states something like "Cannot resolve collation conflict for equal to operation".

This happens when the two databases have different collations and you try to compare strings between them.

Now, you can change the server collation, database collation or column collation but it won't immediately solve the problem. If you just need to query data combining both databases, you can always use the COLLATE keyword:

SELECT * from DB1.DBO1.TABLE t1, DB2.DBO2.TABLE2 t2
WHERE t1.field=t2.field COLLATE [collation_name]; 

[SQL] SQL Server String concatenation and functions

In Oracle, you can concatenate two strings with ||

eg: SELECT 'string1'||'string2' FROM DUAL;

In Microsoft SQL Server you should: use (string1+string2) DON'T forget the parentheses

eg: SELECT ('string1'+'string2');

On MSDN you can also find a complete list of all SQL Server String functions.

[SQL] SQL Server SELECT FROM DUAL

In Oracle, you can easily test functions or simple statements with:

SELECT [things to test] FROM DUAL WHERE [conditions];

eg: SELECT LENGTH('string') FROM DUAL;

But Microsoft SQL Server doesn't have the FROM DUAL syntax; instead you should:

SELECT [thing to test] WHERE [conditions];

eg: SELECT LEN('string');

[SQL] Difference in months/years between two dates

In Oracle SQL, you can easily calculate the difference in months between two dates by using the MONTHS_BETWEEN function:

SELECT MONTHS_BETWEEN (date1, date2)

Note that if the difference is X months and Y days, you'll get a float number. You can truncate it with FLOOR or CEIL or ROUND(float_number, 0) depending on your needs.

Should you be looking for the difference in years, you can use the EXTRACT function:

SELECT EXTRACT(YEAR FROM date1) - EXTRACT(YEAR FROM date2)

This will obviously always return an integer.

If you're using Microsoft SQL Server, you may find DATEDIFF useful instead.

[Java] Sort Map by Keys

If you have a Map (usually a HashMap since a TreeMap is already sorted) and need to sort it by keys, you will find that there's no method you can call directly on it.

What you can do however, is to create a SortedSet (we'll use a TreeSet) of the Map's keys and then use it alongside the Map.

Example:

myMap is a HashMap<K,V>

//Create sorted set from myMap - this already sorts the keys!
SortedSet mySortedMap = new TreeSet(myMap.keySet());
//Loop on it
for(Iterator iter = mySortedMap.iterator(); iter.hasNext();){
     //Obviously, replace K and V with your data types
     K key = (K) iter.next();
     V value = (V) myMap.get(key);
     /* do what you need */
}

If you are luckier than me and use Java 5+ you can type all collections directly in the declaration with the <K,V>syntax and also use the for-each loop to cycle through the sorted set
for(K key : mySortedMap){
     V value = (V) myMap.get(key);
     /* do what you need */ }