Re: tricky SQL query (currently handled through PL/SQL)

From: Jetlag <jetlag11235_at_yahoo.com>
Date: 12 Jun 2003 15:01:34 -0700
Message-ID: <2eeed00e.0306121401.3c06d498_at_posting.google.com>

I combined the solutions from bung ho and ralf to create the following query. Much thanks to both of you. Things to note:

LINE
 4 - "sysdate+NULL" is used to avoid an automatic cast (to_char) of md

 8,9 -   using nvl prevents two extra OR branches in the WHERE clause
 8,9 -   the nvl default of "x" because "x" is not used in my app
 18  -   the "order by" clause is not needed, but clarifies the output
 *   -   the compound column (mn, mc) is glued with the (elsewhere)

unused character "x" to avoid confusion, e.g. (1, 11) vs. (11, 1)

SELECT mn MY_NAME, mc MY_CODE, t_1, t_2
  FROM (

        SELECT mn, mc,
               decode(b, mn||'x'||mc, sysdate+NULL, md) T_1,
               decode(a, mn||'x'||mc, lead(md) over (order by mn, md),
md) T_2
          FROM (
                SELECT my_name MN, my_code MC, my_date MD,
                       nvl(lag(my_name||'x'||my_code)
                           over (order by my_name, my_date), 'x') B,
                       nvl(lead(my_name||'x'||my_code)
                           over (order by my_name, my_date), 'x') A
                  FROM my_table_v1
               )
         WHERE b != mn||'x'||mc
            OR a != mn||'x'||mc
       )

 WHERE t_1 IS NOT NULL
 ORDER BY mn, t_1;

Thanks again for all the help.

• jetlag --

Received on Fri Jun 13 2003 - 00:01:34 CEST