• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 335
  • Last Modified:

oracle query

select sch_date,sch_time,hds.donor_id,gender,
           to_char(to_date(sch_time,'HH24:MI:SS'),'HH:MI:SS AM') as convert_date,
           site_id,hds.first_name,hds.last_name,mc.description
      from donors_don d,hbc_donor_schedule hds,master_codes mc
     where SCH_DATE = trunc(sysdate)
       AND hds.STATUS = 'CNFRM'
       AND PROC_CODE = 'WB'
       AND DRIVE_ID IS NULL
       and donation_type = 'AL'
       and mc.code_type = 'ABO'
       and mc.udf1 = to_char(hds.blood_type)
         and d.donor_id = hds.donor_id
       and hds.donor_id in (select donor_id
                              from ht_bc_don_elig_vw hbdev
                                         where hds.donor_id = hbdev.donor_id
                               and (PLATELET_DATE is null
                                              or PLATELET_DATE < trunc(sysdate)
                                or  PLASMA_DATE is null
                                                or PLASMA_DATE < trunc(sysdate)))                                                                                     
order by to_date(sch_time,'HH24:MI:SS');       



SCH_DATE,SCH_TIME,DONOR_ID,GENDER,CONVERT_DATE,SITE_ID,FIRST_NAME,LAST_NAME,DESCRIPTION

