Dispatching data between columns of a query (wow!)

mike123abc · December 13th, 2003, 07:14 AM

Hi,

Is there a way to :

(a) take data from the same column of a table and put it in various columns of a query, based on some criteria you specify

Example :

Query has 2 columns, "Debit" and "Credit". If account balance in querried table is > 0, write value in column "Debit", if not, write in column "Credit"

(b) take data from several table columns and put it in the same column of the query, based on some criteria

Example :

If column "I" of a query yields 0 for the current line of the query, then look in column "J" of same table (or different ?) and read that one

I feel that this is similar to an IIF function or something.

It might be that these are elementary questions, but can U help me ?
Thanx

Mike

Jeff Mason · December 13th, 2003, 07:36 AM

The CASE expression will allow you to do what you want.

For your debit/credit query, something along the lines of:

Code:

SELECT 
    CASE WHEN balance>0 THEN balance ELSE NULL END as Debit,
    CASE WHEN balance<0 THEN balance END as Credit
    ...

The CASE expression will evaluate each condition and return as its result the first one which evaluates to TRUE. If no conditions are TRUE, CASE returns NULL. Thus, the two CASE expressions above are equivalent; one explicitly has an ELSE clause to handle situations where no WHEN clause evaluates to TRUE, the second does the same thing implicitly.

Your second example:

Code:

SELECT
    CASE WHEN columnI=0 THEN ColumnJ ELSE columnK END as somecolumn,
    ...

Note that 'ColumnJ' is a scalar expression, so it could be just about anything, including a subquery referencing another table.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com