3 Replies Latest reply: Oct 19, 2013 2:33 PM by Rdk RSS

    Query subtracting

    Rdk

      Hi Friends,

       

       

      I am using oracle 10g and forms 6i and 10g and reports 10g and 6i with windows 2008 server

       

       

       

       

       

      I have a query item_a,item_b,item_c and other_item. Each query having different where conditons.

       

       

       

      So I am adding all the queries using union.

       

       

       

      ie

       

       

       

      For Import

       

       

      Add all import amount of  (item_a + item_b + item_c ) and subtract from the total amount based on import

       

       

       

       

       

      Sum(Decode(..amount..)) -

      (SUM(ITEM_A IMPORT)+SUM(ITEM_B IMPORT)+SUM(ITEM_C IMPORT)) Import

       

       

       

      For Export

       

       

       

      Add all export amount of  (item_a + item_b + item_c ) and subtract from the total amount based on export code

       

       

       

      Sum(Decode(..amount..)) -

      (SUM(ITEM_A Export )+SUM(ITEM_B Export)+SUM(ITEM_C Expot)) Export

       

       

       

       

       

       

       

       

      Query start from here

       

      Select 'ITEM_A' item_name, sales_date,

             Sum(Decode(....amount..)) Import,

             Sum(Decode(....amount )) Export

             from sales_dtls t

             group by sales_date

      UNION

      Select 'ITEM_B' item_name ,sales_date,

             Sum(Decode(...amount..)) Import,

             Sum(Decode(...amount..)) Export

             from sales_dtls t

             group by sales_date

      UNION

       

       

      Select 'ITEM_C' item_name , sales_date,

             Sum(Decode(...amount..)) Import,

             Sum(Decode(...amount..)) Export

             from sales_dtls t

             where <CONDITION>

             group by sales_date

      UNION

       

       

       

      Select 'ALL_OTHER_ITEM'  item_name, sales_date,

       

       

      Sum(Decode(..amount..)) -

      (SUM(ITEM_A IMPORT)+SUM(ITEM_B IMPORT)+SUM(ITEM_C IMPORT)) Import

       

       

      Sum(Decode(..amount..)) -

      (SUM(ITEM_A Export )+SUM(ITEM_B Export)+SUM(ITEM_C Expot)) Export

       

       

       

       

       

       

      Rgds

       

       

       

      Rdk

        • 1. Re: Query subtracting
          user614954

          Please ask what your issue is? Also paste some sample data and table creation scripts.

          • 2. Re: Query subtracting
            Rdk

            My out put look like this

             

             

                            IMPORT AMOUNT      EXPORT AMOUNT

            ITEM_A             300                          500

            ITEM_B             400                          900

            ITEM_C             500                          200

            OTHER             600                          500 ----- How to find this value

                                   1800                         2100

             

            TOTAL IMPORT - 1800

            TOTAL EXPORT - 2100

             

             

             

            OTHER IMPORT=TOTAL-(ITEM_A+ITEM_B+ITEM_C)

             

            OTHER EXPORT=TOTAL-(ITEM_A+ITEM_B+ITEM_C)

             

            How to make query to find amount for other_item

             

             

            ie import = 1800-(300+400+500) = 600

            ie export = 2100-(500+900+200) = 500

            • 3. Re: Query subtracting
              Frank Kulash

              Hi,

               

              I'm not sure I understand the problem.

              If you don't want the rows where item_name is ITEM_A, ITEM_B or ITEM_C, then add

               

              NVL (item_name, 'OK')  NOT IN ('ITEM_A', 'ITEM_B', 'ITEM_C')

              to your WHERE clause.  If you're generating thse figures through a UNION (as in your first message), then why not just omit those parts of the UNION?

               

              As mentioned above, you need to post CREATE TABLE and INSERT statements for a little sample data, and the exact output you want from that sample data.  Explain how you get those results from that data.

              See the forum FAQ: https://forums.oracle.com/message/9362002