BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation








EnterpriseDB: In-Line View

Oracle Tips by Burleson Consulting

Instead of just listing columns, you can also select functions and expressions.  I will describe all of the SQL functions below.  In this query I am calling a function named lower that will select all of the enames in lower case text.  In the same query, I am selecting the empno column and am adding 10000 to each (this would be called an expression).

SELECT lower(ename) employee_name,
       empno + 10000
  FROM emp;

employee_name  | ?column?
 smith         |    17369
 allen         |    17499
 ward          |    17521
 jones         |    17566
 martin        |    17654
 blake         |    17698
 clark         |    17782
 scott         |    17788
 king          |    17839
 turner        |    17844
 adams         |    17876
 james         |    17900
 ford          |    17902
 miller        |    17934
 lewis         |    18000

(15 rows)

Notice that the second column, where we added 10000 to the empno is listed as ?column?.  That is because we did not use an alias.  When you call a function or use an expression in a select statement, you should always give it an alias to avoid this issue.

Your FROM may also be a dynamic table called an inline view.  An in-line view can be made up data such as:

   SELECT 'abc' col1 FROM DUAL ) AS foo; 

edb=# SELECT foo.* FROM (
edb(#    SELECT 'abc' col1 FROM DUAL ) AS foo;

(1 row)


You may also use an in-line view to select from a pre-selected and pre-sorted data set:

  SELECT ename
    FROM emp
    WHERE ename like 'A%'
    ORDER BY ename DESC) AS foo;    

edb=# SELECT foo.* FROM (
edb(#   SELECT ename
edb(#     FROM emp
edb(#     WHERE ename like 'A%'
edb(#     ORDER BY ename DESC) AS foo;


(2 rows)


A sub-query may also be called directly from the SELECT clause of the statement.  In this case, the sub-query is called a scalar sub-query.    A scalar sub-query must always return exactly 1 column and exactly 1 row.

SELECT ename, (SELECT dname
                 FROM dept
                 WHERE emp.deptno = dept.deptno) AS dept_name
  FROM emp;

edb=# SELECT ename, (SELECT dname
edb(#                  FROM dept
edb(#                  WHERE emp.deptno = dept.deptno) AS dept_name
edb-#   FROM emp;
 ename  | dept_name

 Lewis  |

(17 rows)


In this case, a JOIN (discussed below) would be a better solution than a scalar sub-query but it does make the point.

When using sub-queries, you should be aware of the impacts.  We can call a sequence's nextval in a query.  What would we get if we called a sequence's nextval in a scalar sub-query that returned multiple rows?

SELECT ename, (SELECT seq1.nextval
                 FROM DUAL) as seq_no
  FROM emp; 

edb=# SELECT ename, (SELECT seq1.nextval
edb(#                  FROM DUAL) as seq_no
edb-#   FROM emp;
 ename  | seq_no
 SMITH  |     25
 ALLEN  |     25

WARD   |     25
 JONES  |     25
 MARTIN |     25
 BLAKE  |     25
 CLARK  |     25
 SCOTT  |     25

 KING   |     25
 TURNER |     25
 ADAMS  |     25
 JAMES  |     25
 FORD   |     25
 MILLER |     25
 Lewis  |     25

  |     25
        |     25

(17 rows)


Was this the result you expected?  It seems counter intuitive to me.  I wanted to make the point that you should always test that what you think is going to happen, really is going to happen.

What we have learned so far is that the SELECT command begins with the SELECT keyword.  That keyword is followed by a column list (or expression list).  The column list is followed by the FROM keyword which lists your table, tables or in-line views.

A sub-query is a query referenced in an outer query.  An in-line view is a sub-query in the FROM clause.  A scalar sub-query is a query that returns exactly 1 column and exactly 1 row.  A scalar sub-query can be in the SELECT, FROM, WHERE, ORDER BY and GROUP BY clauses.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.



Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software







BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.

Hit Counter