1/15/2014,15:40:00,DN20213912,F,03:40:00 PM,LOCN100024,SHARON,WEEKS,A Pos
1/15/2014,14:00:00,DN20116283,F,02:00:00 PM,LOCN101661,PATRICIA,SANCHEZ DE CASTILLO,O Pos
1/15/2014,17:00:00,DN20276561,F,05:00:00 PM,LOCN201102,ERIKA,PASINDO,O Pos
1/15/2014,18:40:00,DN00472401,F,06:40:00 PM,LOCN100024,NANCY,WROBEL,O Pos
1/15/2014,17:00:00,DN00555256,F,05:00:00 PM,LOCN100373,BRENDA,MCDONALD,O Neg
1/15/2014,17:20:00,DN20312819,F,05:20:00 PM,LOCN200034,SUSANA,VALLE,O Pos
1/15/2014,15:40:00,DN20243221,F,03:40:00 PM,LOCN100326,NICOLE,DOUGHTY,O Pos
1/15/2014,15:40:00,DN20288268,F,03:40:00 PM,LOCN100373,CIENNA,RIEMERSMA,O Neg
1/15/2014,14:00:00,DN20082235,F,02:00:00 PM,LOCN200492,LISA,LEONI,A Pos
1/15/2014,17:00:00,DN00069210,F,05:00:00 PM,LOCN100008,NANCYE,WERTZ,O Neg
1/15/2014,14:20:00,DN20286906,F,02:20:00 PM,LOCN100009,KELLY,URBAN,O Neg
1/15/2014,13:00:00,DN00011704,F,01:00:00 PM,LOCN100319,DARLENE,FAGAN,A Pos
1/15/2014,18:40:00,DN20285187,F,06:40:00 PM,LOCN200034,LINDSEY,BECKWITH,O Neg
1/15/2014,15:20:00,DN20250349,F,03:20:00 PM,LOCN201102,KRISTINE,TOKARCZYK,A Pos
1/15/2014,16:00:00,DN20072302,F,04:00:00 PM,LOCN201860,PENNY,MOSLEY,O Pos
1/15/2014,16:40:00,DN00591807,F,04:40:00 PM,LOCN201489,MEGAN,BENHART,A Pos
1/15/2014,15:20:00,DN00481761,F,03:20:00 PM,LOCN100373,VIRGINIA,LUKASIEWICZ,A Pos
1/15/2014,14:20:00,DN00099659,F,02:20:00 PM,LOCN100024,GWENDOLYN,JOHNSON,A Neg
1/15/2014,16:00:00,DN20202542,F,04:00:00 PM,LOCN100326,KELLY,CUNDIFF,A Pos
1/15/2014,17:40:00,DN20090737,F,05:40:00 PM,LOCN100373,JOY,NYHANNA,B Neg
1/15/2014,13:40:00,DN00171528,F,01:40:00 PM,LOCN101661,SHARON,WOODMAN,A Pos
1/15/2014,15:20:00,DN00349221,F,03:20:00 PM,LOCN100319,KAREN,JORGENSEN,O Pos
1/15/2014,12:40:00,DN00469765,F,12:40:00 PM,LOCN100008,JULIE,BROMANN,O Pos
1/15/2014,16:40:00,DN00496118,F,04:40:00 PM,LOCN201860,TAMMY,MICHAEL,O Neg
1/15/2014,13:40:00,DN20273825,F,01:40:00 PM,LOCN201102,KRYSTLE,JULIAN,O Pos
1/15/2014,16:00:00,DN20304349,F,04:00:00 PM,LOCN101661,SHANNON,BREMNER,O Pos
1/15/2014,18:40:00,DN20118852,F,06:40:00 PM,LOCN100024,CHERYL,SCHILKE,O Neg
1/15/2014,13:20:00,DN20046739,F,01:20:00 PM,LOCN201489,ELIZABETH,PLYWACZEWSKI,B Pos
1/15/2014,18:40:00,DN20254741,F,06:40:00 PM,LOCN201489,COURTNEY,ZIMMERMAN,B Pos
1/15/2014,11:40:00,DN00178913,F,11:40:00 AM,LOCN100014,MICHELE,FLOOD,O Pos
1/15/2014,11:40:00,DN20190868,F,11:40:00 AM,LOCN201102,WENDI,LAPIDUS,O Pos
1/15/2014,11:40:00,DN00198414,F,11:40:00 AM,LOCN100014,LINDA,TIMMONS,O Pos
1/15/2014,16:00:00,DN20294810,F,04:00:00 PM,LOCN200492,KAYLA,FERGUSON,O Pos
1/15/2014,13:00:00,DN20289679,F,01:00:00 PM,LOCN100326,ERIKA,SOPKO,A Pos
1/15/2014,11:40:00,DN20317756,F,11:40:00 AM,LOCN100326,ANASTASIA,SALES,O Pos
1/15/2014,17:40:00,DN20199188,F,05:40:00 PM,LOCN201102,NATALIE,PADILLA,A Pos
1/15/2014,15:20:00,DN20127093,F,03:20:00 PM,LOCN101661,KATHLEEN,WILSEK,O Neg
1/15/2014,18:20:00,DN00418672,F,06:20:00 PM,LOCN100014,JILL,YUCUIS,A Pos
1/15/2014,16:00:00,DN20064579,F,04:00:00 PM,LOCN100014,GRISSELLA,MARTINEZ,B Pos
1/15/2014,14:20:00,DN20305280,F,02:20:00 PM,LOCN101661,LAURIE,ALLORI,AB Pos
1/15/2014,15:40:00,DN00511338,F,03:40:00 PM,LOCN201489,VALORIE,PROCE,A Pos
1/15/2014,12:20:00,DN20295063,F,12:20:00 PM,LOCN100024,NANCY,HOLLAND,O Neg
1/15/2014,14:20:00,DN20301255,F,02:20:00 PM,LOCN100326,LORENA,SANTOS,O Neg
1/15/2014,14:20:00,DN20087312,F,02:20:00 PM,LOCN201489,KATHERINE,HALL,A Neg
1/15/2014,18:00:00,DN20001847,F,06:00:00 PM,LOCN100008,FRANCES,MILLER,AB Pos
1/15/2014,12:20:00,DN00254490,F,12:20:00 PM,LOCN201102,PAMELA,PUCEL,A Pos
1/15/2014,16:40:00,DN20317157,F,04:40:00 PM,LOCN201489,SUSAN,CARLTON,A Pos
1/15/2014,12:00:00,DN20238623,F,12:00:00 PM,LOCN100326,KANESHA,JONES,O Pos
1/15/2014,12:20:00,DN20216154,F,12:20:00 PM,LOCN201102,AMY,BARMA,O Pos
1/15/2014,18:00:00,DN20318026,F,06:00:00 PM,LOCN100008,CLAUDIA,HERRERA,A Pos
1/15/2014,13:00:00,DN00301351,F,01:00:00 PM,LOCN101661,NANCY,MILLER,A Pos
1/15/2014,15:40:00,DN20052010,F,03:40:00 PM,LOCN201102,KATLEY,KULAGA,B Pos
1/15/2014,15:40:00,DN00025239,F,03:40:00 PM,LOCN101661,SHARON,CHAKOIAN,O Pos
1/15/2014,16:40:00,DN20248541,F,04:40:00 PM,LOCN100009,MALINA,MARTIN,B Pos
1/15/2014,17:40:00,DN00600812,F,05:40:00 PM,LOCN200492,ZOE,SLAGSTAD,A Neg
1/15/2014,13:00:00,DN00111682,F,01:00:00 PM,LOCN201860,SHERILYN,SORENSEN,B Pos
1/15/2014,16:20:00,DN20271984,F,04:20:00 PM,LOCN100373,TALIYAH,HARDY,O Pos
1/15/2014,13:40:00,DN20242833,F,01:40:00 PM,LOCN100326,LESLIE,WRIGHT,B Pos
1/15/2014,12:40:00,DN00549767,F,12:40:00 PM,LOCN201102,DENISE,PANKEY,A Pos
1/15/2014,18:00:00,DN00415002,F,06:00:00 PM,LOCN100326,BARBARA,HARRIS,A Pos
1/15/2014,13:00:00,DN20287955,F,01:00:00 PM,LOCN201102,TEENA,KREITZER,B Pos
1/15/2014,17:00:00,DN00277667,F,05:00:00 PM,LOCN201489,JANET,SPRIET,O Pos
1/15/2014,17:00:00,DN20239352,F,05:00:00 PM,LOCN202068,MICHELLE,ZEGLIN,A Pos
1/15/2014,13:20:00,DN00178031,F,01:20:00 PM,LOCN201102,LISA,IURO,A Neg
1/15/2014,14:20:00,DN20178569,F,02:20:00 PM,LOCN202068,FANNY,DIAZ,A Pos
1/15/2014,12:00:00,DN20188131,F,12:00:00 PM,LOCN101661,CAROL,SCHMIDT,A Pos
1/15/2014,18:00:00,DN00085433,F,06:00:00 PM,LOCN201489,DIANE,NOWICKI,O Pos
1/15/2014,18:40:00,DN00313075,F,06:40:00 PM,LOCN100008,CHERYL,STASCH,O Pos
1/15/2014,12:40:00,DN00551258,F,12:40:00 PM,LOCN100008,KRISTIN,NETTNIN,A Neg
1/15/2014,12:40:00,DN00524018,F,12:40:00 PM,LOCN201102,MARY ELLEN,SLABY,O Pos
1/15/2014,13:00:00,DN00546976,F,01:00:00 PM,LOCN100024,CORINNE,BOYD,O Neg
1/15/2014,16:00:00,DN20154912,F,04:00:00 PM,LOCN100024,CONNIE,TUCKER,A Pos
1/15/2014,12:00:00,DN20204366,F,12:00:00 PM,LOCN201102,KATHLEEN,KRISCH,O Pos
1/15/2014,15:40:00,DN20090329,F,03:40:00 PM,LOCN100319,ROSEMARY,TOON,A Pos
1/15/2014,12:40:00,DN20064407,F,12:40:00 PM,LOCN101661,SHAWNEE,DAMITZ,A Neg
1/15/2014,17:00:00,DN00189878,F,05:00:00 PM,LOCN200492,KATHLEEN,ROMANO,O Pos
1/15/2014,11:40:00,DN20207487,F,11:40:00 AM,LOCN100014,MARY,BECKER,A Pos
1/15/2014,14:20:00,DN20246617,F,02:20:00 PM,LOCN201489,LAURA,HASELTINE,A Pos
1/15/2014,14:00:00,DN20246376,F,02:00:00 PM,LOCN100373,SAMANTHA,KENNY,O Neg
1/15/2014,15:20:00,DN20062890,M,03:20:00 PM,LOCN100008,CHRISTOPHER,EVANS,O Pos
1/15/2014,17:40:00,DN20300428,M,05:40:00 PM,LOCN100009,JONATHAN,LINO,B Pos
1/15/2014,17:00:00,DN20254855,M,05:00:00 PM,LOCN100014,EDMUND,YOUHANAIE,A Pos
1/15/2014,11:40:00,DN00392146,M,11:40:00 AM,LOCN101661,WILLIAM,SHEA,O Neg
1/15/2014,11:40:00,DN00422383,M,11:40:00 AM,LOCN101661,RICKEY,KETHCART,O Pos
1/15/2014,11:40:00,DN20081778,M,11:40:00 AM,LOCN100373,CHRISTOPHER,GREEN,A Pos
1/15/2014,16:00:00,DN20080498,M,04:00:00 PM,LOCN100373,ANTHONY,CURTIS,A Pos
1/15/2014,15:40:00,DN00135583,M,03:40:00 PM,LOCN101661,STEVEN,KING,A Pos
1/15/2014,17:20:00,DN00243831,M,05:20:00 PM,LOCN100009,JOHN,GISSY,A Pos
1/15/2014,13:00:00,DN20068900,M,01:00:00 PM,LOCN100319,MICHAEL,BROCKMAN,A Pos
1/15/2014,14:00:00,DN20314370,M,02:00:00 PM,LOCN200492,CHRISTIAN,CONLON,B Pos
1/15/2014,12:00:00,DN00592416,M,12:00:00 PM,LOCN202068,JEFFREY,WEIGT,O Pos
1/15/2014,17:40:00,DN00198110,M,05:40:00 PM,LOCN201489,JAMES,BATJES,AB Pos
1/15/2014,15:20:00,DN20277809,M,03:20:00 PM,LOCN201489,STEVE,WANG,AB Pos
1/15/2014,15:20:00,DN20288921,M,03:20:00 PM,LOCN100373,DAVION,CALVERT,O Pos
1/15/2014,17:40:00,DN20294519,M,05:40:00 PM,LOCN100373,RONALD,HAUHE JR,O Pos
1/15/2014,12:20:00,DN20253883,M,12:20:00 PM,LOCN100319,PATRICK,ASHER,O Pos
1/15/2014,17:00:00,DN20133003,M,05:00:00 PM,LOCN202068,BRIAN,ZEGLIN,O Pos
1/15/2014,13:00:00,DN00597960,M,01:00:00 PM,LOCN201102,NICK,IURO,B Neg
1/15/2014,13:00:00,DN20077482,M,01:00:00 PM,LOCN100009,MICHAEL,CIENIUCH,A Pos
1/15/2014,15:20:00,DN00588226,M,03:20:00 PM,LOCN100014,LORENZO,RUIZ,B Pos
1/15/2014,17:00:00,DN20179526,M,05:00:00 PM,LOCN201489,DANIEL,KOZUCHOWSKI,O Neg
1/15/2014,14:00:00,DN20106141,M,02:00:00 PM,LOCN100014,MATTHEW,WEBER,A Pos
1/15/2014,13:00:00,DN20286494,M,01:00:00 PM,LOCN201489,STEPHEN,HANSEL,A Pos
1/15/2014,18:40:00,DN00112885,M,06:40:00 PM,LOCN201102,BILL,GRIESENAUER,B Pos
1/15/2014,13:00:00,DN00584750,M,01:00:00 PM,LOCN100008,JOSEPH,MOELLER,O Pos
1/15/2014,13:40:00,DN00169734,M,01:40:00 PM,LOCN100319,JOHN,QUINN,O Pos
1/15/2014,12:20:00,DN20225262,M,12:20:00 PM,LOCN200492,ROBERT,RICHARDSON,O Pos
1/15/2014,12:00:00,DN00292318,M,12:00:00 PM,LOCN100319,JEFFREY,GOODSPEED,O Neg
1/15/2014,16:40:00,DN20098694,M,04:40:00 PM,LOCN100326,KEVIN,ROBERT,AB Neg
1/15/2014,15:20:00,DN00251709,M,03:20:00 PM,LOCN200492,DAVID,OLSEN,O Pos
1/15/2014,18:20:00,DN20268146,M,06:20:00 PM,LOCN201102,JESSE,PLASENCIA,A Pos
1/15/2014,16:40:00,DN20316924,M,04:40:00 PM,LOCN100009,CHRISTIAN,CYRUS,O Neg
1/15/2014,15:40:00,DN20025966,M,03:40:00 PM,LOCN201489,JAMES,PROCE,O Pos
1/15/2014,16:40:00,DN00578005,M,04:40:00 PM,LOCN100326,JAMES,LANGSTON,O Neg
1/15/2014,16:40:00,DN20300798,M,04:40:00 PM,LOCN201102,JAMES,LORRAINE,O Neg
1/15/2014,16:00:00,DN20223225,M,04:00:00 PM,LOCN201489,KEVIN,COPPERT,A Neg
1/15/2014,12:00:00,DN00586609,M,12:00:00 PM,LOCN201102,RUBEN,VALADEZ,O Pos
1/15/2014,15:20:00,DN00348895,M,03:20:00 PM,LOCN100319,MICHAEL,MILLER,O Pos
1/15/2014,16:20:00,DN00003624,M,04:20:00 PM,LOCN201860,DAVID,HAGLUND,O Neg
1/15/2014,14:20:00,DN20048757,M,02:20:00 PM,LOCN100326,STEVEN,HUYCK,A Pos
1/15/2014,13:40:00,DN20124140,M,01:40:00 PM,LOCN100326,ROBERT,ADLARD,A Pos
1/15/2014,18:40:00,DN00591639,M,06:40:00 PM,LOCN200034,JOSEPH,KOVAC,A Pos
1/15/2014,18:40:00,DN20036976,M,06:40:00 PM,LOCN100319,JOSE,BECERRA,O Pos
1/15/2014,13:40:00,DN20085813,M,01:40:00 PM,LOCN201860,ANTHONY,HOULE,O Pos
1/15/2014,13:00:00,DN20014962,M,01:00:00 PM,LOCN100024,HENRY,CZYZEWSKI,O Pos
1/15/2014,12:00:00,DN00469212,M,12:00:00 PM,LOCN100014,JEFFREY,ADAM,A Neg
1/15/2014,17:20:00,DN00462162,M,05:20:00 PM,LOCN102725,PAUL,HETZLER,O Neg


