Showing posts with label case when. Show all posts
Showing posts with label case when. Show all posts

Tuesday, September 4, 2018

Oracle case when based on numeric check for varchar2 column and select based on range of integers

SELECT
 case
        when SUBSTR( CAST(PGEO.GEO_VAL AS NUMBER) , 1,3) <=199 then  'شرق المدينة'
        when  SUBSTR( CAST(PGEO.GEO_VAL AS NUMBER), 1,3) <=299 THEN   'شمال المدينة'
        when  SUBSTR( CAST(PGEO.GEO_VAL AS NUMBER), 1,3) <=399 then   'غرب المدينة'
        when  SUBSTR( CAST(PGEO.GEO_VAL AS NUMBER), 1,3) <= 499 then  'جنوب المدينة'
        when  SUBSTR( CAST(PGEO.GEO_VAL AS NUMBER), 1,3) <= 599 then 'وسط المدينة'
        else 'other'
     end AS GEO_VAL  ,COUNT(SA.SA_ID) AS ACCT_CNT
 FROM CISADM.CI_SA SA
INNER JOIN  CISADM.CI_PREM PREM ON PREM.PREM_ID = SA.CHAR_PREM_ID
INNER  JOIN CISADM.CI_PREM_GEO PGEO ON PGEO.PREM_ID = PREM.PREM_ID AND PGEO.GEO_TYPE_CD ='9405'--'9401'
 WHERE SA.CIS_DIVISION ='4501'
 AND SA.SA_STATUS_FLG  IN ('20')
 AND SA.SA_TYPE_CD IN ('184015','184012','184013')
 group by case
        when  SUBSTR( CAST(GEO_VAL AS NUMBER), 1,3) <=199 then  'شرق المدينة'
        when  SUBSTR( CAST(GEO_VAL AS NUMBER), 1,3)<= 299 then 'شمال المدينة'
        when  SUBSTR( CAST(GEO_VAL AS NUMBER), 1,3)<= 399 then   'غرب المدينة'
        when  SUBSTR( CAST(GEO_VAL AS NUMBER), 1,3) <= 499 then 'جنوب المدينة'
        when  SUBSTR( CAST(GEO_VAL AS NUMBER), 1,3) <= 599 then 'وسط المدينة'
        else 'other'
        end
        order by geo_val asc;
-------------------------------------------
result