Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two log tables, which are populated from two different PL/SQL packages. It's often useful to keep the data separate, but sometimes I'd like to "interleave" the two tables so I can see what's going on in both pieces of code. Both tables have a time-stamp column (although the columns have different names). Here's a stripped-out version of the two tables, and what I'd like as a result:

Table LOG_1:

TIME_CREATED                  ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824000  Log_1 entry 1
11-SEP-12 12.39.16.037824002  Log_1 entry 2
11-SEP-12 12.39.16.037824004  Log_1 entry 3

Table LOG_2:

TIME_CREATED                  ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824001  Log_2 entry 1
11-SEP-12 12.39.16.037824003  Log_2 entry 2
11-SEP-12 12.39.16.037824005  Log_2 entry 3

New table LOG_COMBINED:

TIME                          ENTRY_TEXT
-------------------------------------------
11-SEP-12 12.39.16.037824000  Log_1 entry 1
11-SEP-12 12.39.16.037824001  Log_2 entry 1
11-SEP-12 12.39.16.037824002  Log_1 entry 2
11-SEP-12 12.39.16.037824003  Log_2 entry 2
11-SEP-12 12.39.16.037824004  Log_1 entry 3
11-SEP-12 12.39.16.037824005  Log_2 entry 3

(Note the [artificial] difference in the timestamps!)

I'm sure this can be done in a simple SQL statement - perhaps involving a union all?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

select 'LOG_1' AS LOG_SOURCE, TIME_CREATED,ENTRY_TEXT FROM LOG_1

UNION ALL

select 'LOG_2', TIME_CREATED,ENTRY_TEXT FROM LOG_2

ORDER BY 2

I formalize this kind of logic as a view so you can document and comment it.

CREATE OR REPLACE FORCE VIEW COMBINED_LOGS

(LOG_SOURCE,

TIME_CREATED,

ENTRY_TEXT ) AS

select 'LOG_1' , TIME_CREATED,ENTRY_TEXT FROM LOG_1

UNION ALL

select 'LOG_2', TIME_CREATED,ENTRY_TEXT FROM LOG_2;

COMMENT ON TABLE COMBINED_LOGS IS 'Explanatory comment goes here';

This works for me on Oracle 11g R2 and Oracle 9i, perhaps it was the use of keyword SOURCE as a column name which I have corrected to LOG_SOURCE

Edit: Corrected to UNION all after the comment. See here for more discussion on the details of UNIONS. This should be done for performance reasons and to ensure identical records are not removed.

share|improve this answer
    
Thanks - that works perfectly. However, Oracle (11g-R2, SQL Developer 3.2) won't let me save that as a view, because: "The SQL syntax is valid, however the query is invalid or uses functionality that is not supported. Declarative query support does not currently include UNION, INTERSECT or MINUS" –  Allan Lewis Sep 11 '12 at 13:29
    
OK... actually it does let me create the view, despite that error - isn't Oracle wonderful... –  Allan Lewis Sep 11 '12 at 13:31
    
I don't think it's the column name - I've also tried changing it and it reports the same error, but creates the view nonetheless. –  Allan Lewis Sep 11 '12 at 13:37
1  
Please do use UNION ALL, otherwise you would risk losing some rows! (as OP rightly suggested) –  kubanczyk Sep 12 '12 at 12:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.