запрос оракула с несколькими подзапросами

tryng, чтобы выбрать строки таблицы, а затем использовать подзапросы для создания списка одиночных строк (item1, item2, item3)

в любом случае 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)

проблема, похоже, является основным полем запроса «код» из таблицы « выберите a . имя пользователя , string_agg ( c . name ) от админов a , adminrooms b , groups c где a . code = b . admincode и b . groupcode = c . код группы по . имя пользователя s 'не работает внутри запроса генерации списка

oracle,

2

Ответов: 4

0 принят

На основе вашей диаграммы db лучший способ сделать это - использовать пользовательскую функцию агрегации строк, а затем просто выполнить группу. Это похоже на mysql group_concat.

Если вы используете функцию string_agg (), используя приведенную выше ссылку, вы можете использовать ее, как показано ниже в вашем коде:

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

Вот скрипт create_agg create script. Просто запустите это как скрипт, и у вас будет функция выше (взята из ссылки, показанной выше):

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

D2
--
X
2

Я могу воспроизвести ваши результаты на простом примере. Рассматривать:

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

Это работает, потому что подзапрос «d2» может видеть строки основного запроса «d1», но если мы добавим уровень, я получаю ту же ошибку, что и вы:

admin

Здесь подзапрос «D3» не может видеть значения строки из «D1».

Вам придется изменить свой запрос:
* присоединиться к adminс adminroomsи затем использовать sys_connect_by_pathили
* написать функцию , которая будет принимать код в качестве параметра и будет выводить результат вашего выбора.

Предоставьте нам CREATE TABLE и образцы данных, если вы хотите привести пример.

0

Я думаю, что в таблице «admins» нет столбца «code» ... это всего лишь небольшая ошибка. Если бы это было так, вы бы не захотели присоединиться к номеру комнаты администратора, скорее, что-то вроде admincode = admins.code.

0

Попробуйте переписать подзапрос

   (
    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)

как таблица в основном запросе типа

 ... 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...

И использовать wmsys.WM_CONCAT()вместоsys_connect_by_path()

оракул,
Похожие вопросы
Яндекс.Метрика