1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to avoid repeat where clause in oracle sql

Discussion in 'SQL PL/SQL' started by murman, Jun 4, 2013.

  1. murman

    murman Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    Please find my query below, I need a help to avoid duplication of **where** clause in my query.

    In my below query, **JOIN** condition is same for both the queries and **WHERE** condition also same except this clause "and code.code_name="transaction_1"
    In **IF ** condition only credit and debit is swapped on both queries, due to this **Credit and Debit** and this where clause "and code.code_name="transaction_1" I am duplicating the query. Can you please give some solution to avoid this duplication. I am using oracle 11g

    select DAY as business_date,sum(amount) as AMOUNT, type_amnt as amount_type,test_code as code_seg
    from
    (
    select table1_alias.date as DAY,code.code_numb as test_code,
    CASE
    WHEN qnty_item > 0 THEN 'credit'
    ELSE 'debit'
    END as type_amnt,
    "25.55" as amount
    from
    code_table code,
    table1 table1_alias
    join table2 table2_alias on table1_alias.id = table2_alias.id
    where
    table1_alias.state="OK"
    and table1_alias.type="R"
    and code.code_type="Movie"
    and code.code_name="transaction_1"
    UNION ALL

    select table1_alias.date as DAY,code.code_numb as test_code,
    CASE
    WHEN qnty_item > 0 THEN 'debit'
    ELSE 'credit'
    END as type_amnt,
    "25.55" as amount
    from
    code_table code,
    table1 table1_alias
    join table2 table2_alias on table1_alias.id = table2_alias.id
    where
    table1_alias.state="OK"
    and table1_alias.type="R"
    and code.code_type="Movie"
    and code.code_name="transaction_2"
    )
    group by DAY, test_code,type_amnt


    Thanks
     
  2. ac.arijit

    ac.arijit Forum Advisor

    Messages:
    217
    Likes Received:
    22
    Trophy Points:
    280
    Location:
    Kolkata, India
    Hi,

    Try this..
    Code (SQL):

    SELECT  DAY             AS business_date
           ,SUM (amount)    AS amount
           ,type_amnt       AS amount_type
           ,test_code       AS code_seg
    FROM   (SELECT  table1_alias.DATE   AS DAY
                   ,code.code_numb      AS test_code
                   ,DECODE(code.code_name
                          ,'transaction_1',DECODE(SIGN(qnty_item)
                                                 ,1,'credit'
                                                 ,'debit'
                                                 )
                          ,'transaction_2',DECODE(SIGN(qnty_item)
                                                 ,1,'debit'
                                                 ,'credit'
                                                 )
                          )             AS type_amnt
                   ,"25.55"             AS amount
            FROM    code_table  code
                   ,table1      table1_alias
                    JOIN    table2  table2_alias
                        ON  table1_alias.ID =   table2_alias.ID
            WHERE   table1_alias.state  =   "OK"
            AND     table1_alias.TYPE   =   "R"
            AND     code.code_type      =   "Movie"
           )
    GROUP   BY  DAY
               ,test_code
               ,type_amnt;
     
     
  3. murman

    murman Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Thanks Ari for your quick response. As per your query it will return only one result but my requirement is it should return 2 result set, one for credit and another one for debit.
    Sample result:

    business_date amount amount_type code_seg
    20130604 25 credit 35627
    20130604 25 debit 38383

    I need an output like above,how can I achieve this scenario?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to add the criteria Ari forgot to add:

    Code (SQL):
    SELECT DAY AS business_date
           ,SUM (amount) AS amount
           ,type_amnt       AS amount_type
           ,test_code       AS code_seg
    FROM (SELECT  table1_alias.DATE AS DAY
                   ,code.code_numb      AS test_code
                   ,DECODE(code.code_name
                          ,'transaction_1',DECODE(SIGN(qnty_item)
                                                 ,1,'credit'
                                                 ,'debit'
                                                 )
                          ,'transaction_2',DECODE(SIGN(qnty_item)
                                                 ,1,'debit'
                                                 ,'credit'
                                                 )
                          ) AS type_amnt
                   ,'25.55' AS amount
            FROM    code_table  code
                   ,table1      table1_alias
                    JOIN    table2  table2_alias
                        ON  table1_alias.ID =   table2_alias.ID
            WHERE   table1_alias.state  = 'OK'
            AND     table1_alias.TYPE = 'R'
            AND     code.code_type      = 'Movie'
            AND code.code_name IN ('transaction_1','transaction_2')
           )
    GROUP BY DAY
               ,test_code
               ,type_amnt;
     
     
  5. murman

    murman Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    sweet :) It works fine. Thank your very much.