From the 127 donors if the gender is 'F' then have to go to the below query. This is the testing done to find if females are eligible. So how do I add that query?
Only if F go to donor_interdictions table. For male do not have to.

       and hds.donor_id in (select di.donor_id
                              from donor_interdictions_don di
                                       where di.donor_id = hds.donor_id
                                           and di.inter_code = 'HLAN'
                                           and di.term_date is null)
0
anumoses
Asked:
anumoses
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
sdstuberCommented:
and (gender = 'M'
or
(gender = 'F' and hds.donor_id in (select di.donor_id
                              from donor_interdictions_don di
                                       where di.donor_id = hds.donor_id
                                           and di.inter_code = 'HLAN'
                                           and di.term_date is null) )
)
0
 
awking00Commented:
select <from your above query>
where gender = 'M'
union all
select <from your above query>
where gender = 'F'
 and hds.donor_id in (select di.donor_id
                              from donor_interdictions_don di
                                       where di.donor_id = hds.donor_id
                                           and di.inter_code = 'HLAN'
                                           and di.term_date is null)
0
 
sdstuberCommented:
executing the query twice shouldn't be necessary, that's a lot of overhead when a simple "or" is sufficient
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
awking00Commented:
Sorry, sdstuber, I didn't see your response before I posted or I wouldn't have since you're right :-)
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
@awking00: avoid using correlated sub queries by simply rewriting your select statement using the WITH clause(replacing the 2 sub selects with just one you can use as often as you want within the main query).  I suppose you're familiar with that
0
 
