CREATE OR REPLACE VIEW SOWCUSTOMERPROJECTVIEW
AS
SELECT
OPPRTNTY.CUST_ACNT_NBR,
OPPRTNTY.OPPRTNTY_ID,
OPPRTNTY.EXT_OPPRTNTY_ID,
OPPRTNTY.OPPRTNTY_NM,
OPPRTNTY.OPPRTNTY_DSC,
LISTAGG(PROJECT.PROJECT_NM, ', ') WITHIN GROUP (
ORDER BY
PROJECT.PROJECT_NM) AS SUB_PRODUCT,
LISTAGG(BATCH_CLASS.BATCH_CLASS_NM, ', ') WITHIN GROUP (
ORDER BY BATCH_CLASS.BATCH_CLASS_NM)
AS SUB_PRODUCT2,
--PROJECT.PROJECT_NM,
--BATCH_CLASS.BATCH_CLASS_NM,
COUNT(PROJECT.PROJECT_ID)
AS cnt
FROM OPPRTNTY
INNER JOIN PROJECT
ON OPPRTNTY.OPPRTNTY_ID =
PROJECT.OPPRTNTY_ID
INNER JOIN BATCH_CLASS
ON
BATCH_CLASS.BATCH_CLASS_ID = PROJECT.BATCH_CLASS_ID
GROUP BY
OPPRTNTY.CUST_ACNT_NBR,
OPPRTNTY.OPPRTNTY_ID,
OPPRTNTY.EXT_OPPRTNTY_ID,
OPPRTNTY.OPPRTNTY_NM,
OPPRTNTY.OPPRTNTY_DSC;
--PROJECT.PROJECT_NM,
--BATCH_CLASS.BATCH_CLASS_NM ;
No comments:
Post a Comment