У меня есть три оператора LISTAGG, которые выглядят так:
LISTAGG (ga.FULL_APPL_NUM, ',') WITHIN GROUP (ORDER BY ga.FULL_APPL_NUM) AS "FullApplNums",
LISTAGG (ga.APPL_ID, ',') WITHIN GROUP (ORDER BY ga.FULL_APPL_NUM) AS "ApplIds",
LISTAGG (ga.PROJECT_TITLE, ',') WITHIN GROUP (ORDER BY ga.FULL_APPL_NUM) AS "ProjectTitles",
Результаты выглядят так:
FullApplNums
2r01HL02858573-23A1,5R01HL02857324, ...
Приложения
7219924,6718409, ...
ProjectTitles strong>
ProjectTitle1, ProjectTitle2, ...
Все эти столбцы ссылаются на одну и ту же запись из одной таблицы в объединении примерно с 4 таблицами, но все они взяты из одной и той же таблицы - ga.
Мне нужен еще один список agg, чтобы заполнить еще один столбец, но он исходит из другой таблицы с псевдонимом pp. Можно ли LISTAGG снова ссылаться на другую таблицу и как мне синхронизировать значения, поскольку списки с разделителями-запятыми используются как массивы индексов. Итак, я могу создать запись из ga ["FULL_APPL_NUM"] [0], ga ["APPL_ID"] [1], ga ["PROJECT_TITLE"] [0].
Теперь мне нужен ga ["IsContact"] [0], но он будет исходить из таблицы pp, а не из ga.
Или можно выполнить дополнительный запрос выбора, например: SELECT * FROM pp, где идентификатор_приложения в (ApplIds) - где ApplIds является результатом агрегации списка.
Вот весь запрос на данный момент:
PROCEDURE GetPrincipalInvestigators
(
loginId IN VARCHAR2 := NULL,
portfolioId IN NUMBER := NULL,
portfolioType IN VARCHAR := NULL,
ic IN VARCHAR2 := NULL,
startRow IN INT := NULL,
endRow IN INT := NULL,
sortField IN VARCHAR2 := NULL,
sortDirection IN VARCHAR2 := NULL,
PrincipalInvestigators_CUR IN OUT SYS_REFCURSOR
)
IS
v_APPL_ID NUMBER(10,0) := 0;
v_ADMIN_PHS_ORG_CODE VARCHAR2(2 BYTE) := ' ';
v_SERIAL_NUM NUMBER(6,0) := 0;
v_Proj_Appl_Rec Proj_Appl_Rec;
v_Proj_Appl_Tab Proj_Appl_Tab := Proj_Appl_Tab();
v_Proj_Appl_Cur SYS_REFCURSOR;
v_sortField VARCHAR2(50 BYTE) := NULL;
v_sortDirection VARCHAR2(4 BYTE) := NULL;
v_cnt NUMBER := 0;
v_orderBy VARCHAR2(200 BYTE) := ' ORDER BY ';
v_sql CLOB := ' ';
BEGIN
IF sortField IS NULL THEN
v_sortField := 'LAST_NAME';
ELSE
v_sortField := sortField;
END IF;
IF sortDirection IS NULL THEN
v_sortDirection := 'DESC';
ELSE
v_sortDirection := sortDirection;
END IF;
v_orderBy := v_orderBy || v_sortField || ' ' || v_sortDirection;
--DBMS_OUTPUT.PUT_LINE(v_orderBy);
IREPORT_PORTFOLIOS.GetPortfolioAppsAndProjects
(
loginId => loginId,
portfolioId => portfolioId,
portfolioType => portfolioType,
ic => ic,
AppIds_CUR => v_Proj_Appl_Cur
);
LOOP
FETCH v_Proj_Appl_Cur
INTO v_APPL_ID, v_ADMIN_PHS_ORG_CODE, v_SERIAL_NUM;
EXIT WHEN v_Proj_Appl_Cur%NOTFOUND;
v_Proj_Appl_Tab.extend;
v_cnt := v_cnt + 1;
v_Proj_Appl_Tab(v_cnt) := Proj_Appl_Rec(v_APPL_ID, v_ADMIN_PHS_ORG_CODE, v_SERIAL_NUM);
END LOOP;
CLOSE v_Proj_Appl_Cur;
OPEN PrincipalInvestigators_CUR FOR
WITH projects_CTE
AS
(
SELECT DISTINCT
pa.APPL_ID,
pa.ADMIN_PHS_ORG_CODE,
pa.SERIAL_NUM
FROM TABLE(v_proj_appl_tab) pa
)
SELECT
pp.PERSON_PROFILE_ID AS PersonProfileId,
pp.LAST_NAME, FIRST_NAME,
(pp.LAST_NAME || ' , ' || pp.FIRST_NAME) AS InvestigatorName,
pp.ORG_NAME AS PrimaryEmployer,
pp.EMAIL_ADDR AS Email,
pp.PHONE_NUM AS Phone,
COUNT(*),
LISTAGG (ga.FULL_APPL_NUM, ',') WITHIN GROUP (ORDER BY ga.FULL_APPL_NUM) AS "FullApplNums",
LISTAGG (ga.APPL_ID, ',') WITHIN GROUP (ORDER BY ga.FULL_APPL_NUM) AS "ApplIds",
LISTAGG (ga.PROJECT_TITLE, ',') WITHIN GROUP (ORDER BY ga.FULL_APPL_NUM) AS "ProjectTitles"
FROM projects_CTE pcte
JOIN APPL_PIS apis
ON pcte.APPL_ID = apis.APPL_ID
JOIN PERSON_PROFILES pp
ON pp.PERSON_PROFILE_ID = apis.CONTACT_PI_PROFILE_PERSON_ID
JOIN GRANT_APPLS ga
ON ga.APPL_ID = pcte.APPL_ID
WHERE
ga.APPL_TYPE_CODE <> 3 -- Non Supplement
AND rownum < 100
GROUP BY PERSON_PROFILE_ID, LAST_NAME, FIRST_NAME, ORG_NAME, EMAIL_ADDR,PHONE_NUM ;
END GetPrincipalInvestigators;
Извините, это сложно объяснить. Может быть, у кого-то будет лучший подход, чем этот.
pp
запись с каждойga
? Если да, то по какой причине вы думаете, что не можете получить ещеlistagg
? - person Alex Poole   schedule 10.08.2015