Oracle’s Lag in MS SQL Server 2005

I am currently porting our new database from Oracle 10g to MS SQL Server 2005, and I have it all done except the views that use the Oracle LAG and LEAD functions (non-ANSI).

What these functions provide (for the MS SQL camp) is the ability to get the next or previous rows when sorted. In my case I have a value that is the ‘volume change since the start’ at time intervals, and I want the relative change between each interval.

So PL/SQL of the view is:

SELECT t.*,
    t.volume - LAG(volume) OVER (PARTITION BY group_number  ORDER BY timestamp) AS volume_change
FROM volume_table t;

The partition clause splits the data into different buckets, then each bucket is sorted, with all results returned.

Asking on the NZ .Net User Group mailing list I got a pointer to this MS feedback page, but the solution presented there gives me an error “Incorrect syntax near ‘ROWS’.” when I run this query against SQL 2K5

SELECT MIN(volume) OVER(PARTITION BY group_number  ORDER BY timestamp
                        ROWS BETWEEN 1 PRECEDING  AND 1 PRECEDING) change
FROM volume_table; GO

I had a side point showing why I wanted to avoid sub-select, as the performance of a different query had an orders of magnitude improvement from changing to using a LAG function, yet that same sub-select query runs just as fast as the “improved” Oracle statement in MS SQL Server, so I’ll just stick to the main topic, and post about that another day…

Chris recently showed how to use Common Table Expressions (CTE) (sort of auto-magic temp table) to find the first entries for a day, which is very close to what I was want, but the filtering is hard coded.  I could not see how to make it dynamic, so I used the idea, and started massaging the concept, till I finally got what I wanted.

Conceptually the Oracle solution could be done using cursors under the hood to provide the rolling previous (LAG) rows, where-as here I’m doing many look-ups but the table is not getting re-created as in the nested select method.

So my code is as follows:

WITH Rows( vol_diff, time, rn, gn ) AS (     SELECT v.volume,
        v.timestamp,
        Row_Number() OVER (PARTITION BY group_number
                           ORDER BY timestamp),
        group_number
    FROM volume_table v
), PrevRows( timestamp, prev_vol, group_number) AS (     SELECT a.time, b.vol_diff, a.gn
    FROM Rows a
    LEFT JOIN Rows b  ON a.rn = b.rn + 1  AND a.gn = b.gn
) SELECT v.*, v.volume - p.prev_vol as volume_change
FROM volume_table v
LEFT JOIN PrevRows p
    ON v.timestamp = p.timestamp
    AND v.group_number = p.group_number; GO

So I use two CTE tables, one to partition and sort the data, the second to do a lag based join, then I can select the lagged based data, by matching the time and group to the current entry.It works a treat, and I will do some performance testing tomorrow once my production data has finished loading into my db.

After the results of the not discussed query I expect that the sub-select will be just as performant.

SELECT * FROM USER_ERRORS

The most important Oracle pl/sql right now is:

SELECT * FROM USER_ERRORS

I’ve been “developing” some new functions, and I was getting sick and tired of being told:

Warning: execution completed with warning
function MissingProd Compiled.

Now I can know what’s wrong, and fix it, before reloading my complete scheme.   Yay!

Oracle SQL Developer

Having been developing my PL/SQL in Toad Data Modeler, and running via a customer VB program, the debugging effort has been poor at best. A co-worker pointed me to Oracle SQL Developer, it’s in the league as MS SQL Server Management Studio, but for Oracle!

The other point I’ve been tripping on, in PL/SQL to use a date (via SQL*Plus) to_date(‘2007-08-06′,’YYYY-MM-DD’) is the trick.

Remote connection to Oracle 10g Express via ODBC

For some strange reason the Oracle 10g Express installation will not run the configuration web client for me but it works all fine for the testing folks. But it installs happily in a virtual PC, and it runs/configures just fine.

Now to connect from my PC I need some client stuff. On the Oracle 10g Express page it has a client download, which you would think would do the business. But does not. The current approach in the office is to install the complete Express server and just ignore the server parts. That idea stinks, so with some help from a co-worker I got the correct option work.

Install the Instant Client, for ODBC (god bless VB6.0 applications) you need to:

  1. Download the Instant Client Package – Basic and Instant Client Package – ODBC
  2. unzip them to C:\Oracle\ and the will make a instantclient_10_2 subdirectory. The resulting directory C:\Oracle\instantclient_10_2 will be referred to as <dir> from now on.
  3. run <dir>\odbc_install.exe
  4. create a file in <dir> called tnsnames.ora with the contents
    Name Of TNS =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = your_server_name)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = XE)
    )
    )
    replacing your_server_name with the name/ip address of you remote server. You can name you TNS with a sensible name by replacing Name Of TNS
  5. Add <dir> to the PATH environment variable
  6. Add new environment variable TNS_ADMIN with value <dir>
  7. Now you can use the Data Source (ODBC) tool to setup the DSN like via the Oracle in instantclient 10_2 Driver

Done!

Updated: Changed the naming the TNS part to be correct.