5 Replies Latest reply: Feb 21, 2014 10:51 AM by AnnPricks E RSS

Oracle query

user_anumoses Newbie
Currently Being Moderated

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.

  • 1. Re: Oracle query
    GregV Guru
    Currently Being Moderated

    Hi,

     

    Maybe I'm missing something but I feel both SELECT statements in this UNION are the same, is it normal?

    If you need to add one column then you need to tell us where to find it and how.

    Thanks

  • 2. Re: Oracle query
    BluShadow Guru Moderator
    Currently Being Moderated

    It's not clear what your issue is.

    Remember, people here don't know your database or data, so without you providing details of the table structures and relationships, along with some suitable example data and expected output, we're going to find it difficult to help and give the correct answer (we'd just be guessing).

     

    Please read: Re: 2. How do I ask a question on the forums?

  • 3. Re: Oracle query
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    This sounds like a job for a CASE expression, such as

     

    CASE

        WHEN  gender      = 'M'               THEN  NULL

        WHEN  tested      = 'N'               THEN  'N'

        WHEN  inter_code  IN ('HLAP', HLAN')  THEN  'Y'

                                              ELSE  '?'

    END    AS hla_tested

    What if there was a test, but inter_code is not 'HLAP' or 'HLAN'?  The ELSE clause says what to return in that situation.

     

    I hope this answers your question.

    If not, post  a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.

    Post your query, modified to use a CASE expression like the one above.  Point out where that query is getting the wrong results, and explain, using specific examples, how you get the right results from the given data in those places.

    See the forum FAQ: https://forums.oracle.com/message/9362002

  • 4. Re: Oracle query
    user_anumoses Newbie
    Currently Being Moderated

    To answer to your question. There will be other tests. If not in HLAP or HLAN then 'N'

  • 5. Re: Oracle query
    AnnPricks E Guru
    Currently Being Moderated

    I couldn't see any difference in both queries. I just add one more column based on what you said... I think, you have already checked for female (tested or not and inter_code IN('HLAN','HLAP'))(mark it as red). So you are selecting only tested and inter_code in hlap and hlan.. So no need to check that. Just give 'Y' for female. If i am wrong then tell me how to check tested or not and in intercode hlap and hlan. Please try the below query and let me know in case of any issues

    SELECT donor_id,

           last_name,

           first_name,

           gender,

           description,

           CASE WHEN gender = 'M' THEN NULL

           ELSE 'Y'

           END AS hla_tested

    FROM

    (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))));

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points