[Solved] from keyword not found where expected in case statement

by


Looks like there is comma missing.

Also you need to use the entire case statement in group by not the column name alone

SELECT
To_date(To_char(BATCH_CREATION_TIME,'YYYY/MM/DD'),'YYYY/MM/DD') AS CREATION_DATE,
Sum(Decode(CDR_ACTUAL_COUNT,NULL,0,CDR_ACTUAL_COUNT)) AS CCOLLECTED,
Sum(Decode(CDR_ACTUAL_COUNT,NULL,0,CDR_ACTUAL_COUNT)) - Sum(Decode(CDR_PARSE_ERROR_COUNT,NULL,0,CDR_PARSE_ERROR_COUNT))
- Sum(Decode(CDR_DISCARD_COUNT,NULL,0,CDR_DISCARD_COUNT)) AS COLLECTED,
Sum(Decode(VALIDATION_CNR_COUNT,NULL,0,VALIDATION_CNR_COUNT)) + Sum(Decode(VALIDATION_CE_COUNT,NULL,0,VALIDATION_CE_COUNT))
+ Sum(Decode(VALIDATION_CR_COUNT,NULL,0,VALIDATION_CR_COUNT)) + Sum(Decode(VALIDATION_NCR_COUNT,NULL,0,VALIDATION_NCR_COUNT))  AS ERRORED,
Sum(Decode(VALIDATION_RNC_COUNT,NULL,0,VALIDATION_RNC_COUNT)) + Sum(Decode(VALIDATION_RV_COUNT,NULL,0,VALIDATION_RV_COUNT)) AS PROCESSED ,

CASE
WHEN CDR_SOURCE_TYPE IN ('SMSC') THEN Decode(NETWORK_TYPE,'CDMA','SMSC_CDMA','HSPA','SMSC_HSPA')
WHEN CDR_SOURCE_TYPE IN ('P_GW') AND BATCH_ID LIKE '%_PGW_%' AND BATCH_ID LIKE '%_PGW_REPLAY_%' THEN 'PGW_REPLAY'
WHEN CDR_SOURCE_TYPE IN ('OCS_UDR','OCS_EDR','OCS_CDMA_EDR','OCS_UDR_RECOVER','MACH_UDR','AAA','PTT_CDMA','OCS_CDMA_UDR','MTX') THEN CDR_SOURCE_TYPE
--ELSE 'Excelent'
END AS CDR_SOURCE_TYPE
FROM  BATCH_SUMMARY

WHERE
BATCH_CREATION_TIME BETWEEN To_date(To_char(sysdate,'YYYY-MM-DD') ||' 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND To_date(To_char(sysdate,'YYYY-MM-DD') ||' 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
AND BATCH_CREATION_TIME < (SYSDATE -1/24)
--and CDR_SOURCE_TYPE IN ('OCS_UDR','OCS_EDR','OCS_CDMA_EDR','OCS_UDR_RECOVER','MACH_UDR','AAA','PTT_CDMA','OCS_CDMA_UDR','MTX')

GROUP BY
TO_DATE(TO_CHAR(BATCH_CREATION_TIME,'YYYY/MM/DD'),'YYYY/MM/DD'), 
CASE
WHEN CDR_SOURCE_TYPE IN ('SMSC') THEN Decode(NETWORK_TYPE,'CDMA','SMSC_CDMA','HSPA','SMSC_HSPA') AS "CDR_SOURCE_TYPE"
WHEN CDR_SOURCE_TYPE IN ('P_GW') AND BATCH_ID LIKE '%_PGW_%' AND BATCH_ID LIKE '%_PGW_REPLAY_%' THEN 'PGW_REPLAY' AS "CDR_SOURCE_TYPE"
WHEN CDR_SOURCE_TYPE IN ('OCS_UDR','OCS_EDR','OCS_CDMA_EDR','OCS_UDR_RECOVER','MACH_UDR','AAA','PTT_CDMA','OCS_CDMA_UDR','MTX') THEN "CDR_SOURCE_TYPE"
--ELSE 'Excelent'
END

Also make sure Aliasname=column_name format is supported in your database. In Sql server it will work. Else use

Column_name as Aliasname

Update:

SELECT CASE -- CDR_SOURCE_TYPE removed
         WHEN CDR_SOURCE_TYPE IN ( 'SMSC' ) THEN Decode(NETWORK_TYPE, 'CDMA', 'SMSC_CDMA',
                                                                      'HSPA', 'SMSC_HSPA')
         WHEN CDR_SOURCE_TYPE IN ( 'P_GW' )
              AND BATCH_ID LIKE '%_PGW_%'
              AND BATCH_ID LIKE '%_PGW_REPLAY_%' THEN 'PGW_REPLAY'
       END
FROM   BATCH_SUMMARY

9

solved from keyword not found where expected in case statement