Tuesday, August 30, 2005

Nesting instinct

It is said that the human brain can retain only seven facts in short term memory at one time. Or something like that. Test yourself anyway with the following example, which occurs in the middle of a 175-line INSERT statement, in a 1200-line package body, all written in much the same self-explanatory style:
INSERT INTO some_unsuspecting_table
WITH all_dn AS (SELECT ... FROM ...)
SELECT ...
       CASE WHEN SUBSTR(all_dn.dn_data,6,4) IN ('PODN','UNDN')
           THEN SUBSTR(all_dn.dn_data,6,4)
           ELSE
               NVL2
               ( NVL(l.len, substr(a.features,16,15)),
                 DECODE(MAX(CASE WHEN all_dn.dn_data LIKE '%MSN%'
                                 THEN 2
                                 ELSE
                                     CASE WHEN all_dn.dn_data LIKE '%TWDN%'
                                          THEN 1
                                     END
                            END)
                        OVER (PARTITION BY NVL(l.len, SUBSTR(a.features,16,15) ) ),
                        2, 'MSN',
                        1, 'TWDN', 'LINE' ),
                 CASE WHEN all_dn.dn_data LIKE '%MSN%'
                      THEN 'MSN'
                      ELSE all_dn.dn_data
                 END )
       END main_service_type
     , ...and so on...
FROM   etc etc;
Now, how many levels of nesting was that? What, you passed out somewhere around OVER (PARTITION BY NVL(l.len, SUBSTR(a.features,16,15) ) )? Shame on you.

1 comment:

Laurent Schneider said...

So true! Even if the author can read what he wrote, because he knew what he wanted, the code is unreadable.

A great demo of an unreadable script! Thank you