A Common Table Expression, or CTE is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. CTEs can be thought of as alternatives to derived tables (subquery), views, and inline user-defined functions.
Common table expressions are supported by Teradata, DB2, Firebird, Microsoft SQL Server, Oracle (with recursion since 11g release 2), PostgreSQL (since 8.4), Oracle calls CTEs “subquery factoring.
Below We have a example of a CTE;
WITH MY_TABLE AS ( SELECT USERNAME, OSUSER, PROGRAM FROM V$SESSION WHERE STATUS ='ACTIVE' ) SELECT A.USERNAME FROM MY_TABLE A /
HTH – Antonio NAVARRO