anumosesAuthor Commented:
Alexander many have suggested in the past and I remember you giving me a query. But can you help me in the above using a with clause? As a matter any expert who can help me first.
0
 
sdstuberCommented:
the "with clause" suggestion was directed to awking00,  it doesn't really apply to your query.

You "could" use one, but it wouldn't help.


For example, using the answer already given (the first post)
  SELECT sch_date,
         sch_time,
         hds.donor_id,
         gender,
         TO_CHAR(TO_DATE(sch_time, 'HH24:MI:SS'), 'HH:MI:SS AM') AS convert_date,
         site_id,
         hds.first_name,
         hds.last_name,
         mc.description
    FROM donors_don d, hbc_donor_schedule hds, master_codes mc
   WHERE sch_date = TRUNC(SYSDATE)
     AND hds.status = 'CNFRM'
     AND proc_code = 'WB'
     AND drive_id IS NULL
     AND donation_type = 'AL'
     AND mc.code_type = 'ABO'
     AND mc.udf1 = TO_CHAR(hds.blood_type)
     AND d.donor_id = hds.donor_id
     AND (gender = 'M'
       OR (gender = 'F'
       AND hds.donor_id IN (SELECT di.donor_id
                              FROM donor_interdictions_don di
                             WHERE di.donor_id = hds.donor_id
                               AND di.inter_code = 'HLAN'
                               AND di.term_date IS NULL)))
     AND hds.donor_id IN (SELECT donor_id
                            FROM ht_bc_don_elig_vw hbdev
                           WHERE hds.donor_id = hbdev.donor_id
                             AND (platelet_date IS NULL
                               OR platelet_date < TRUNC(SYSDATE)
                               OR plasma_date IS NULL
                               OR plasma_date < TRUNC(SYSDATE)))
