Home » RDBMS Server » Performance Tuning » How to avoid repeat where clause in oracle sql (Oracle 11g, Windows)
How to avoid repeat where clause in oracle sql [message #586243] Tue, 04 June 2013 14:53 Go to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 22926
Registered: January 2009
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.
Re: How to avoid repeat where clause in oracle sql [message #586245 is a reply to message #586243] Tue, 04 June 2013 15:20 Go to previous message
John Watson
Messages: 4709
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?
Previous Topic: Unable to update newly added column in existing table
Next Topic: System statistics: demonstration
Goto Forum:
  


Current Time: Thu Oct 30 18:59:56 CDT 2014

Total time taken to generate the page: 0.08361 seconds