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.
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.
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 simple CASE and searched CASE in structure and functionality.
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 can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article.
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 query to retrieve odd rows or even rows can be written in this form:
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:
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 below:
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 you convert the interview call into an offer for employment?
To make a success of the interview, you have to start hitting the right notes from before the interview. Here are a few interview tips for you.
How to Create an Awesome Resume: 8 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 resume tips to help you get you that valuable interview call.
Your resume is your sales pitch, your chance to make an impact in the brief time the employer scans it.
Organizations are flooded with resumes far in excess of the openings; it is essential for yours to get noticed and interest a prospective employer enough to call you for an interview.
Here are eight “Golden Rules” for creating a resume that’s truly outstanding.
The Fallacy of Interview Questions
A series of posts about Oracle interviews starts with this article.
“Interview Questions” is a hot topic on many Oracle forums. Job aspirants want these questions to be listed down, emailed to them, complete with model answers. Some imagine that by learning answers to such questions, they will land a job, though they have not really worked at finding the meaning or rationale behind the answers.
I am utterly bemused.