stewashton.wordpress.com
Sync tables: generate MERGE using Unique constraint | An Oracle Programmer
https://stewashton.wordpress.com/2015/01/15/sync-tables-generate-merge-using-unique-constraint
SQL and PL/SQL techniques and solutions. About this blog and test environment. Sync tables: generate MERGE using Unique constraint. In my post “ Compare and sync tables: Generating the code. 8220;, I use the primary key constraint on the target table. A reader called “Bal” asked how to use a unique constraint instead. Here is a list of my posts about comparing and synchronizing tables. I’m going to create a target table with a unique constraint, but without any NOT NULL constraints on the columns. 0 ) wh...
stewashton.wordpress.com
Merging Overlapping Date Ranges with MATCH_RECOGNIZE | An Oracle Programmer
https://stewashton.wordpress.com/2015/06/10/merging-overlapping-date-ranges-with-match_recognize
SQL and PL/SQL techniques and solutions. About this blog and test environment. Merging Overlapping Date Ranges with MATCH RECOGNIZE. I forgot to add a MATCH RECOGNIZE solution to my last post on merging overlapping date ranges. That should take me just a few minutes, right? Wrong: it’s not that easy and here’s why. For test data, please refer to the previous post. To Merge or not to Merge? We sort by start date, then end date. The current row should be merged if its start date is less than or equal to.
stewashton.wordpress.com
Splitting a Table into Rowid Ranges of Equal Size | An Oracle Programmer
https://stewashton.wordpress.com/2015/07/01/splitting-a-table-into-rowid-ranges-of-equal-size
SQL and PL/SQL techniques and solutions. About this blog and test environment. Splitting a Table into Rowid Ranges of Equal Size. In the OTN forum, Jonathan Lewis recently asked for an efficient SQL solution to split a table into 12 ROWID ranges having the same ( -1) number of blocks. I’m posting here a slightly cleaned-up version of my answer so I can change it if necessary after the question gets archived. If you stop at the ANSWER clause, you will see the number of blocks in each bucket. What is the r...
stewashton.wordpress.com
Join Tables on Date Ranges | An Oracle Programmer
https://stewashton.wordpress.com/2015/03/21/join-tables-on-date-ranges
SQL and PL/SQL techniques and solutions. About this blog and test environment. Join Tables on Date Ranges. A recent question on the OTN SQL forum. Asked how best to join two tables related by ID and date range, in order to insert one row per date into a data warehouse. One solution was to expand the data from each table, creating one row per date, then join on date. I think it’s more efficient to join on date range, then expand. Here’s how. Input Tables and Requirement. The start date in table 1. If you ...
stewashton.wordpress.com
MATCH_RECOGNIZE: gaps in date ranges | An Oracle Programmer
https://stewashton.wordpress.com/2015/06/14/match_recognize-gaps-in-date-ranges
SQL and PL/SQL techniques and solutions. About this blog and test environment. MATCH RECOGNIZE: gaps in date ranges. To find gaps between date ranges, there is an elegant solution using analytic functions. We can follow the same logic with. Using a technique that avoids the problems with. Comparisons I just wrote about. Here are some simple test data. Please note that I use exclusive end dates: the second to last range ends when March 2d starts, so there should be a gap from March 2d to March 3d. Will ou...
stewashton.wordpress.com
SQL for date ranges, gaps and overlaps | An Oracle Programmer
https://stewashton.wordpress.com/2014/03/11/sql-for-date-ranges-gaps-and-overlaps
SQL and PL/SQL techniques and solutions. About this blog and test environment. SQL for date ranges, gaps and overlaps. Update 2014-07-04] Since this is my most popular post, I’m adding an index to all my posts about date ranges:. This post right here explains terms like “gap, meet, overlap”, shows how to design date ranges in tables and explains what null values should mean. SQL and date ranges: don’t make NULL mean something. Gaps in Date Ranges: when are you free? Merging contiguous date ranges. Is jus...
stewashton.wordpress.com
Merging Overlapping Date Ranges | An Oracle Programmer
https://stewashton.wordpress.com/2015/06/08/merging-overlapping-date-ranges
SQL and PL/SQL techniques and solutions. About this blog and test environment. Merging Overlapping Date Ranges. A recent question on the OTN forum asked about merging date ranges. Thanks to Karthick App. I realized that my previous blog on this subject was incomplete. Here is a solution that will merge any date ranges that meet, or that “overlap” in any way. As a reminder, Allen’s Interval Algebra. Defines 13 possible relations between date ranges. I wrote about the start of group method. And don’t...
stewashton.wordpress.com
MATCH_RECOGNIZE: matching based on aggregates | An Oracle Programmer
https://stewashton.wordpress.com/2015/06/13/match_recognize-matching-based-on-aggregates
SQL and PL/SQL techniques and solutions. About this blog and test environment. MATCH RECOGNIZE: matching based on aggregates. The heart of row pattern matching is finding which row matches what part of the pattern. Within the 12c. Lists the conditions a row may meet; it doesn’t always work the way you expect, especially if you use aggregates in the condition. Clause lists the conditions that have to be met by a series of rows in order to match the pattern. The. Do you see something strange here? When the...
stewashton.wordpress.com
Gaps in Date Ranges: when are you free? | An Oracle Programmer
https://stewashton.wordpress.com/2014/03/15/gaps-in-date-ranges-when-are-you-free
SQL and PL/SQL techniques and solutions. About this blog and test environment. Gaps in Date Ranges: when are you free? Calculating “free time” in Calendars is a very common task. Did you know it can be done by a SQL statement less than 100 bytes long? This post pays homage to an incredibly neat and concise use of analytic functions. Here is his input data:. And his expected output:. The end point is really 2007-02-01 00:00:00; I take it to be exclusive. Any time on that date. So his gap would start the n...
