TRIM is not working under MAX function in Oracle

Question

I have a select query where I want to TRIM one column, which is already as MAX function. I tried like the below but it's not working.

SELECT MAX( id ) KEEP (  -- 102436
         DENSE_RANK LAST
         ORDER BY TENANCY_CHANGEDDATE NULLS FIRST,
                  RRH_CHANGEDDATE    NULLS FIRST,
                  ID_OD_CHANGEDDATE   NULLS FIRST
       ) AS id,
       MAX( cmp ) KEEP (
         DENSE_RANK LAST
         ORDER BY TENANCY_CHANGEDDATE NULLS FIRST,
                  RRH_CHANGEDDATE    NULLS FIRST,
                  ID_OD_CHANGEDDATE   NULLS FIRST,
                  id
       ) AS cmp,
MAX(TRIM(SITE_NAME)) KEEP (
     -- regex_replace( MAX( SITE_NAME ), '\W', '') KEEP (
         DENSE_RANK LAST
         ORDER BY SITE_NAME  NULLS FIRST,
                  id
       ) AS SITE_NAME 
FROM   TBL_IPCOLO_BILLING_MST
GROUP BY sap_id;

Answer 1

It looks like you are trying to use the TRIM function inside the MAX function with the KEEP clause

SELECT MAX(id) KEEP (DENSE_RANK LAST ORDER BY TENANCY_CHANGEDDATE NULLS FIRST, RRH_CHANGEDDATE NULLS FIRST, ID_OD_CHANGEDDATE NULLS FIRST) AS id,
   MAX(cmp) KEEP (DENSE_RANK LAST ORDER BY TENANCY_CHANGEDDATE NULLS FIRST, RRH_CHANGEDDATE NULLS FIRST, ID_OD_CHANGEDDATE NULLS FIRST, id) AS cmp,
   MAX(TRIM(SITE_NAME)) KEEP (DENSE_RANK LAST ORDER BY SITE_NAME NULLS FIRST, id) AS SITE_NAME

FROM TBL_IPCOLO_BILLING_MST GROUP BY sap_id;

Make sure that you close the parenthesis for the MAX function before applying the TRIM function. Also, ensure that the column you are applying the TRIM function to is of a string data type

Answer 2

You can use the trim outside as an outer query

SELECT id,cmp,trim(site_name) as SITE_NAME
 FROM
 (SELECT MAX( id ) KEEP (  -- 102436
     DENSE_RANK LAST
     ORDER BY TENANCY_CHANGEDDATE NULLS FIRST,
              RRH_CHANGEDDATE    NULLS FIRST,
              ID_OD_CHANGEDDATE   NULLS FIRST
   ) AS id,
   MAX( cmp ) KEEP (
     DENSE_RANK LAST
     ORDER BY TENANCY_CHANGEDDATE NULLS FIRST,
              RRH_CHANGEDDATE    NULLS FIRST,
              ID_OD_CHANGEDDATE   NULLS FIRST,
              id
   ) AS cmp,
   MAX((SITE_NAME)) KEEP (
 -- regex_replace( MAX( SITE_NAME ), '\W', '') KEEP (
     DENSE_RANK LAST
     ORDER BY SITE_NAME  NULLS FIRST,
              id
   ) AS SITE_NAME 
   FROM   TBL_IPCOLO_BILLING_MST
   GROUP BY sap_id);