It’s not often that I mention anything technical or work related on my blog, but this confused me because it’s counter-intuitive until I understood it and now I’m quite impressed, so it’s worth sharing.
I’m currently designing a system to deal with Equilend Criteria Based returns trading ( RTCB).
Basically, Equilend is an external system owned by various investment banks including JP Morgan, Morgan Stanley etc and facilitates easier trading between the banks who subscribe to system by cutting costs of developing separate systems.
All an individual bank has to do is integrate this feed with theirs to manage their trading of stocks, equity’s etc to maintain their positions.
Until recently the Equilend would send a return message when a borrowing bank wanted to return the stock to a specific loan, providing the trade_id and all of that loan would be returned.
This was somewhat inflexible, because what happens when the borrower only wants to return a specific quantity of the loan?
The new interface RTCB allows the borrower to return the stocks based on various criteria, eg what fees or loan rate charged and for the lender to manage the loan accordingly.
Upshot is, the criteria is complex based on stock, quantity, returned quantity, loan rates, loan types, fee rates, counterparty’s, callable flags, tax voucher percentages etc.
That’s all background, this brings me to the technicalities.
I’ve been developing Oracle database application for 20 years, it’s what I do for a living and it pays pretty well.
So for the past couple of weeks, I’ve been putting this interface together, loading up the RTCB message into a database table and then writing code to identify the criteria and process the return accordingly.
However it’s more complex than it appears, because we have to exact match on each criteria provided, whilst not excluding criteria which hasn’t been provided. Additionally not all criteria on the various types of loans are mandatory.
Most of identification process can be handled with Oracles built in NVL, DECODE, CASE etc.
But it becomes particularly complicated when the return criteria is optional and the data on the loan is also optional .. what do you compare against?
Historically, I’d do this with DECODES and NVLs and pick an arbitrary value for when both are NULL and code around it.
But today, I was thinking there has to be a better way and did some research on new Oracle functionality and came across the LNNVL – Logical Not Null Value.
I’ve put together a test table LMM_TEST and some queries to demonstrate its use below.
The first query has no criteria and shows all of the test data including the nulls and matched and unmatched criteria.
The second query has an LNNVL( value1 = value2) and although the NULLS are returned the matched value is NOT returned.
The third query has an LNNVL( value1 != value2) ( That != means not equals ) and returns the NULLS also the matched values, this is counter-intuitive because it’s returning values when the criteria is that they are not equal until you take time to understand it.
Summary, the LNNVL is a great function for when dealing with complex criteria which may or may not be supplied, but you need to know how to use it properly.
—
create table lmm_test
( id number, value1 number, value2 number);
insert into lmm_test values( 1, null, null); — Both NULLS
insert into lmm_test values( 2, 2, null); — A Single NULL
insert into lmm_test values( 3, null, 3); — A Single NULL on other record
insert into lmm_test values( 4, 4, 4); — Matched Values
insert into lmm_test values( 5, 5, 6); — Unmatched values
— 1 – Show all the data
select l.* from lmm_test l
order by 1;
ID VALUE1 VALUE2
———- ———- ———-
1
2 2
3 3
4 4 4
5 5 6
— 2 – Show how it handles nulls and equal criteria – This doesn’t provide the correct results set
select l.* from lmm_test l
where lnnvl( value1 = value2 )
order by 1;
ID VALUE1 VALUE2
———- ———- ———-
1
2 2
3 3
5 5 6
— 3 – Show how it handles nulls and NOT equal criteria – This DOES provide the correct results set
select l.* from lmm_test l
where lnnvl( value1 != value2 )
order by 1;
ID VALUE1 VALUE2
———- ———- ———-
1
2 2
3 3
4 4 4
DancingBhoy 