Don't repeat yourself: same SQL query, but two different tables

Question

I have an SQL query with exact the same code, but two different tables (AUDIT_TRAIL_ARCHIVE and AUDIT_TRAIL). I use "UNION ALL" to have one result.

Good programmers use "Don't repeat yourself" principle. Good programmers avoid WET (write everything twice).

Howto rewrite this code with "Don't repeat yourself" principle?

SELECT REPLACE (ENTITY_KEY, 'rss_user_name=CN=', '')
 FROM    AUDIT_TRAIL_ARCHIVE AU
   INNER JOIN
      (SELECT RSS_USER_NAME
         FROM RSS_USER
        WHERE RSS_NAME = 'rmad'
              AND ADD_INFO_MASTER LIKE '%__47__UPN=%@richemont.com%') FALSCH
   ON REPLACE (AU.ENTITY_KEY, 'rss_user_name=CN=', '') =
         FALSCH.RSS_USER_NAME
WHERE     AU.RSS_NAME = 'rmad'
   AND AU.TABLE_NAME = 'rss_user'
   AND AU.ACTION = 'Insert'
   AND AU.ENTITY_KEY LIKE 'rss_user_name=CN=%'
   AND AU.ORIGIN != 'RSS'
UNION ALL
SELECT REPLACE (ENTITY_KEY, 'rss_user_name=CN=', '')
 FROM    AUDIT_TRAIL AU
   INNER JOIN
      (SELECT RSS_USER_NAME
         FROM RSS_USER
        WHERE RSS_NAME = 'rmad'
              AND ADD_INFO_MASTER LIKE '%__47__UPN=%@richemont.com%') FALSCH
   ON REPLACE (AU.ENTITY_KEY, 'rss_user_name=CN=', '') =
         FALSCH.RSS_USER_NAME
WHERE     AU.RSS_NAME = 'rmad'
   AND AU.TABLE_NAME = 'rss_user'
   AND AU.ACTION = 'Insert'
   AND AU.ENTITY_KEY LIKE 'rss_user_name=CN=%'
   AND AU.ORIGIN != 'RSS'

What is your goal here: to use dynamic sql, e.g. same sql but diff. tables or rewrite the query as in Florin's example? — Art, Apr 12 '13 at 12:37
@fyodor78 The first solution is not always the best. Of course you can join the tables BEFORE Selecting - but why would you build a temporary table of 100.000 rows, when you want to retrieve one row from each? See at dynamic SQL, create a procedure, build up a query string and reuse that procedure. — dognose, Apr 12 '13 at 12:53

score 5 · Accepted Answer · answered Apr 12 '13 at 12:23

5

For example:

SELECT REPLACE (ENTITY_KEY, 'rss_user_name=CN=', '')
 FROM    (select * --or relevant columns
          from AUDIT_TRAIL_ARCHIVE AU
          union all
          select * 
          from AUDIT_TRAIL AU
           ) AU
   INNER JOIN
      (SELECT RSS_USER_NAME
         FROM RSS_USER
        WHERE RSS_NAME = 'rmad'
              AND ADD_INFO_MASTER LIKE '%__47__UPN=%@richemont.com%') FALSCH
   ON REPLACE (AU.ENTITY_KEY, 'rss_user_name=CN=', '') =
         FALSCH.RSS_USER_NAME
WHERE     AU.RSS_NAME = 'rmad'
   AND AU.TABLE_NAME = 'rss_user'
   AND AU.ACTION = 'Insert'
   AND AU.ENTITY_KEY LIKE 'rss_user_name=CN=%'
   AND AU.ORIGIN != 'RSS'

answered Apr 12 '13 at 12:23

Florin Ghita

17,525
6
57
76

Am I right in thinking that you wouldn't be able to filter the two selects that you `UNION ALL` by values from the rest of the query? – Lilienthal Aug 21 '15 at 14:11
1

No. Actually, the answer filters the two queries in the where clause: `AU.TABLE_NAME = 'rss_user'` . AU is the alias for the union all part. The filter can be pushed by the engine to filter before doing the join. – Florin Ghita Aug 21 '15 at 14:21

Bryan Devaney · Answer 2 · 2013-04-12T12:49:04.823

0

Good programmers use "Don't repeat yourself" principle. Good programmers avoid WET (write everything twice).

Heh. I like that. subtle.

also, I'm probably being way too basic but would something like this work:

CREATE [OR REPLACE] PROCEDURE <name_of_procedure> [ (<ENTITY_KEY_variable>) ]
IS
    <ENTITY_KEY=ENTITY_KEY_variable>
BEGIN
    <your code goes here>

    [EXCEPTION
        exception_section]
END [procedure_name];

edit: i see from the first posted answer I fell for a trolling? silly me.

edited Apr 12 '13 at 12:49

answered Apr 12 '13 at 12:32

Bryan Devaney

188
7

@@Bryan Devaney: Your code will never work in Oracle. Please revise. – Art Apr 12 '13 at 12:35
sorry, popped up under sql, thought it was mysql... updating answer now – Bryan Devaney Apr 12 '13 at 12:41

score 0 · Answer 3 · answered Apr 12 '13 at 12:47

Simply you can not. SQL is compiled language (even if it looks like scripting) and Oracle remembers OBJECT_IDs the query depends. Each half of the query has different dependencies, different "bytecode" and different execution plan.

You can

Use tablespace partitioning. Then you would have only one table. Queries against live data could be restricted using "select * from AUDIT_TRAIL partition ACTIVE".

Use query factoring like

WITH AU AS
(SELECT * from AUDIT_TRAIL union all select * from AUDIT_TRAIL_ARCHIVE)
SELECT REPLACE (ENTITY_KEY, 'rss_user_name=CN=', '')
FROM AU JOIN ...
...

But I'm not sure whether in this case will Oracle guarantee the same efficiency of execution plan.

Don't repeat yourself: same SQL query, but two different tables

3 Answers3