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.
Tuesday, August 30, 2005
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:
Posted by William Robertson at 5:22 pm