5 Replies Latest reply: Feb 21, 2014 12:51 PM by AnnPricks E RSS

    Oracle query

    user_anumoses

      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

          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

            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

              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

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

                • 5. Re: Oracle query
                  AnnPricks E

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