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
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
No comments:
Post a Comment