ORDER BY TO_DATE(sch_time, 'HH24:MI:SS');

Open in new window


you could rewrite it using WITH and it might look like this...
but note, this is purely cosmetic.  There is nothing inherently better about this


WITH female_donor_check
     AS (SELECT di.donor_id
           FROM donor_interdictions_don di
          WHERE di.donor_id = hds.donor_id AND di.inter_code = 'HLAN' AND di.term_date IS NULL),
     all_donor_check
     AS (SELECT donor_id
           FROM ht_bc_don_elig_vw hbdev
          WHERE hds.donor_id = hbdev.donor_id
            AND (platelet_date IS NULL
              OR platelet_date < TRUNC(SYSDATE)
              OR plasma_date IS NULL
              OR plasma_date < TRUNC(SYSDATE)))
  SELECT sch_date,
         sch_time,
         hds.donor_id,
         gender,
         TO_CHAR(TO_DATE(sch_time, 'HH24:MI:SS'), 'HH:MI:SS AM') AS convert_date,
         site_id,
         hds.first_name,
         hds.last_name,
         mc.description
    FROM donors_don d, hbc_donor_schedule hds, master_codes mc
   WHERE sch_date = TRUNC(SYSDATE)
     AND hds.status = 'CNFRM'
     AND proc_code = 'WB'
     AND drive_id IS NULL
     AND donation_type = 'AL'
     AND mc.code_type = 'ABO'
     AND mc.udf1 = TO_CHAR(hds.blood_type)
     AND d.donor_id = hds.donor_id
     AND (gender = 'M'
       OR (gender = 'F' AND hds.donor_id IN (SELECT donor_id FROM female_donor_check)))
     AND hds.donor_id IN (SELECT donor_id FROM all_donor_check)
ORDER BY TO_DATE(sch_time, 'HH24:MI:SS');

Open in new window

0
 
anumosesAuthor Commented:
thanks
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now