Home » SQL & PL/SQL » SQL & PL/SQL » Exists question
Exists question [message #248576] Fri, 29 June 2007 10:47 Go to next message
rterry
Messages: 6
Registered: June 2007
Junior Member
I'm new to this so forgive me if I do not follow protocol. I have a sql statement that I am trying to figure out. The statement works but I want it to return if the person is 'FACULTY' or 'STUDENT'.

   select spriden_pidm, 
         spriden_id, 
         spriden_last_name,  
         spriden_first_name,
         spriden_mi,
         gobtpac_external_user, 
         spbpers_birth_date     
  from spriden, spbpers, gobtpac
  WHERE spriden_change_ind is null
    AND spriden_pidm = gobtpac_pidm
    AND spriden_pidm = spbpers_pidm
    AND spriden_entity_ind = 'P'
    AND (exists (select 'x' from pebempl
                 where spriden_pidm = pebempl_pidm
                 AND gobtpac_pidm = pebempl_pidm
                 AND spbpers_pidm = pebempl_pidm
                 AND NVL(pebempl_term_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) 
                 AND NVL(pebempl_loa_beg_date, TRUNC(SYSDATE)-1) < TRUNC(SYSDATE)
                 AND pebempl_ecls_code NOT IN ('SW','SG','SU', 'RE', 'T1', 'T2')
                 AND pebempl_empl_status = 'A')    
         OR
         exists (select 'x' from sgbstdn
                where spriden_pidm = sgbstdn_pidm
                and   sgbstdn_stst_code = 'AS' ))    
    
    order by spriden_last_name, spriden_first_name;


The top exists returns faculty and the bottom one returns students. How do you pass that back to be included on the print line?

Thanks,

Randy
Re: Exists question [message #248587 is a reply to message #248576] Fri, 29 June 2007 11:28 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Try:


select spriden_pidm, 
         spriden_id, 
         spriden_last_name,  
         spriden_first_name,
         spriden_mi,
         gobtpac_external_user, 
         spbpers_birth_date  ,
         'FACULTY' p_type
from spriden, spbpers, gobtpac
WHERE spriden_change_ind is null
  AND spriden_pidm = gobtpac_pidm
  AND spriden_pidm = spbpers_pidm
  AND spriden_entity_ind = 'P'
  AND exists (select 'x' from pebempl
                 where spriden_pidm = pebempl_pidm
                 AND gobtpac_pidm = pebempl_pidm
                 AND spbpers_pidm = pebempl_pidm
                 AND NVL(pebempl_term_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE) 
                 AND NVL(pebempl_loa_beg_date, TRUNC(SYSDATE)-1) < TRUNC(SYSDATE)
                 AND pebempl_ecls_code NOT IN ('SW','SG','SU', 'RE', 'T1', 'T2')
                 AND pebempl_empl_status = 'A')    
UNION 
select spriden_pidm, 
         spriden_id, 
         spriden_last_name,  
         spriden_first_name,
         spriden_mi,
         gobtpac_external_user, 
         spbpers_birth_date       ,
         'STUDENT' p_type
from spriden, spbpers, gobtpac
WHERE spriden_change_ind is null
  AND spriden_pidm = gobtpac_pidm
  AND spriden_pidm = spbpers_pidm
  AND spriden_entity_ind = 'P'
  AND (exists  (select 'x' from sgbstdn
                         where spriden_pidm = sgbstdn_pidm
                         and   sgbstdn_stst_code = 'AS' )
order by 3,4;
Re: Exists question [message #248589 is a reply to message #248587] Fri, 29 June 2007 11:47 Go to previous message
rterry
Messages: 6
Registered: June 2007
Junior Member
B-I-N-G-O!!! Thank you Mmichael Bialik for your help. We are trying to go live with Banner this fall term and we are swamped. Thank you again.

Randy Terry
Previous Topic: Error PLS-00428: Problem creating Procedure
Next Topic: Index Usage
Goto Forum:
  


Current Time: Tue Dec 06 04:35:37 CST 2016

Total time taken to generate the page: 0.16619 seconds