RE: [sqr-users] query

[Bob Stone <bstone@fastenal.com>]

There's a more elegant way to write this without making the limiting select
in the FROM clause, but this should work.  The idea is that you want to
exclude those who have multiple STU_ITM_TYP_NBR.  So you look at the
count(*) (i put STU_ITM_TYP_NBR so you see what i'm doing), and only grab
those 'having count(*) < 2'.  Then you cross that with the
FA_STU_AWD_PROC_VW_GT to print out what you want.

*you might need more things in the where clause depending on what exactly
you're lookig at.  

SELECT distinct 
a.prsn_univ_id, 
a.prsn_prm_full_nm, 
a.stu_acpt_bal_amt, 
a.acad_term_cd, 
a.STU_ITM_TYP_NBR, 
a.inst_cd
FROM dss_rds.fas_stu_awd_proc_vw_gt a, 
  (select prsn_univ_id, count(STU_ITM_TYP_NBR) from
dss_rds.fa_stu_awd_proc_vw_gt group by prsn_univ_id having
count(STU_ITM_TYP_NBR) < 2) b
WHERE a.prsn_univ_id = b.prsn_univ_id


The problem you had with both of them was you were looking at the ID where
there existed a non 90001-3 ITM_TYP_NBR.  So if a student had 90001, 90003,
90004 you'd grab all 3 of his things because his ID had a match on a non
90001-3 id (namely 90004).  


-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org]On Behalf Of
Pan, Zhen
Sent: Wednesday, November 17, 2004 9:14 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] query


Hi 

I have a query that I was unable to write. It is simple, but my brain is
not working now...

For each student, there are one or many item types.

So we are identify if the student has one (say 90004), however, if the
student has any 3 other item types(say 90001,90002,90003), even s/he has
that 90004, we do not need it. 

Here is the query

select distinct

A.PRSN_UNIV_ID, 

A.PRSN_PRM_FULL_NM, 

A.STU_acpt_BAL_AMT ,  

A.acad_term_cd,

A.STU_ITM_TYP_NBR,

a.inst_cd

 FROM   DSS_RDS.FA_STU_AWD_PROC_VW_GT A, DSS_RDS.FA_STU_AWD_PROC_VW_GT B

where A.prsn_univ_id=b.prsn_univ_id and a.STU_ITM_TYP_NBR not
in('90001','90002','90003') 

      and  B.STU_ITM_TYP_NBR='90004' 

 

I also tried

select distinct

A.PRSN_UNIV_ID, 

A.PRSN_PRM_FULL_NM, 

A.STU_acpt_BAL_AMT ,  

A.acad_term_cd,

A.STU_ITM_TYP_NBR,

a.inst_cd

 FROM   DSS_RDS.FA_STU_AWD_PROC_VW_GT A

where A.prsn_univ_id  IN (select PRSN_UNIV_ID from
DSS_RDS.FA_STU_AWD_PROC_VW_GT  where STU_ITM_TYP_NBR not
in('90001','90002','90003')) 

      and  A.STU_ITM_TYP_NBR='90004' 

 

Both failed(they both pick the students wit/without the 3 item type that
I do not want).

Any suggestion is appreciated. Thanks!

 

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users