Looping through a table to find records
Looping through a table to find records
(OP)
Thanks in advance for all of your time and help. It is greatly appreciated!!
First, I need to find the record where -
METRIC_INTERNAL_NAME = 'LOGIN' AND DATA_MNEMONIC_ID = 'DEP'
Then collect all records until
METRIC_INTERNAL_NAME = 'LOGOUT' AND DATA_MNEMONIC_ID = 'TIMEOUT'
then loop through and find the next records where the conditions above are true.
I was thinking (but what do I know) of writing each group of records to a temp table.
First, I need to find the record where -
METRIC_INTERNAL_NAME = 'LOGIN' AND DATA_MNEMONIC_ID = 'DEP'
Then collect all records until
METRIC_INTERNAL_NAME = 'LOGOUT' AND DATA_MNEMONIC_ID = 'TIMEOUT'
then loop through and find the next records where the conditions above are true.
I was thinking (but what do I know) of writing each group of records to a temp table.
RE: Looping through a table to find records
djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!
RE: Looping through a table to find records
Typically any sql query is an iteration of data, it just needs an ORDER BY sometime and where clauses for lower and upper bound (WHERE sometime>X AND sometime<Y) to get the portion of data you want.
Bye, Olaf.
RE: Looping through a table to find records
RE: Looping through a table to find records
FYI: You jump on false conclusions, if you think you can't make use of table indexes when querying a view, just because thew view itself is not materialized and has no index.
1. I have two tables headtable and detailtable and did the following query, which is optimized by indexes:
CODE
2. I created a view (view_all) with this query:
CODE
3. I used this query on the view and it is optimized on the same indexes as the original query is:
CODE
So indeed querying a view you make use of all indexes the underlying tables have, as a view is nothing but a query and queries are optimized, if you use a view as a table it's just like using a CTE, and CTEs also make use of indexes. Views themselves have no indexes, because they have no permanent data (unless you create a 'materialized view, but that's not applicable for such log data). You have all the advantages (and disadvantages) of the optimization of queries with a view or a query including a view. You can imagine the view query used as aliased (named) subquery and the overall query is analyzed and optimized.
Bye, Olaf.