Faster SQL Updates using ROWID April 7, 2007
Posted by Duncan in Oracle, PeopleSoft, SQL.comments closed
Most UPDATE statements get written using the technique:
UPDATE <recname>
SET <fieldname> = <field, expression or statement>
WHERE <where clause>
However, if the value that you’re setting the field to is a fairly complex SQL statement of its own there may be a better way (particularly if you have an Oracle DB). You can take advantage of the fact that ROWID is the fastest way to locate a row (faster even than the primary key). Run the first bit of SQL to select the rowid of the row to update and the value to update it to, and then the second bit to run the updates. This can be done either as 2 SQL blocks or with an App Engine Step containing DoSelect and SQL actions.
Incrementing Counts in SQL March 25, 2007
Posted by Duncan in Oracle, SQL.comments closed
Fairly frequently (particularly during Data Migration when deriving new key values) you have to rank and number rows within a particular key value. For instance, Key1 may have three rows – and these should be given a value of 1, 2 & 3. Key 2 however may have a different number of values but these should start again from 1. Example shown in this table:
| Value | RowNum |
| Key1 | 1 |
| Key1 | 2 |
| Key1 | 3 |
| Key2 | 1 |
| Key3 | 1 |
| Key3 | 2 |
| Key3 | 3 |
| Key3 | 4 |
The Rownum field can be achieved in Oracle using the following SQL:
SELECT <Key1>
, rank() over (partition by <Key1> order by <OrderField> )
FROM <recname> k
As well as the RANK() function, there is a DENSE_RANK() function. The latter doesn’t leave gaps when there is a tie, for example RANK may give the output 1,2,2,4 where DENSE_RANK would give 1,2,2,3. The highest rank value provided by a DENSE_RANK statement would be the number of unique values.
The PeopleSoft Tipster Blog 