Oracle - SELECT

oracle

sys.odciNumberList
SYS_CONNECT_BY_PATH

https://docs.oracle.com/database/121/SQLRF/toc.htm
http://www.oracle-developer.net/display.php?id=412
https://community.oracle.com/thread/1120647
http://stackoverflow.com/questions/8785459/anonymous-table-or-varray-type-in-oracle
http://oracle.developer-works.com/article/4237691/sys.odcinumberlist
https://lalitkumarb.wordpress.com/2014/12/02/split-comma-delimited-string-into-rows-using-oracle-sql/
https://blogs.oracle.com/aramamoo/entry/how_to_split_comma_separated_string_and_pass_to_in_clause_of_select_statement
http://db-oracl.blogspot.com/2009/01/dynamic-in-list-from-string.html
http://www.sqljason.com/2010/05/converting-single-comma-separated-row.html
http://www.oracle-documentation.com/plsql/converting-delimited-string-to-rows-in-sql-query
http://www.experts-exchange.com/Database/Oracle/Q_28186312.html
http://stackoverflow.com/questions/5825356/converting-a-delimited-string-or-column-to-rows-in-oracle-using-a-pre-defined
http://stackoverflow.com/questions/14328621/oracle-10g-splitting-string-into-multiple-rows

Some interesting SQL statements:

SELECT n - ROWNUM + 1 label, n - ROWNUM + 1 VALUE
  FROM (    SELECT TO_CHAR (SYSDATE, 'YYYY') n, ROWNUM
              FROM DUAL
        CONNECT BY LEVEL <= 5)

The above SQL statement produce a list of years for a drop-down box.

What is the purpose of the + operator in the where clause?

select ...
from a,b
where a.id=b.id(+)

This is the Oracle's notation for an OUTER JOIN, because the ANSI-89 format (using a comma in the FROM clause to separate table references) didn't standardize OUTER joins. The query would be re-written in ANSI-92 syntax as:

   SELECT ...
     FROM a
LEFT JOIN b ON b.id = a.id

Oracle recommends not using the + indicator.

In order to keep the (+) straight in my head (left side vs. right side), I like to think of the (+) as "adding NULL values if no match found". For example, "a.id=b.id(+)" means allow b.id to be NULL if there is no match with a.id.

The (+) operator indicates an outer join. This means that Oracle will still return records from the other side of the join even when there is no match. For example if a and b are emp and dept and you can have employees unassigned to a department then the following statement will return details of all employees whether or not they've been assigned to a department.

select * from emp, dept where emp.dept_id=dept.dept_id(+)

So in short, removing the (+) may make a significance difference but you might not notice for a while depending on your data!

How can we control the sort order for NULL values?

SELECT INCIDENTS_ID, COUNT(*) FROM SRM.EFFECTS GROUP BY INCIDENTS_ID HAVING COUNT(*) > 1 ORDER BY INCIDENTS_ID NULLS LAST;

If you have string of values concatenated by commas, how can you return a result set such that each value is on a separate row?

select distinct column_value from table(sys.odcinumberlist(1,1,2,3,3,4,4,5))

Also look for sys.odcivarchar2list, sys.ODCIDATELIST

If we have a string of values concatenated by commas, how can we return a result set such that each value is on a separate row?

SELECT * FROM TABLE(comma_to_table('word1, word2, word3, word4, word5, word6'))

See https://lalitkumarb.wordpress.com/2014/12/02/split-comma-delimited-string-into-rows-in-oracle/

How can we extract part of a column?

SELECT SUBSTR(columnName,1,INSTR(columnName,'@')-1) FROM tableName WHERE UPPER(columnName) LIKE UPPER('%@%');

How can we find data that are in one table but not in another table?

SELECT *
  FROM QXPLORE.NASH_SSO_CONV
 WHERE    (UPPER (EMAILADDRESS) NOT IN
              (SELECT UPPER (EMAILADDRESS) FROM QXPLORE.NASH_SSO_CONV2))
       OR (EMAILADDRESS IS NULL);

The above SQL statement does not return any record, probably due to some null values. Use:

SELECT *
  FROM QXPLORE.NASH_SSO_CONV
 WHERE    (NVL (UPPER (EMAILADDRESS), '*') NOT IN
              (SELECT NVL (UPPER (EMAILADDRESS), '*')
                 FROM QXPLORE.NASH_SSO_CONV2))
       OR (EMAILADDRESS IS NULL)

How can we use hierarchical query?

    SELECT qgroupsid AS ID,
           (LEVEL - 1) lvl,
           LPAD (' ', 2 * (LEVEL - 1)) || TO_CHAR (NAME) s,
           TO_CHAR (NAME) NAME
      FROM qxplore.qgroups a
     WHERE a.projectsid = 802 AND a.entitiesid = 555001881
START WITH parent_group_id = 0
CONNECT BY PRIOR a.qgroupsid = parent_group_id

We have a table which has a column (parent_group_id) that is self-referencing. This is similar to the employee table. Within a company, everybody is an employee. The CEO is an employee even though he is at the very top level. C-level executives are also employees. Directors are also employees. So, the employee table has this hierarchical nature. In the above query, LEVEL is a pseudo-column indicating the depth of the hierarchy. The first level is the root.

SELECT
   ENAME
FROM
   EMP
WHERE
   LEVEL=2
CONNECT BY
   PRIOR EMPNO = MGR
START WITH
   ENAME = 'JONES';

How can we use the CASE clause?

SELECT CASE
          WHEN TO_DATE (?, 'mm/dd/yyyy') < TRUNC (SYSDATE) THEN 'PAST'
          WHEN TO_DATE (?, 'mm/dd/yyyy') < TRUNC (SYSDATE + 30) THEN 'OK'
          ELSE 'TOO FAR'
       END
          DATE_CHECK
  FROM DUAL

How can we select all columns and a few other function from a table?

SELECT DSSCSMSGINFO.*, TO_CHAR(CREATION_TIME) CTIME FROM DSSCSMSGINFO  WHERE PROJECT_NAME IN (?);

How can we use the GROUP BY ROLLUP feature?

This feature is like the traditional GROUP BY feature, however, with the ROLLUP keyword, Oracle automatically also calculate the grand total. Use this feature when we need to calculate the grand total or subtotals. See http://www.oracletutorial.com/oracle-basics/oracle-rollup/

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License