DPSQLStudio — Hierarchy Explorer & Compare Datasets
Two ways to make sense of a result set
Some questions aren’t about which rows but about shape and change. DPSQLStudio’s Data menu has a tool for each: Hierarchy Explorer turns a parent/child query into an expandable tree, and Compare Datasets diffs two snapshots and tells you exactly what moved. This post shows both on a real EMPLOYEES table — every screenshot is the live app (v0.1.0 Beta) on Oracle as ADMIN@dpsqltest_high.
1. Hierarchy Explorer — an org chart from a flat table
The EMPLOYEES table is flat: each row has an EMP_ID and a MANAGER_ID. Data ▸ Hierarchy Explorer reads that self-reference and rebuilds the tree. You point it at a parent column and a child column, pick a label, and a direction (top-down).
Run it and the grid gains LEVEL and PATH columns, with names indented to show depth — a proper org chart you can read top to bottom:
The PATH column (e.g. /1/2/4/7) is the materialised path from the root — handy for sorting the tree or spotting how deep a branch runs. This is the same idea as Oracle’s CONNECT BY, but built from any result with two clicks.
2. Compare Datasets — what changed between two snapshots
Now a different question: how did salaries change from one year to the next? Save two sheets — EMP_SALARY_2023 and EMP_SALARY_2024 — then Data ▸ Compare Datasets. You choose the two datasets, the key to match on, and which value column to diff.
The result lines them up by EMP_ID and computes the difference, percent variance, and a total — including a row that exists in A but not B:
| EMP_ID | SALARY (A) | SALARY (B) | DIFF | %VAR |
|---|---|---|---|---|
| 101 | 120,000 | 132,000 | +12,000 | 10% |
| 103 | 95,000 | 99,000 | +4,000 | 4.21% |
| 106 | 85,000 | 90,000 | +5,000 | 5.88% |
| 107 | 72,000 | 78,000 | +6,000 | 8.33% |
| 108 | 65,000 | (no match) | −65,000 | −100% |
| TOTAL | 725,000 | 691,000 | −34,000 |
Why this beats eyeballing two grids: Compare classifies every key as matched, A-only, or B-only and quantifies the change, so a departure (108) and a raise (101) are both impossible to miss.
Coming up
Next: the BLOB image viewer and Chart tools — viewing images stored in the database and turning a result column into a bar chart.
DPSQLStudio v0.1.0 (Beta) — a free Oracle SQL client for Windows. Screenshots are from the live app; demo data is fictional.
Comments (0)