[Closed] SQL query (totally out of my depth)

Not enough info.

Presumably live and archive share the same structure but the data contents vary ?

And are the following statements correct ?

1. if an entry exists in live then it's the most recent and the one you want regardless of any archive entry
2. if an entry doesn't exist in live then the archive entry is the one you want

Both statements are correct.

Depends on what the value you want is? (number, date, string)
You could union 2 selects of everything to create a view that you then group to give you the MAX value (if its a number or date) from the view.

As all the pies said, not really enough info to help, but hope that helps a bit!

As above, use a UNION operator to select from both live and archive and then get the latest row using a MAX clause on a timestamp column (assuming you have one to reflect the "freshness" of each row)

allthepies

I'm at home at the moment and only have limited access to the data, its a string that I need to return, this is what I'm using to return the string (controlentries) from the live table:-
SQL13=SingleRecord, SELECT * FROM %ActiveOperatorLog% WHERE ControlEntries Like '%Chemistry%' Order By LogDate DESC

I'm trying to retrieve controlentries from OperatorArchive if they're not in the ActiveOperatorLog.
I hope this makes more sense.

what allthepies said

edit: Although union all is quicker (but may produce dupes) in SQL server, can't remember if that's ansi sql or available in Oracle.

Do you mean
A)trying to retrieve a single latest record from either live or archive
b) trying to receive all latest records from both tables where one column says the record type and another is the timestamp?

If a) then just write the two selects (ordered by time desc, live table first), union them and limit 1 to make only one record be returned. Should even be nice and only scan the live table if it finds it there.

If it is b) then I would be thinking of selecting max timestamps from both tables, then selecting the records relating to those timestamps.

I'm reasonably sure you can union all in Oracle, the dupes shouldn't matter if there is going to be a max anyway.

Something to bear in mind - that LIKE clause will be pretty slow, especially with the wild card at the front AND back. If you can possibly constrain it to 'Chemistry%' it'll perform a lot better...

I was going to say just union it, then select the max, but there are some tidier ideas up there, especially if you've got indexes on the date columns.

An aggregate of a union of two aggregate subqueries. This stuff gets me excited. I nearly exploded the other day when I got to do a self-join on a self-subquery to a join to another self-join. Had to think in about 6 dimensions.

I'm trying to retrieve controlentries from OperatorArchive if they're not in the ActiveOperatorLog. I hope this makes more sense

Here is my interpretation.
Although a UNION or UNION ALL may well suit - it looks to me like you are after a single records answer.
If you are after a record from either table as a minimum then you will have to add a UNION ALL to the select statement below and query the ActiveEntries.

Using wildcard searches on columns disables indexes. Your ControlEntries column looks like a key column which may cause problems if you are accessing large *volumes* of data.
So if you can use a clause like - ControlEntries = 'Chemistry'

SELECT OA.*
FROM %OperatorArchive% OA
WHERE OA.ControlEntries LIKE'%Chemistry%'
AND NOT EXISTS
(SELECT 1 FROM %ActiveOperatorLog% AOL
WHERE AOL.ActiveEntries LIKE '%Chemistry%'
AND AOL.ActiveEntries = OA.ControlEntries)

This page should blow your mind then 🙂

[url= http://psoug.org/reference/analytic_functions.html ]Analytic functions[/url]

Like searching suggests a badly designed data structure - check the table for something more practical that is indexed.

Thanks for the replys I'll have a play and let you know how I get on.