sqloracle-databasesql-functioninline-view

Returning a row from a With clause


I'm trying to make a Function that retruns a value.

In my function I have this script:

WITH t_new AS
    (
      SELECT PersIDOLD, PersIDNEW, RightsMUT,
      SUM(gap) over(ORDER BY PersIDOLD, PersIDNEW) grp

      FROM
        (
          SELECT h1.*,
            CASE
              WHEN h1.PersIDNEW = lag(h1.PersIDNEW) 
                                    over(ORDER BY h1.PersIDOLD, h1.PersIDNEW)
                OR h1.PersIDOLD = lag(h1.PersIDOLD) 
                                    over(ORDER BY h1.PersIDOLD, h1.PersIDNEW)
              THEN 0
              ELSE 1
            END gap
          FROM HelpTable1 h1

        )
    )
  SELECT PERSIDNEW, RIGHTSMUT
  FROM t_new
  WHERE grp =
    (
      SELECT grp FROM t_new WHERE PersIDNEW = 12 -- going to be a variable
    )

the return is like this:

http://img59.imageshack.us/img59/6733/tablei.gif

Now I want to return row 3 coze it has a 1 in row RightsMUT

How could I do this? I tried to do it with 2 With but oracle does not support 2 with yet.


Solution

  • My solution:

      WITH t_new AS
        (
          -- Alles durch gehen und
          SELECT PersIDOLD, PersIDNEW, RightsMUT, SUM(gap) over(ORDER BY PersIDOLD, PersIDNEW) grp
          FROM
            (
              SELECT h1.*,
                CASE
                  WHEN h1.PersIDNEW = lag(h1.PersIDNEW) over(ORDER BY h1.PersIDOLD, h1.PersIDNEW)
                    OR h1.PersIDOLD = lag(h1.PersIDOLD) over(ORDER BY h1.PersIDOLD, h1.PersIDNEW)
                  THEN 0
                  ELSE 1
                END gap
              FROM LUTAXKT.HILFSTABELLE1UMSETZUNG h1
                --WHERE h1.updateflag = 0
            )
        )
      SELECT PersIDNEW, RightsMUT
      FROM t_new
      WHERE  RightsMUT = 1 and
       grp =
        (
          SELECT grp FROM t_new WHERE PersIDNEW = 12
        )