MDBitz Development Resources

While continuing my exploration into the world of DB2 databases, I found myself confused as to why my basic SELECT … WHERE … statement were failing when I tried to do AND/OR clauses. After a little fumbling around I found the solution… parentheses.

Parenthesis? you ask, yes. Simply put when you want to write a compound WHERE clause using AND and/or OR statements you need to wrap each clause in parentheses. To illustrate lets start with a basic SQL select query and then we’ll show how it needs to be written for DB2

SQL Statement

SELECT first_name, last_name, id 
FROM APP_DB..APP_USERS 
WHERE active=1 
    AND  is_admin=1

DB2 SQL Statement

SELECT first_name, last_name, id
FROM APP_SCHEMA.APP_USERS
WHERE ( active = 1 ) 
    AND  ( is_admin = 1 );

As you can see for the DB2 query to function properly it needs the parentheses around the clauses while most other SQL languages don’t. Hopefully this little tidbit will save others the half hour I spent fumbling around with this little subtle syntax difference.

Back from an extended vacation and I find myself in the middle of a project needing to export data from a DB2 database. Fortunately I have decent knowledge of SQL and the basic syntax matches up but then I found myself wanting to limit the results returned. Now in T-SQL and MySQL I often would use the, LIMIT, TOP or SET ROWCOUNT commands but they aren’t available in DB2. Instead you use the FETCH FIRST command.

To put things simply to limit the number of rows returned the command is:

FETCH FIRST 10 ROWS ONLY

What this means is that your basic SELECT statement would be entered as:

SELECT * FROM mySchema.myTable WHERE TRAN_TS >= '05/01/2010' FETCH FIRST 10 ROWS ONLY

The official 1.0 release of the PHP Wrapper Library of the Harvest API is finally here. Over the last few weeks I have been performing bug fixes as well as making some functional enhancements. In this release I have added in a new HarvestReports object that allows users to query for only active/inactive clients, projects, and users. In addition there are quick methods for obtaining the active time entry of a user or for all users in your account, if you need to quickly see what Users are working on. The second major enhancement is the addition of preset time period methods to the Harvest_Range object allowing you to quickly get reports on this week, last week, and this/last month.

You can download the latest version of the library on it’s official page over in the Project Laboratory. In addition you will find many examples showing you how to configure and use the library.