No Duplicates

Database

No Duplicates

Overall Objective is to have all the information on one line can any assist?

SELECT     DISTINCT CA.ACCOUNT_NO AS "ACCOUNT NO"
,CASE COMPANY_NO
WHEN '01' THEN 'SCEG'
WHEN '26' THEN 'PSNC'
END AS COMPANY
,CASE ACCOUNT_TYPE_CODE
WHEN 'C' THEN 'COMMERICAL'
WHEN 'R' THEN 'RESIDENTAL'
END AS TYPE
,CASE SUBSTR(CODES_DATA_PRESENT,22,1) /*STA*/
WHEN 'A' THEN ' Y'
WHEN ' ' THEN ' '
END AS STA
,CASE CASH_ONLY_FL
WHEN 'Y' THEN ' Y'
WHEN ' ' THEN ' '
WHEN 'N' THEN ' '
END AS "CASH ONLY"
,CASE SUBSTR(CODES_DATA_PRESENT,08,1) /*CONTRACT*/
WHEN 'A' THEN 'Y'
ELSE ' '
END AS CONTRACT
,CASE CUSTOMER_TYPE
WHEN 'S' THEN 'Y'
ELSE ' '
END AS "SPOUSE EXISTS"
,CASE
WHEN ADDRESS_ID > 0 THEN 'Y'
END AS "BILL ADDR EXISTS"
,CASE PTP_STATUS_CD
WHEN 'A' THEN 'Y'
END AS "ACTIVE PTP"
,COLL_EXEMPTION_CD AS "EXEMPTIONS"
,PROMISE_BROKEN_NO AS "PROMISE BROKEN"
,PROMISE_KEPT_NO AS "PROMISE KEPT"
,CH.WRITE_OFF_DT AS "WRITE OFF DATE"
,CH.DATE_FORECAST_DNP AS "DNP DATE"
FROM CSRD.CSS_COLLECT_HIST CH
,CSRD.CSS_ACCOUNT CA
,CSRD.CSS_CREDIT_PROFILE CP
,CSRD.CSS_COLL_ACCT_INFO CI
,CSRD.CSSVCUST_STATS CN
,CSRD.CSS_COLL_HIST_DET CD
WHERE CH.ACCOUNT_NO =CA.ACCOUNT_NO
AND CA.ACCOUNT_NO = CP.ACCOUNT_NO
AND CA.ACCOUNT_NO = CI.ACCOUNT_NO
AND CA.CUSTOMER_NO = CN.CUSTOMER_NO
AND CA.ACCOUNT_NO = CD.ACCOUNT_NO
AND FUTURE_WORK_DT > '12/01/2014'
AND CODE_ACCT_STAT IN ('B')
AND CALL_TYPE_CD IN ('A','M')
Tags (1)