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.
It only works in version 12 :( That’s right, I didn’t test in previous versions and there was a bug. Please refer to the COMPARE_SYNC post for the new, improved version. Here is what I changed: The name is now COMPARE_SYNC. I don’t know what got into me to put those four meaningless letters in front. The […]
If a column or expression may be NULL, it is a pain to compare it to something else. Why? Because comparisons involving a NULL do not result in TRUE or FALSE: they result in NULL. I use the DECODE function to work around this problem: here's why - and how.
@thatjeffsmith recently recommended an article about making your blog more popular. The article said "lists of 10 things" were great ways to get more readers. Hey, if that's all it takes... STEW_COMPARE_SYNC is a package that generates SQL to compare data or synchronize tables. Here are 10 good reasons to use it.
Oracle Database 12c introduced "invisible columns": they are only visible when you name them explicitly in the
SELECTlist. Unfortunately, they seem to be even more invisible when you access them through a database link! Here are some surprising results from
Oracle 12c introduced "invisible columns" to help us add columns to tables without breaking existing applications. The documentation explains how they work with SELECT and INSERT, but not MERGE. Here's what happened when I tried MERGE.
After many blog posts about comparing and synchronizing tables, I have united all the techniques I presented in one place. The STEW_COMPARE_SYNC package generates SQL for
- Comparing tables, views and queries, both local and remote.
- Synchronizing, or applying changes to target tables from either source tables or "Change Data Capture" input.
I have written a lot about comparing and synchronizing tables. My examples always had both primary keys and non-key columns, so I could do updates along with inserts and deletes. What about the other tables? Here's a technique that works for them.
In my post “Compare and sync tables: Generating the code“, I use the primary key constraint on the target table. A reader called “Bal” asked how to use a unique constraint instead. Test data I’m going to create a target table with a unique constraint, but without any NOT NULL constraints on the columns. UK1 […]