Everything From how to write simple SQL and PL/SQL statements to new features of the language, Creating functions, Stored procedures, complex queries, solutions to real world challenges and much more. In this Category I will post about the Language that has been around for a while and is still going strong Oracle SQL and it’s procedural extension PL/SQL
Everyone has a pattern or writing style when it come to SQL I’ve noticed. Some people use uppercase for keywords other people don’t, some start a column on a new line others don’t, people format differently, leaving commas at the end of the line vs at the beginning and many more niche ways people write. […]
BACKGROUND Consider this post from stackoverflow. As seen above, Nesting of with clauses is not possible in oracle and while the examples in the answer are simple and straightforward allowing you to define multiple different subqueries, sometimes (and I know because I’ve been there) you actually need to or at least wish you would nest
In this post, I explain with examples the new oracle database 26ai feature “Group by Alias and group by All” I also discuss using the Having clause and Rollup as well as what to watch out for.
Group by Alias and Group by All in Oracle Database 26ai Read More »
A quick guide to Oracle 26ai’s extended VALUES constructor, showing how to insert, update, and merge multiple rows with cleaner, simpler SQL.
Table Values Constructor In Oracle Database 26ai Read More »
BACKGROUND Consider this problem from LeetCode: leetcode_db_1158, about figuring out for each user, the number of orders they made in 2019. The code below can create the sample data set needed for the question. Here’s an Oracle SQL solution to this problem Notice the left join to a subquery highlighted above! This is done to
Conditional Filter For Aggregates In Oracle Database 26ai Read More »
Background Oracle AI Database 26ai provides new, easier and self documenting syntax for insert statements: Non-positional Inserts. This makes use of the INSERT INTO SET and BY NAME clauses The SET clause works similarly to the existing set clause for update statements, and the by name clause makes it easier to insert the results of
Non-Positional Inserts in Oracle AI Database 26ai Read More »
The Qualify clause in Oracle database 26ai makes it easier to filter on the results of window functions. See an example in this blog post.
Easier filtering of window functions with the Qualify clause in oracle AI Database 26ai Read More »
Background Historically, updating a table based on values from another table in oracle database required developers to rely on correlated subqueries and or merge statements or other workarounds. Oracle ai database 26ai allows tables to be joined directly using an update and/or a delete statement. The result is cleaner SQL, more intuitive data modification queries,
Simpler Update and Delete logic with direct joins in Oracle 26ai Read More »
A BRIEF PRIMER: VIEWS Views are a great feature of the database, they are stored SQL queries that can be reused again and again without the need for rewriting the entire code. If a view has a filter in it however, and maybe you want to see the same resulting columns that the view generates
Creating “views” That accept input using SQL Macros Read More »
Unlock the secrets of Oracle’s hierarchical queries and discover how to tackle complex Bill of Material (BOM) structures! In this insightful blog post, we delve into a real-world challenge: calculating the total reorder lead time for parts needed to build a car or bike. With a blend of SQL and PL/SQL, we explore the intricacies of parent-child relationships in data, revealing how to efficiently navigate through layers of components. Join us on this journey to master hierarchical queries and enhance your database skills—your BOM management will never be the same!
Solving Complex BOM Structures with Oracle Hierarchical Queries Read More »
