4 Replies Latest reply on Dec 19, 2013 11:06 AM by Solomon Yakobson

    help on Query

    Murugesapandi

      Hi All

       


      Transaction_No

      Total

      20

      40

      20

      -20

      41

      40

      41

      -40.

      81

      40

      81

      -40

      42

      38

       

      Find the above records.

      Transaction No 20 has +40, -20 --->+20

      Transaction No 41 has +40, -40 --->0

      Transaction No 81 has +40 , -40 --->0

      Transaction No 42 has 38 --+ and it has 1 transcation where as all other entities has more than 1 transaction.

       

      Required data:

      if the total is equal to 0 then

      ----script1

      else if total is not equal to 0 and no.of transactions > 1 then

      ---script2

      else

      script3

      end

       

      Help me to sort out this.

        • 1. Re: help on Query
          SKP

          What are these Script1,Script2, and Script3?

          • 2. Re: help on Query
            Partha Sarathy S

            Kindly provide the CREATE AND INSERT TABLE scripts and Oracle Version you are using. What do these SCRIPT1, SCRIPT2, SCRIPT3 mean? Does it mean if these IF conditions satisfy those particular scripts must be invoked? If they are not actual scripts to be invoked, then the below query would help. If they are scripts to be invoked, then this cannot be done through SQL query.

             

            WITH T1 AS

              (SELECT 20 TRANSACTION_NUMBER, 40 TOTAL FROM DUAL

              UNION ALL

              SELECT 20,-20 FROM DUAL

              UNION ALL

              SELECT 41,40 FROM DUAL

              UNION ALL

              SELECT 41,-40 FROM DUAL

              UNION ALL

              SELECT 81,40 FROM DUAL

              UNION ALL

              SELECT 81,-40 FROM DUAL

              UNION ALL

              SELECT 42,38 FROM DUAL

              )

            SELECT TRANSACTION_NUMBER,

              CASE

                WHEN (SUM_TOTAL = 0)

                THEN 'SCRIPT1'

                WHEN (SUM_TOTAL !=0

                AND CNT_TRANS         >1)

                THEN 'SCRIPT2'

                ELSE 'SCRIPT3'

              END AS "CASE_RESULT"

            FROM

              (SELECT TRANSACTION_NUMBER,

                SUM(TOTAL) sum_total,

                COUNT(TRANSACTION_NUMBER) cnt_trans

              FROM T1

              GROUP BY TRANSACTION_NUMBER

              );

            1 person found this helpful
            • 3. Re: help on Query
              Karthick2003

              SQL> with t
                2  as
                3  (
                4  select 20 transaction_no, 40 total
                5    from dual
                6  union all
                7  select 20 transaction_no, -20 total
                8    from dual
                9  union all
              10  select 41 transaction_no, 40 total
              11    from dual
              12  union all
              13  select 41 transaction_no, -40 total
              14    from dual
              15  union all
              16  select 81 transaction_no, 40 total
              17    from dual
              18  union all
              19  select 81 transaction_no, -40 total
              20    from dual
              21  union all
              22  select 42 transaction_no, 38 total
              23    from dual
              24  )
              25  select transaction_no
              26       , case when total = 0 then 'SCRIPT1'
              27              when total != 0  and cnt > 1 then 'SCRIPT2'
              28              else 'SCRIPT3'
              29         end script_to_run
              30    from (
              31            select transaction_no
              32                 , sum(total) total
              33                 , count(transaction_no) cnt
              34              from t
              35             group
              36                by transaction_no
              37         )
              38   order
              39      by transaction_no;

               

              TRANSACTION_NO SCRIPT_TO_RUN
              -------------- --------------------
                          20 SCRIPT2
                          41 SCRIPT1
                          42 SCRIPT3
                          81 SCRIPT1

              1 person found this helpful
              • 4. Re: help on Query
                Solomon Yakobson

                for v_rec in (

                              select  transaction_no,

                                      sum(total) total,

                                      count(*) cnt

                                from  your_table

                                group by transaction_no

                             ) loop

                if the v_rec.total = 0 then

                ----script1

                elsif v_rec.total != 0 and v_rec.cnt > 1 then

                ---script2

                else

                script3

                end if;

                end loop;

                 

                will give you all you need.

                 

                SY.