7 Replies Latest reply on Sep 5, 2015 8:53 AM by Hjava

    add condition when merge?

    Hjava

      can I add if   flag='A' before the insert ?

       

      merge into A j
      
          using ( select * from B where flag='O') csl
      
          on (nj.appno=csl.appno
      
          AND nvl(j.exam_type, 'null') = nvl(csl.exam_type, 'null')
      
          AND nvl(j.exam_year,    'null') = nvl(csl.exam_year,    'null')
      
          AND nvl(j.exam_month,    'null') = nvl(csl.exam_month,    'null')
      
             AND vl(j.exam_level,    'null') = nvl(csl.exam_level,    'null')
      
             AND vl(j.exam_board,    'null') = nvl(csl.exam_board,    'null')
      
             AND vl(j.subject_code,  'null') = nvl(csl.subject_code,  'null')
      
             AND vl(j.subject_other, 'null')= nvl(csl.subject_code_other, 'null')
      
          )
      
          when  matched then
      
             update set j.grade=csl.grade,
      
                        j.grade_other=csl.grade_other 
      
          when not matched then
      
          insert (j.appno, j.exam_type, j.exam_year, j.exam_month, j.exam_level,
      
                   j.exam_board, j.subject_code,j.subject_other,
      
          j.actual_grade, j.actual_grade_other,
      
          j.predict_grade, j.predict_grade_other, j.release_date, j.official_grade, j.counselor_grade,
      
          j.counselor_grade_other)  values(csl.appno, csl.exam_type, csl.exam_year, csl.exam_month, csl.exam_level,
      
          csl.exam_board, csl.subject_code, csl.subject_code_other, csl.actual_grade, csl.actual_grade_other,
      
          csl.predict_grade, csl.predict_grade_other,  csl.release_date,  csl.official_grade, csl.counselor_grade,
      
          csl.counselor_grade_other
      
          );
      
        • 1. Re: add condition when merge?
          KiwiAndrew

          Almost

           

          You can have a WHERE clause after the INSERT() e.g. INSERT (...) values (...) WHERE FLAG='A';

          • 2. Re: add condition when merge?

            KiwiAndrew wrote:

             

            Almost

             

            You can have a WHERE clause after the INSERT() e.g. INSERT (...) values (...) WHERE FLAG='A';

            Almost

             

            The WHERE clause for an INSERT can ONLY refer to source columns. And this is OPs USING clause

                using ( select * from B where flag='O') csl 

            Although FLAG is a source column that clause only selects rows for FLAG = 'O' so flag can NEVER be 'A'.

            • 3. Re: add condition when merge?

              Wrong forum!

               

              Please post future SQL questions, and any followup to this one, in the Sql and PL/SQL forum

              SQL & PL/SQL

               

              This forum is ONLY for Sql Developer questions.

              Hjava wrote:

               

              can I add if   flag='A' before the insert ?

               

              1.  
              2.     using ( select * from B where flag='O') csl 
              3.  

              No - a WHERE clause for an INSERT can ONLY use columns from the source.

               

              Since you USING clause only selects rows where flag is 'O' then flag will NEVER BE 'A'.

              http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

              Specify the where_clause if you want Oracle Database to execute the insert operation only if the specified condition is true. The condition can refer only to the data source table. Oracle Database skips the insert operation for all rows for which the condition is not true.

              So if you use a WHERE clause as the other responder suggests the result will be that ALL ROWS will be skipped.

              • 4. Re: add condition when merge?
                KiwiAndrew
                Since you USING clause only selects rows where flag is 'O' then flag will NEVER BE 'A'.

                 

                The OP asked if filtering the insert is possible, not whether it would be useful

                • 5. Re: add condition when merge?

                  KiwiAndrew wrote:

                   

                  Since you USING clause only selects rows where flag is 'O' then flag will NEVER BE 'A'.

                   

                  The OP asked if filtering the insert is possible, not whether it would be useful

                  Really? You think OP and others don't care whether the answers people give are 'useful'? You can't be serious!

                   

                  Sorry but that is really lame. I could understand if you had just said that you overlooked that initial query condition.

                  • 6. Re: add condition when merge?
                    KiwiAndrew

                    You're right - I wasn't serious - that's why there was that little picture of a smiley face at the end of my post...

                    • 7. Re: add condition when merge?
                      Hjava

                      should be using

                      ( select * from B where flag<>'O') csl