6 Replies Latest reply: Feb 1, 2012 12:31 AM by 883627 RSS

    Sequence Checking

    883627
      i have 2 table as like below:

      table1          
      type     partisi     priority
      A     AA     1
      A     BB     2
      A     CC     3
      B     AA     1
      B     CC     2
      B     DD     3

      table2          
      type     partisi     priority     expected result
      A     AA     2     1
      A     BB     1     2
      A     CC     3     3
      B     DD     1     2
      B     CC     2     1

      Based on the table1, i want to checking the priority on table2 is correct or not. Column of expected result is expected result from query.
      i have no any idea for checking this case by query.

      Are there can some person solve this case ?
        • 1. Re: Sequence Checking
          Liron Amitzi
          I didn't really get what you are trying to do.
          Is the key between the tables "type" and "partisi"?
          Then what do you want to compare table1.priority to table2.expected_result?
          What do you want to see, all rows where the priority is not equal to expected_result?
          • 2. Re: Sequence Checking
            Umesh Gupta
            Rahardi Trisna wrote:
            i have 2 table as like below:

            table1          
            type     partisi     priority
            A     AA     1
            A     BB     2
            A     CC     3
            B     AA     1
            B     CC     2
            B     DD     3

            table2          
            type     partisi     priority     expected result
            A     AA     2     1
            A     BB     1     2
            A     CC     3     3
            B     DD     1     2
            B     CC     2     1

            Based on the table1, i want to checking the priority on table2 is correct or not. Column of expected result is expected result from query.
            i have no any idea for checking this case by query.

            Are there can some person solve this case ?
            if i understood u correctly, u need this
            with a
            as (
            select 'A' type, 'AA' partisi, 1 priority from dual union all
            select 'A', 'BB', 2 from dual  union all
            select 'A', 'CC', 3 from dual union all
            select 'B', 'AA', 1 from dual union all
            select 'B', 'CC', 2 from dual union all
            select 'B', 'DD', 3 from dual ),
            
            b as
            (select 'A' type, 'AA' partisi, 2 priority from dual union all
            select 'A', 'BB', 1 from dual  union all
            select 'A', 'CC', 3 from dual union all
            select 'B', 'DD', 1 from dual  union all
            select 'B', 'CC', 2 from dual)
            
            select b.type,b.partisi,b.priority,a.priority expected_result 
            from a,b
            where  a.type=b.type
            and a.partisi =b.partisi
            or elobrate the problem more clearly..
            Regards

            Umesh
            • 3. Re: Sequence Checking
              883627
              thanks for your response, but until not yet answered. your result like this:

              TYPE     PARTISI     PRIORITY     EXPECTED_RESULT
              A     AA     2     1
              A     BB     1     2
              A     CC     3     3
              B     CC     2     2
              B     DD     1     3

              my expected is

              TYPE     PARTISI     PRIORITY     EXPECTED_RESULT
              A     AA     2     1
              A     BB     1     2
              A     CC     3     3
              B     CC     2     *1*
              B     DD     1     *2*


              Sorted by priority. fixed value of 1 should be filled, so that the smallest expected result here is 2 go up to 1. the sample like this:
              2-->up to become 1
              4-->up to become 3
              3-->up to become 2

              May be my explaination make you undestand.

              very thanks for all
              • 4. Re: Sequence Checking
                883627
                hi Liron Amitzi, i just want get sequence number. the sequence number replace back to number 1 again if get difference partisi column.
                • 5. Re: Sequence Checking
                  Liron Amitzi
                  You can use the row_number analytic function:
                  select b.type,b.partisi,b.priority,a.priority expected_result, row_number() over (partition by b.type order by b.partisi)
                  from a,b
                  where  a.type=b.type
                  and a.partisi =b.partisi
                  Does this help?
                  • 6. Re: Sequence Checking
                    883627
                    thanks Liron, you are cleaver