Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Report Builder 10.1.2.0.2
ORACLE Server Release 10.1.0.4.2
Oracle Procedure Builder 10.1.2.0.2
Oracle ORACLE PL/SQL V10.1.0.4.2 - Production
Oracle CORE 10.1.0.4.0 Production
Oracle Tools Integration Services 10.1.2.0.2
Oracle Tools Common Area 10.1.2.0.2
Oracle Toolkit 2 for Windows 32-bit platforms 10.1.2.0.2
Resource Object Store 10.1.2.0.2
Oracle Help 10.1.2.0.2
Oracle Sqlmgr 10.1.2.0.2
Oracle Query Builder 10.1.2.0.2 - Production
PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
Oracle ZRC 10.1.2.0.2
Oracle XML Developers Kit 10.1.0.4.2 - Production
Oracle Virtual Graphics System 10.1.2.0.2
Oracle Image 10.1.2.0.2
Oracle Multimedia Widget 10.1.2.0.2
Oracle Tools GUI Utilities 10.1.2.0.2
---------------------------------------------------------------------------
| select don.donor_id, last_name, first_name, gender,description |
from donations_don d,
| | donors_don don, |
| | master_codes mc |
where d.donor_id = don.donor_id
| | and mc.code_type = 'ABO' |
| | and mc.udf1 = don.blood_type |
and coll_date = trunc(sysdate-1)
and blood_type in ('84','28')
and procedure_code = 'WB'
and (gender = 'M'
| | or |
| | (gender = 'F' and don.donor_id in (select di.donor_id |
| | from donor_interdictions_don di |
| | where di.donor_id = don.donor_id |
| | and di.inter_code in ('HLAP','HLAN') |
| | and di.term_date is null) )) |
union
| select don.donor_id, last_name, first_name, gender,description |
from donations_don d,
| | donors_don don, |
| | master_codes mc |
where d.donor_id = don.donor_id
| | and mc.code_type = 'ABO' |
| | and mc.udf1 = don.blood_type |
and coll_date = trunc(sysdate-1)
and blood_type in ('84','28')
and procedure_code = 'WB'
and (gender = 'M'
| | or |
| | (gender = 'F' and don.donor_id not in (select di.donor_id |
| | from donor_interdictions_don di |
| | where di.donor_id = don.donor_id |
| | and di.inter_code in ('HLAP','HLAN') |
| | | | | | | | | | | | and di.term_date is null) )) |
If I do not use union then I get only 6 donors. I want all the 17 donors. This is the reason to use union
DONOR_ID,LAST_NAME,FIRST_NAME,GENDER,DESCRIPTION
DN00061453,HARDING,GERALD,M,AB Pos
DN00102630,JOHNSON,KAY,F,AB Pos
DN00312291,HALLENBECK,SUSAN,F,AB Pos
DN00364278,PONSCHKE,MARY,F,AB Pos
DN00406527,BELIN,JULIE,F,AB Neg
DN00537804,HUMPHREY SR,RONALD,M,AB Pos
DN00572245,MWINZI,MUTHIO,F,AB Pos
DN20016905,BARR,EMILY,F,AB Pos
DN20031307,JENSEN,SCOTT,M,AB Pos
DN20141590,SOUTER,ALLISON,F,AB Pos
DN20171054,STOKLOSA,ROBERT,M,AB Pos
DN20249642,HOLL,MARY,F,AB Pos
DN20278720,DE GRAFF,DAWN,F,AB Pos
DN20280710,BORK,THOMAS,M,AB Pos
DN20312826,MARTINEZ,DALIA,F,AB Pos
DN20314980,GREEN,MAXINE,F,AB Pos
DN20320906,BUENO,ALICIA,F,AB Pos
What I need is another column called hla_tested. For Male gender this column will be null since we don not test males. For all females, we need to find if they have been tested. If tested and have a inter_code on HLAP or HLAN then hla_tested column will be Y. If not tested then it will be N. How do I add that additional column to the report?
Thanks in advance.