Returning a row from a With clause

Question

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.

score 0 · Answer 1 · answered May 21 '10 at 10:19

0

Let me try to guess - pls check if the following gives you what you need:

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 PersIDNEW = 12 AND RightsMUT = 1

answered May 21 '10 at 10:19

Unreason

12,556
2
34
50

this does return nothing but i found a way how to do it... and it was so simple :/ – domiSchenk May 21 '10 at 11:02
ok, if you solved it either update the question to show the answer (you might get some comments on it) or post it as the answer to your own question. – Unreason May 21 '10 at 12:41

score 0 · Accepted Answer · edited Dec 12 '11 at 19:47

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
    )

Returning a row from a With clause

2 Answers2