Wednesday, June 16, 2010

ADS 10 Tip #13 – Boolean Expressions in the Select List

You can now put Boolean expressions in the SELECT list of any SQL statement. This allows you to see if a condition is true without having to filter the entire resultset. You can use any expression that returns a Boolean result.

// Using Boolean expression in Select List
SELECT Name, State, (State = 'ID') AS GemState FROM Company Order by 3 desc;

// Show true if the employee has worked at the company for 15 or more years
SELECT LastName, FirstName, ( TIMESTAMPDIFF( SQL_TSI_YEAR, DateOfHire, NOW()) > 14) 
  As "15+ Years" FROM Employee

The result of the second query is shown below

BooleanExpr

For a demonstration of the new SQL features in Advantage 10 you can watch this screencast on the DevZone.

No comments: