OraFAQ Forum: Performance Tuning » How to avoid repeat where clause in oracle sql

Home » RDBMS Server » Performance Tuning » How to avoid repeat where clause in oracle sql (Oracle 11g, Windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to avoid repeat where clause in oracle sql [message #586243]

Tue, 04 June 2013 14:53

murman
Messages: 1
Registered: June 2013
Location: bangalore

Junior Member

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

*BlackSwan added {code} tags & formatted SQL. do so yourself in the future.

[Updated on: Tue, 04 June 2013 14:59] by Moderator

Report message to a moderator

Re: How to avoid repeat where clause in oracle sql [message #586244 is a reply to message #586243]

Tue, 04 June 2013 14:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>I need a help to avoid duplication of **where** clause in my query.
WHY?
what do you expect to gain by doing so since you are not charged by the character to input SQL statements.

Report message to a moderator

Re: How to avoid repeat where clause in oracle sql [message #586245 is a reply to message #586243]

Tue, 04 June 2013 15:20

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum. Please read our OraFAQ Forum Guide and How to use [code] tags and make your code easier to read

Apart from that, surely you can extend your case projection to something like

case when qnty_item > 0 and code_name='transaction_1' then 'credit' 
when qnty_item <= 0 and code_name= 'transaction_1' then 'debit' 
when qnty_item > 0 and code_name='transaction_2' then 'debit' 
when qnty_item <= 0 and code_name= 'transaction_2' then 'credit' end

and replace your UNION ALL with an OR predicate?

Report message to a moderator

Previous Topic:	Unable to update newly added column in existing table
Next Topic:	System statistics: demonstration

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 21 15:37:15 CST 2024