oracle query with multiple subqueries

Question

tryng to select table rows and then use subqueries to generate single row list (item1,item2,item3)

anyway sql:

select 
  username,
  concat(firstname || ' ', lastname) as name,
  email,
  phone,
  (
  select 
    ltrim(sys_connect_by_path(res, ', '), ', ')
  from (
    select 
      count(*) over() as cnt, 
      row_number() over(order by ofield) as rnum, 
      (select name from rooms where code=roomcode) as res 
    from adminrooms
    where roomcode=admins.code) /*admins.code - come from main query but it gives error: invalid identifier*/
  where cnt=rnum start with rnum=1 connect by prior rnum=(rnum-1)
  ) as groups
from admins
where frozen=0 and (type <> 'root' or type is null)

problem seems to be the main query field 'code' from table 'admins' don't work inside list generation query

Answer 1

I guess that there is no column "code" in the "admins" table... it's just a little mistake. If there was, you wouldn't want to join with the roomcode of adminrooms, rather something like "admincode=admins.code".

Answer 2

I can reproduce your findings with a simple example. Consider:

SQL> SELECT (SELECT d1.dummy FROM dual d2) d2
  2    FROM dual D1;

D2
--
X

This works because the subquery "d2" can see the rows of the main query "d1" but if we add a level I get the same error as you:

SQL> SELECT (SELECT NULL FROM (SELECT d1.dummy FROM dual d3))
  2  FROM dual D1;

SELECT (SELECT NULL FROM (SELECT d1.dummy FROM dual d3))
FROM dual D1                     ~

ORA-00904: "D1"."DUMMY": invalid identifier

Here the subquery "D3" can not see the values of the row from "D1".

You will have to modify your query:
* join admin with adminrooms and then use the sys_connect_by_path or
* write a function that will take a code as parameter and will output the result of your select.

Provide us with CREATE TABLE and sample data if you want an example.

Answer 3

table layout: picture

this is the basic layout of tables and what I want to get out of them is at the end.

joining tables seems like a good idea here, so if someone can show me how to do it.

Answer 4

Based on your db diagram, the best way to do it is to use a custom string aggregation function and then just do a group by. This is similar to mysql group_concat.

If you make a function called string_agg() using the above link, you can use it as below in your code:

select a.username, string_agg(c.name) from admins a, adminrooms b, groups c
where a.code=b.admincode
and b.groupcode=c.code
group by a.username

Here is the string_agg function create script. Just run this as a script and you will have the function above (taken from the link shown above):

CREATE OR REPLACE TYPE t_string_agg AS OBJECT
(
  g_string  VARCHAR2(32767),

  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
     RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER
);
/
SHOW ERRORS


CREATE OR REPLACE TYPE BODY t_string_agg IS
  STATIC FUNCTION ODCIAggregateInitialize(sctx  IN OUT  t_string_agg)
    RETURN NUMBER IS
  BEGIN
    sctx := t_string_agg(NULL);
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(self   IN OUT  t_string_agg,
                                       value  IN      VARCHAR2 )
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := self.g_string || ',' || value;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(self         IN   t_string_agg,
                                         returnValue  OUT  VARCHAR2,
                                         flags        IN   NUMBER)
    RETURN NUMBER IS
  BEGIN
    returnValue := RTRIM(LTRIM(SELF.g_string, ','), ',');
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(self  IN OUT  t_string_agg,
                                     ctx2  IN      t_string_agg)
    RETURN NUMBER IS
  BEGIN
    SELF.g_string := SELF.g_string || ',' || ctx2.g_string;
    RETURN ODCIConst.Success;
  END;
END;
/
SHOW ERRORS


CREATE OR REPLACE FUNCTION string_agg (p_input VARCHAR2)
RETURN VARCHAR2
PARALLEL_ENABLE AGGREGATE USING t_string_agg;
/
SHOW ERRORS

Answer 5

Try to rewrite you subquery

   (
    select 
      count(*) over() as cnt, 
      row_number() over(order by ofield) as rnum, 
      (select name from rooms where code=roomcode) as res 
    from adminrooms
    where roomcode=admins.code)

as table in main query like

 ... from admins, 
       (
        select 
          roomcode,
          count(*) over() as cnt, 
          row_number() over(order by ofield) as rnum, 
          (select name from rooms where code=roomcode) as res 
        from adminrooms) t
 where t.roomcode = admins.code...

And use wmsys.WM_CONCAT() instead of sys_connect_by_path()