Decode or NVL, I am trying to avoid duplicates

Question

I am trying to avoid duplicate records. I want sql to pull anyone who has a ('AU','RE', 'RW') record but I only need one to show in my results. Can you use Decode or Nvl for something like this?

select distinct r1.sfrstcr_rsts_code, spriden_last_name, spriden_first_name, s1.saradap_appl_date, s1.saradap_appl_no

from saradap s1, sfrstcr r1, spriden

where spriden_pidm = sfrstcr_pidm

and spriden_pidm = saradap_pidm

and s1.saradap_appl_no = (select max( s2.saradap_appl_no)

                  from saradap s2

                  where s2.saradap_pidm = s1.saradap_pidm)

and sfrstcr_pidm (+) = saradap_pidm

and saradap_term_code_entry = sfrstcr_term_code

and r1.sfrstcr_rsts_code in ('AU','RE', 'RW')

and r1.sfrstcr_reg_seq = (select max (r2.sfrstcr_reg_seq)

              from sfrstcr r2

            where r2.sfrstcr_reg_seq = r1.sfrstcr_reg_seq)

AND saradap_term_code_entry = 202210

--and stvmajr_code = saradap_term_code_entry

order by spriden_last_name

Answer 1

---------------------------------------------------------------------------------------
--This may be a place to start.
--Note: This query returns all students who meet the following criteria:
--1. At least one record in SARADAP where SARADAP_TERM_ENTRY = SFRSTCR_TERM_CODE 
--2. At least one record in SPRIDEN where SPRIDEN_CHANGE_IND is null
--3. At least one record in SFRSTCR where RSTS_CODE in AU,RE,RW
--It doesn't return all the distinct RSTS_CODE in SFRSTCR.
--It does check that a record is in SFRSTCR for TERM=202210 and reports the RSTS_CODE for the MAX(CRN)
--To get an understanding of how this query works, I suggest running the INNER query first.
---------------------------------------------------------------------------------------
select sfrstcr_rsts_code,
       spriden_last_name,
       spriden_first_name,
       saradap_appl_date,
       saradap_appl_no
       from (
----------------------------------------------------------------------------       
--INNER QUERY: Returns all related SARADAP records and sorts by APPL_NO
--             Returns all related SFRSTCR records and sorts by CRN
----------------------------------------------------------------------------
select sfrstcr_pidm, --Useful for spot checking 
       sfrstcr_rsts_code, 
       spriden_last_name, 
       spriden_first_name, 
       saradap_appl_date, 
       saradap_appl_no,
       --SARADAP has multiple rows. You specified max(appl_no) as record of choice Use RANK() which ranks record by appl_no
       rank() over (partition by saradap_pidm order by saradap_appl_no desc) as max_saradap_record,
       --SFRSTCR has multiple rows. The WHERE clause filters by AU,RE,RW. Rank here sorted by CRN arbitrarly ranks
       --Since you just want students who have at least one registration record in SFRSTCR you can sort by CRN and get max crn.
       --However it will report the RS code just for this SFRSTCR record.
       rank() over (partition by sfrstcr_pidm order by sfrstcr_crn desc) as max_crn
from sfrstcr inner join saradap on sfrstcr_pidm =  saradap_pidm and
                                   sfrstcr_term_code = saradap_term_code_entry
--SPRIDEN has many rows. Use WHERE CHANGE_IND = null as it is the record of choice for SPRIDEN             
             inner join spriden on sfrstcr_pidm = spriden_pidm 
where sfrstcr_term_code = '202210'
and   spriden_change_ind is null
and   sfrstcr_rsts_code in ('AU','RE','RW')
-------END INNER QUERY
)
where max_saradap_record=1 and
      max_crn=1;