How do I use apply 2 times on the same table?-CodePudding

this is just a example chosen to be easily reproducible. As gslamed proprosed, there is solution to avoid using apply twice. But that not what I need. I need to use apply twice.

In this example, I want to take the first element of a collection (t.b) in v.c

WITH
    s (a, b)
    AS
        (SELECT 1,  'ff' FROM DUAL
         UNION ALL
         SELECT 1,  'ee' FROM DUAL
         UNION ALL
         SELECT 1,  'ee' FROM DUAL
         UNION ALL
         SELECT 2,  'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         GROUP BY s.a),
    v (a, b, c)
    AS
        (SELECT t.a, t.b, tb.c
           FROM t
                OUTER APPLY (SELECT x.COLUMN_VALUE     c
                               FROM TABLE (t.b) x
                              FETCH FIRST 1 ROW ONLY) tb)
SELECT *
  FROM v;

it's working.

then I'm counting how many elements there are in v.c

WITH
    s (a, b)
    AS
        (SELECT 1, 'ff' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 1, 'ee' FROM DUAL
         UNION ALL
         SELECT 2, 'ee' FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         GROUP BY s.a),
    v (a, b, c)
    AS
        (SELECT t.a, t.b, tb.c
           FROM t
                OUTER APPLY (SELECT count(*)     c
                               FROM TABLE (t.b) x
                              ) tb)
select * from v

Now I want to have have the first element and how many elements there are in v.c and v.d

WITH
    s (a, b)
    AS
        (SELECT 1, TO_CLOB ('ff') FROM DUAL
         UNION ALL
         SELECT 1, TO_CLOB ('ee') FROM DUAL
         UNION ALL
         SELECT 1, TO_CLOB ('ee') FROM DUAL
         UNION ALL
         SELECT 2, TO_CLOB ('ee') FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         GROUP BY s.a),
    v (a, b, c)
    AS
        (SELECT t.a, t.b, tb.c
           FROM t
                OUTER APPLY (SELECT x.COLUMN_VALUE,count(*)     c
                               FROM TABLE (t.b) x
                              FETCH FIRST 1 ROW ONLY) tb)
select * from v

ORA-00937: not a single-group group function

It's not woking and that is normal. I don't have used a group-by .

Therefore I want to calculate that with a second subselect in v.

I have tried to add two times t outer apply. But it's not working

WITH
    s (a, b)
    AS
        (SELECT 1, TO_CLOB ('ff') FROM DUAL
         UNION ALL
         SELECT 1, TO_CLOB ('ee') FROM DUAL
         UNION ALL
         SELECT 1, TO_CLOB ('ee') FROM DUAL
         UNION ALL
         SELECT 2, TO_CLOB ('ee') FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         GROUP BY s.a),
    v (a, b, c,d)
    AS
        (SELECT t.a, t.b, tb.c,tb2.d
           FROM t
                OUTER APPLY (SELECT x.COLUMN_VALUE     c
                               FROM TABLE (t.b) x
                              FETCH FIRST 1 ROW ONLY) tb)
                OUTER APPLY (SELECT count(*)     d
                               FROM TABLE (t.b) x
                              ) tb2)

select * from v

ORA-00928: missing SELECT keyword

How can I use 2 time outer apply?

code

CodePudding user response：

You just have an extra closing parenthesis; when you had this:

    AS
        (SELECT t.a, t.b, tb.c,tb2.d
           FROM t
                OUTER APPLY (SELECT x.COLUMN_VALUE     c
                               FROM TABLE (t.b) x
                              FETCH FIRST 1 ROW ONLY) tb)
--------------------------------------------------------^

... the ) I marked ends the with clause. But when you changed it to this:

    AS
        (SELECT t.a, t.b, tb.c,tb2.d
           FROM t
                OUTER APPLY (SELECT x.COLUMN_VALUE     c
                               FROM TABLE (t.b) x
                              FETCH FIRST 1 ROW ONLY) tb)
--------------------------------------------------------^
                OUTER APPLY (SELECT count(*)     d
                               FROM TABLE (t.b) x
                              ) tb2)
-----------------------------------^

.. you kept that closing parenthesis, which ended the CTE, so the second outer apply isn't in a valid place. Just remove the first marked ) and keep the second marked ).

db<>fiddle

For complicated queries I tend to indent the parentheses to make this sort of this more obvious - so the first one looks OK:

    AS
    (
       SELECT t.a, t.b, tb.c,tb2.d
       FROM t
       OUTER APPLY (
         SELECT x.COLUMN_VALUE c
         FROM TABLE (t.b) x
         FETCH FIRST 1 ROW ONLY
       ) tb
    )

but the second one doesn't:

    AS
    (
       SELECT t.a, t.b, tb.c,tb2.d
       FROM t
       OUTER APPLY (
         SELECT x.COLUMN_VALUE c
         FROM TABLE (t.b) x
         FETCH FIRST 1 ROW ONLY
       ) tb
    )
    OUTER APPLY (
      SELECT count(*) d
      FROM TABLE (t.b) x
    ) tb2
  )

and it's more obvious is should be:

    AS
    (
       SELECT t.a, t.b, tb.c,tb2.d
       FROM t
       OUTER APPLY (
         SELECT x.COLUMN_VALUE c
         FROM TABLE (t.b) x
         FETCH FIRST 1 ROW ONLY
       ) tb
      OUTER APPLY (
        SELECT count(*) d
        FROM TABLE (t.b) x
      ) tb2
    )

CodePudding user response：

Use this:

WITH
    s (a, b)
    AS
        (SELECT 1, TO_CLOB ('ff') FROM DUAL
         UNION ALL
         SELECT 1, TO_CLOB ('ee') FROM DUAL
         UNION ALL
         SELECT 1, TO_CLOB ('ee') FROM DUAL
         UNION ALL
         SELECT 2, TO_CLOB ('ee') FROM DUAL),
    t (a, b)
    AS
        (  SELECT s.a, COLLECT (s.b)
             FROM s
         GROUP BY s.a),
    v (a, b, c)
    AS
        (SELECT t.a, t.b, tb.c
           FROM t
                OUTER APPLY (SELECT x.COLUMN_VALUE,count(*) over()    c
                               FROM TABLE (t.b) x
                              FETCH FIRST 1 ROW ONLY) tb)
select * from v