Oracle Select Destinct Dups?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • dowlingm815
    New Member
    • Feb 2010
    • 133

    Oracle Select Destinct Dups?

    the following code keeps generating duplicate row as:

    POHD_CODE FY ACTIVITY_DATE USER_ID SPRIDEN_ID LAST_NAME ITEM COMM_DESC AMT COAS_CODE
    P0469513 10 16-Nov-09 OBLISKD H00446436 Integra Radionics 1 n/a-Extended Warranty on RT Workstation 36285 1
    P0469513 10 16-Nov-09 OBLISKD H00446436 Integra Radionics 1 n/a-Extended Warranty on RT Workstation 36285 1


    Any insight would be appreciate?

    Code:
    SELECT DISTINCT FIMSMGR.FPRPODA.FPRPODA_POHD_CODE as POHD_CODE,
      FIMSMGR.FPRPODA.FPRPODA_FSYR_CODE as FY,
      FIMSMGR.FPRPODA.FPRPODA_ACTIVITY_DATE AS ACTIVITY_DATE,
      FIRST_VALUE(FIMSMGR.FPBPOHD.FPBPOHD_USER_ID) 
       OVER (ORDER BY FIMSMGR.FPRPODA.FPRPODA_POHD_CODE) as UserId,
      SATURN.SPRIDEN.SPRIDEN_ID as SPRIDEN_ID,
      SATURN.SPRIDEN.SPRIDEN_LAST_NAME AS LAST_NAME,
      FIMSMGR.FPRPODA.FPRPODA_ITEM AS ITEM,
      FIMSMGR.FPRPODT.FPRPODT_COMM_DESC AS COMM_DESC,
      FIMSMGR.FPRPODA.FPRPODA_AMT AS AMT,
      FIMSMGR.FPBPOHD.FPBPOHD_COAS_CODE AS COAS_CODE
      
    FROM FIMSMGR.FPRPODT
    INNER JOIN FIMSMGR.FPRPODA
    ON FIMSMGR.FPRPODT.FPRPODT_POHD_CODE = FIMSMGR.FPRPODA.FPRPODA_POHD_CODE
    AND FIMSMGR.FPRPODT.FPRPODT_ITEM     = FIMSMGR.FPRPODA.FPRPODA_ITEM
    INNER JOIN FIMSMGR.FPBPOHD
    ON FIMSMGR.FPRPODA.FPRPODA_POHD_CODE = FIMSMGR.FPBPOHD.FPBPOHD_CODE
    INNER JOIN SATURN.SPRIDEN
    ON SATURN.SPRIDEN.SPRIDEN_PIDM = FIMSMGR.FPBPOHD.FPBPOHD_VEND_PIDM
    
    WHERE FIMSMGR.FPRPODA.FPRPODA_POHD_CODE = 'P0469513' 
    AND (FIMSMGR.FPRPODT.FPRPODT_COMM_DESC LIKE '%software%'
    AND FIMSMGR.FPRPODT.FPRPODT_CANCEL_IND     IS NULL
    AND FIMSMGR.FPRPODA.FPRPODA_CHANGE_SEQ_NUM IS NULL
    AND SATURN.SPRIDEN.SPRIDEN_CHANGE_IND      IS NULL)
    
    OR FIMSMGR.FPRPODA.FPRPODA_POHD_CODE = 'P0469513' 
    AND (FIMSMGR.FPRPODT.FPRPODT_COMM_DESC LIKE '%Software%'
    AND FIMSMGR.FPRPODT.FPRPODT_CANCEL_IND     IS NULL
    AND FIMSMGR.FPRPODA.FPRPODA_CHANGE_SEQ_NUM IS NULL
    AND SATURN.SPRIDEN.SPRIDEN_CHANGE_IND      IS NULL)
    
    OR FIMSMGR.FPRPODA.FPRPODA_POHD_CODE = 'P0469513' 
    AND (FIMSMGR.FPRPODT.FPRPODT_CANCEL_IND     IS NULL
    AND FIMSMGR.FPRPODA.FPRPODA_CHANGE_SEQ_NUM IS NULL
    AND SATURN.SPRIDEN.SPRIDEN_CHANGE_IND      IS NULL
    AND UPPER(FIMSMGR.FPRPODT.FPRPODT_COMM_DESC) LIKE '%SOFTWARE%');
Working...