DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to avoid repeat where clause in oracle sql

Hybrid View

  1. #1
    Join Date
    Jun 2013
    Posts
    1

    How to avoid repeat where clause in oracle sql

    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. #2
    Join Date
    Nov 2021
    Posts
    7
    Your SQL is so long that i don't have patient enough to got it.

  3. #3
    Join Date
    Apr 2011
    Location
    Largo, FL.
    Posts
    38
    Please Note the date this thread was started.... Year -> 2013

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width