Select Statement  «Prev 

Query using two Pseudocolumns

Query using the ROWNUM and SYSDATE pseudocolumns
', today is ' || SYSDATE
Query using the ROWNUM and SYSDATE pseudocolumns

  1. The ROWNUM pseudocolumn appears here in the SELECT clause just like any column.
  2. The SYSDATE pseudocolumn is concatenated to a literal in the SELECT clause. The SYSDATE pseudocolumn contains the current date and time. Its default display format is the same as other dates.
  3. The ROWNUM values appear here and are incremented for each row.
  4. The SYSDATE values appear here and are the same for each row returned.
Question: How do I execute a query using two Pseudocolumns in Oracle?
In Oracle, a pseudocolumn is a column-like object that behaves like a regular column in a table, but it is not stored as a part of the table. Pseudocolumns can be used in SQL queries to retrieve data, perform calculations, or generate unique values. Two common pseudocolumns in Oracle are ROWNUM and ROWID.
To execute a query using two pseudocolumns in Oracle, you can follow the steps below:
  1. Identify the pseudocolumns you want to use in your query. In this example, we will use ROWNUM and ROWID.
  2. Write the SELECT statement, specifying the columns you want to retrieve from the table, along with the pseudocolumns.
  3. Include the pseudocolumns in the SELECT clause, either by using them directly or by applying functions or expressions to them.
  4. Use the appropriate clauses (e.g., WHERE, GROUP BY, ORDER BY) to filter, group, or sort the result set based on the pseudocolumn values.

Here's an example of a query using the ROWNUM and ROWID pseudocolumns to retrieve data from the "employees" table:
SELECT ROWNUM AS row_number, ROWID AS row_identifier, first_name, last_name, salary
FROM employees
In this query:
  1. The ROWNUM pseudocolumn is used to assign a unique row number to each row in the result set.
  2. The ROWID pseudocolumn is used to retrieve the unique row identifier for each row in the table.
  3. The WHERE clause filters the result set to include only the first 10 rows, based on the ROWNUM pseudocolumn value.
  4. The ORDER BY clause sorts the result set in descending order of the "salary" column.

Keep in mind that using pseudocolumns can sometimes lead to unexpected results, particularly when combining them with other clauses. Make sure to test your queries thoroughly to ensure they produce the desired outcome.

The CHECK Constraint

Many columns must have values that are within a certain range or that satisfy certain conditions. With a CHECK constraint, you can specify an expression that must always be true for every row in the table. For example, the RATING table stores the valid ratings; to limit the available values beyond the limits enforced by the column definition, you can use a CHECK constraint, as shown in the following listing:

create table RATING_WITH_CHECK
(Rating VARCHAR2(2) CHECK (Rating <= 9),
RatingDescription VARCHAR2(50));

A column-level CHECK constraint can’t refer to values in other rows; it can’t use the pseudocolumns such as SysDate, User, CurrVal, NextVal, Level, and RowNum. You can use the table constraint form (as opposed to the column constraint form) to refer to multiple columns in a CHECK constraint.