I have to create a custom pre-defined filter in cognos Framework. Filters follows the below logic.
AND I_CLM_SAN NOT IN
(
SELECT I_CLM_SAN FROM (
SELECT COUNT(*) AS A1,I_CLM_SAN FROM
(SELECT DISTINCT C_CLM_STAT,I_CLM_SAN FROM DLROB.SVC_CLM)
WHERE C_CLM_STAT = 'R' OR C_CLM_STAT = 'X' GROUP BY I_CLM_SAN) WHERE A1 > 1
)
Basically, in database Claim San (I_CLM_SAN) is been updating two times for Claim status (C_CLM_STAT ) X and R flags, for that we have to exclude those san which are repeated. i wont able to make this sql in framework filter.
Please address, if someone has better solution on this..