Programming

I have a table with the following structure:

trxNo expiryDate remoteTrxNo
445455 2011-06-26 0
445455 2011-02-26 0
445999 2011-07-30 0
445999 2011-03-28 0

There are other columns that make the records unique but they are not relevant to my question, however, they are always in pairs.

I would like to update the remoteTrxNo in all the records in the table with the following logic:

If the expiryDate is the largest of the two possible values, write 'FAR' in the remotetrxNo column, when it is the smallest, write 'NEAR'.

Can this be done in a massive insert? Do I need to split the query in two to do the MAX once, and the MIN on the other update?

I've tried without success to write a query (or two) to accomplish this. I want to avoid writing code that will loop through a resultset one by one.

Any help would be appreciated.

If you are using Oracle 10g or later, you can use analytic functions like rank, mix and max.

Code:

  1  select ename, sal, deptno
  2   , decode(min(sal)over(partition by deptno), sal, 'MIN')
  3   , decode(max(sal)over(partition by deptno), sal, 'MAX')
  4* from emp

ENAME             SAL     DEPTNO DEC DEC
---------- ---------- ---------- --- ---
CLARK            2450         10
KING             5000         10     MAX
MILLER           1300         10 MIN
JONES            2975         20
FORD             3000         20     MAX
ADAMS            1100         20
SMITH             800         20 MIN
SCOTT            3000         20     MAX
WARD             1250         30
TURNER           1500         30
ALLEN            1600         30
JAMES             950         30 MIN
BLAKE            2850         30     MAX
MARTIN           1250         30

14 rows selected.

Thanks for the reply. Unfortunately, this is with Sybase, so I can't use those functions, nor cans I use PLSQL.

1) Google search
URL => sybase equivalent for oracle decode - Google Search

Answer => Case()

2) Google search
URL=>sybase olap functions - Google Search

Answer=> http://www.sybase.com/files/White_Papers/olap.pdf

Time spent: 2 minutes.

Usefulness of googlng decode and olap: 0

Thanks for trying.

I am aware of the use of case. I was already using it I just dumbed down the example to attempt to prevent simplistic answers like the last one. FAIL!

Anyway, I solved my problem by using a temporary table so that sybase wasn't confused by the table aliases which was at the root of the initial problem.