SQL WITH Clause - Unreferenced Query

Why the SQL WITH clause is not exactly like a function definition

The SQL WITH clause is similar in concept to a function definition in procedural code. In a function, we factor the common code, put it all together at one place and call it as many times as needed in the main program. That’s precisely how we use the WITH clause in SQL – factor out […]

Read the Full Article →
Thumbnail image for Write Smarter Queries with the SQL WITH Clause

Write Smarter Queries with the SQL WITH Clause

The WITH clause, also known as the subquery factoring clause, was introduced in Oracle 9i as a way to make queries more efficient and readable. Let’s see how WITH works with a basic example.

Read the Full Article →
ORA-06592: CASE_NOT_FOUND Exception

ORA-06592: CASE_NOT_FOUND Exception

CASE is a smarter rewrite for IF-THEN-ELSE, we said. It is for sure, but there is a difference in the way the ELSE part of it is handled. Compare the code units below, one using CASE WHEN the other using IF-ELSIF. Both are identical in logic – two defined conditions, no ELSE path.

Read the Full Article →
Simple CASE vs. Searched CASE

Simple CASE vs. Searched CASE

The CASE construct in Oracle has two variants – the simple CASE and the searched CASE. We saw examples of both kinds in the topic The Difference Between DECODE and CASE. Let’s have a closer look to compare them in structure and functionality.

Read the Full Article →
Difference Between DECODE and CASE

The Difference Between DECODE and CASE

DECODE and CASE statements in Oracle both provide a conditional construct, of this form: if A = n1 then A1 else if A = n2 then A2 else X Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function. Everything DECODE […]

Read the Full Article →
Selecting ODD or EVEN rows from a table

Selecting ODD or EVEN rows from a table

Question: How can I select only the even/odd rows from an Oracle table? Answer: Talking of “even or odd rows” is meaningless in Oracle until you have ordered the rows. Oracle does not store rows in a specific order – the order has to come from the query. Once the order is specified, then the […]

Read the Full Article →
LEVEL Pseudocolumn in Hierarchical Queries

LEVEL Pseudocolumn in Hierarchical Queries

LEVEL is a pseudocolumn (i.e. not a real column in the database but available in a query), which has a special function in hierarchical queries – it returns the position of any row in the hierarchy. Consider the hierarchy of employees in SCOTT’s EMP table, shown in tree structure like below:

Read the Full Article →
Hierarchical Queries in Oracle

Hierarchical Queries: A QuickStart Guide

A hierarchical query is one that works on data with a "tree" relationship. An Example of Hierarchical Data The employee-manager relationship in SCOTT’s famous EMP table. Each employee record has a manager’s id associated with it. In effect, there is a "tree" of data relationships At each level, I can get the employee-manager relationship as […]

Read the Full Article →
Oracle Interview Tips

7 Surefire Tips to Ace your Interview

Presenting for you the third article in the series on interviews. Earlier in the series: tips to make your resume stand out, and the fallacy of memorizing interview questions. Your resume has made it to the shortlist and you’ve got that precious interview call. You’re excited and nervous and eager to sail through. How can […]

Read the Full Article →
How to Create an Awesome Résumé

How to Create an Awesome Résumé: Eight Golden Rules

We’ve seen the pointlessness of memorizing answers to interview questions, earlier in the series on interviews. In this article, we have for you 8 gleaming tips to give your résumé a facelift and get you that valuable interview call. Your résumé is your sales pitch, your chance to make an impact in the brief time […]

Read the Full Article →