Coalesce in the Group by
732375Nov 24 2009 — edited Nov 24 2009I have the need to write in the GROUP BY a column with the COALESCE function.
If I write the COALESCE or the field ORACLE gives me an error.
How I should write the COALESCE in the Group BY
SELECT PCMS_CHDOC_DATA.CH_DOC_ID,
COUNT(PCMS_CHDOC_DATA_2.CH_DOC_ID) OVER (PARTITION BY PCMS_CHDOC_DATA.CH_DOC_ID) COUNT_ID,
ROW_NUMBER() OVER (PARTITION BY PCMS_CHDOC_DATA.CH_DOC_ID ORDER BY PCMS_CHDOC_DATA_2.CH_DOC_ID) ROW_NUMBER,
PCMS_CHDOC_DATA.CH_UID,
PCMS_CHDOC_DATA.CH_DOC_TYPE,
PCMS_CHDOC_DATA.STATUS,
PCMS_CHDOC_DATA.TOOL,
PCMS_CHDOC_DATA_2.CH_DOC_ID,
PCMS_CHDOC_DATA_2.CH_UID,
PCMS_CHDOC_DATA_2.CH_DOC_TYPE,
PCMS_CHDOC_DATA_2.STATUS,
PCMS_CHDOC_DATA_2.TOOL,
PCMS_CHDOC_DATA_2.DROP_TRIGGER,
PCMS_CHDOC_DETAIL_DESC.CH_DOC_ID,
PCMS_CHDOC_DETAIL_DESC.CH_UID,
PCMS_CHDOC_DETAIL_DESC.FILE_VERSION
COALESCE(PCMS_CHDOC_DETAIL_DESC.DATA, TO_CLOB('See Description above')
FROM PCMS_CHDOC_DATA,
PCMS_CHDOC_DATA PCMS_CHDOC_DATA_2,
PCMS_CHDOC_DETAIL_DESC
WHERE PCMS_CHDOC_DATA.TOOL='ICD'
AND PCMS_CHDOC_DATA.CH_DOC_ID = PCMS_CHDOC_DATA_2.DROP_TRIGGER
AND PCMS_CHDOC_DATA.CH_DOC_ID = PCMS_CHDOC_DETAIL_DESC.CH_DOC_ID
AND PCMS_CHDOC_DATA.STATUS <> 'CLOSED'
GROUP BY PCMS_CHDOC_DATA.CH_DOC_ID,
PCMS_CHDOC_DATA.CH_UID,
PCMS_CHDOC_DATA.CH_DOC_TYPE,
PCMS_CHDOC_DATA.STATUS,
PCMS_CHDOC_DATA.TOOL,
PCMS_CHDOC_DATA_2.CH_DOC_ID,
PCMS_CHDOC_DATA_2.CH_UID,
PCMS_CHDOC_DATA_2.CH_DOC_TYPE,
PCMS_CHDOC_DATA_2.STATUS,
PCMS_CHDOC_DATA_2.TOOL,
PCMS_CHDOC_DATA_2.DROP_TRIGGER,
PCMS_CHDOC_DETAIL_DESC.CH_DOC_ID,
PCMS_CHDOC_DETAIL_DESC.CH_UID,
PCMS_CHDOC_DETAIL_DESC.FILE_VERSION
COALESCE ?
ORDER BY PCMS_CHDOC_DATA.CH_DOC_ID, PCMS_CHDOC_DATA_2.CH_DOC_ID