I tried to execute a select query which I select from the different tables but I still getting duplicate record even I use “DISTINCT”. Below I attached the result from my query.
x
SELECT DISTINCT CSC.POLICYNO
,PSA.WORKFLOWID
,CSC.POLICYHOLDERNAME
,CSC.TASKTYPE
,CSC.PRODUCTCODE
,WF.STATUS
,(SELECT concat(USR.SFIRSTNAME, USR.SLASTNAME) where USR.lseqid = (select psa.userid where psa.actiontype IN(602))) AS DE_Name
,PSA.CREATEDATE AS DE_Start_date
,(SELECT UPDATEDATE FROM C_WF_PS_AUDITTRAIL WHERE WORKFLOWID = PSA.WORKFLOWID AND ACTIONTYPE IN (603) ) as DE_End_date
FROM C_WF_PS_AUDITTRAIL PSA
INNER JOIN MUSER USR ON USR.LSEQID = PSA.USERID
INNER JOIN C_WF_PS_WORKFLOW WF ON WF.WORKFLOWID = PSA.WORKFLOWID
INNER JOIN C_CSC_PS_CACHE CSC ON CSC.POLICYNO = WF.POLICYNO
WHERE WF.POLICYNO = CSC.POLICYNO
AND MONTH (PSA.CREATEDATE) = @month AND YEAR(PSA.CREATEDATE) = @year
Advertisement
Answer
DE_Name
is different between 2 rows. So it’s correct.
To resolve your problem, just do it
WHERE WF.POLICYNO = CSC.POLICYNO and DE_name is not null
However, the root causes problem is you just need to select some columns that you make sure that it is unique and make sense.