4 Replies Latest reply: Jan 31, 2013 2:30 PM by Rb2000rb65 RSS

    Help with query using MINUS

    Rb2000rb65
      Hello Experts

      I am having trouble in select the record_sequence into the output. Please see the desired results part.
      Please help in solving this issue.

      The Oracle version I am working on is

      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      Thanks

      RB

      WITH TABLE1 AS
      (
      SELECT '28' EXAM_CD1, '29' EXAM_CD2,'10' EXAM_CD3, '111' CAND_ID FROM DUAL UNION ALL
      SELECT '21' EXAM_CD1, '39' EXAM_CD2,'20' EXAM_CD3, '112' CAND_ID FROM DUAL UNION ALL
      SELECT '22' EXAM_CD1, '49' EXAM_CD2,'30' EXAM_CD3, '113' CAND_ID FROM DUAL UNION ALL
      SELECT '23' EXAM_CD1, '59' EXAM_CD2,'40' EXAM_CD3, '114' CAND_ID FROM DUAL UNION ALL
      SELECT '24' EXAM_CD1, '69' EXAM_CD2,'50' EXAM_CD3, '115' CAND_ID FROM DUAL)
      ,TABLE2 AS
      (
      SELECT '28' EXAM_CD, '111' CANDID, 1 RECORD_SEQ FROM DUAL UNION ALL
      SELECT '30' EXAM_CD, '113' CANDID, 2 RECORD_SEQ FROM DUAL UNION ALL
      SELECT '94' EXAM_CD, '111' CANDID, 3 RECORD_SEQ FROM DUAL UNION ALL
      SELECT '69' EXAM_CD, '115' CANDID, 4 RECORD_SEQ FROM DUAL)
      (
      SELECT CANDID,EXAM_CD FROM TABLE2
      MINUS
      SELECT CAND_ID,
      MAX(CASE L WHEN 1 THEN EXAM_CD1 WHEN 2 THEN EXAM_CD2 ELSE EXAM_CD3 END) exam_code
      FROM TABLE1,
      (SELECT LEVEL L FROM DUAL CONNECT BY LEVEL <= 3)
      GROUP BY CAND_ID,L)

      The desired result is

      CAND_ID,  EXAM_CD, RECORD_SEQ
      *111 , 94 , 3*
        • 1. Re: Help with query using MINUS or any other solution
          Rb2000rb65
          The solution may not be using minus as long as I am getting my results using the latest functions it is good with me.

          Thanks

          RB
          • 2. Re: Help with query using MINUS
            Frank Kulash
            Hi,
            Rb2000rb65 wrote:
            The solution may not be using minus as long as I am getting my results using the latest functions it is good with me.
            Good idea!
            MINUS is not the best tool for this job. The query you posted gets the exam_cd and candid that you want, but it can't get the record_seq because there's nothing like record_seq in table1.

            You can do it this way:
            SELECT     *
            FROM     table2     m
            WHERE     NOT EXISTS (
                                SELECT  1
                            FROM    table1
                            WHERE   m.candid      = candid
                            AND     m.exam_cd  IN ( exam_cd1
                                                     , exam_cd2
                                         , exam_cd3
                                         )
                         )
            ;
            I suppose you could use MINUS, like this
            SELECT     *
            FROM     table2     
            WHERE     (exam_cd, candid) 
                 IN ( 
                        SELECT  ...  -- The MINUS query you posted goes here 
                    )
            ;
            but it's unecessarily complicated.
            • 3. Re: Help with query using MINUS
              Solomon Yakobson
              Frank already did it.

              SY.

              Edited by: Solomon Yakobson on Jan 31, 2013 2:59 PM
              • 4. Re: Help with query using MINUS
                Rb2000rb65
                Thanks Frank. I got the solution.

                Thanks for all your help.

                Regards

                RB