Mimic LIKE ANY in Oracle SQL

Last year I wrote a small post on a unique feature of Teradata SQL: LIKE ANY operator. You can read it here. Recently I realized that we can mimic this functionality in Oracle using Regular Expressions.

For instance, if we need to find all employee whose names contain ‘AR’ or ‘AM’, we can do it in a traditional Oracle way:

SELECT ename
FROM emp
WHERE ename LIKE '%AR%' OR ename LIKE '%AM%'

Result:

ENAME
---------
WARD
MARTIN
CLARK
ADAMS
JAMES

In Teradata, we would write it as following:

 
SELECT ename
FROM emp
WHERE ename LIKE ANY ('%AR%', '%AM%')

In Oracle we can use REGEXP_LIKE function:

SELECT ename
FROM emp
WHERE REGEXP_LIKE(ename, 'AR|AM')

Note, that in regular expression pattern we don’t use the wild card character ‘%’.

If we needed to see all employees whose name start with A or B, we would use a slightly different matching pattern:

SELECT ename
FROM emp
WHERE REGEXP_LIKE(ename, '^A|^B')

Result:

ENAME
--------
ALLEN
BLAKE
ADAMS

For names ending on ‘N’ or ‘S’:

SELECT ename
FROM emp
WHERE REGEXP_LIKE(ename, 'N$|S$')

Result:

ENAME
--------
ALLEN
JONES
MARTIN
ADAMS
JAMES

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

LIKE with ANY in Teradata SQL

Teradata happened to support a very convenient SQL feature that Oracle does not have:

Teradata:

SELECT *
FROM emp
WHERE ename LIKE ANY ('%A%', '%B%');

--Works perfectly fine

Oracle:

SELECT *
FROM emp
WHERE ename LIKE ANY ('%A%', '%B%');

WHERE ename LIKE ANY ('%A%', '%B%')
                 *
ERROR at line 3:
ORA-00936: missing expression

Oracle does not seem to support any combination of LIKE and ANY:

SELECT *
FROM emp
WHERE ename LIKE ANY (SELECT '%A%' FROM dual UNION ALL
                      SELECT '%B%' FROM dual);

WHERE ename LIKE ANY (SELECT '%A%' FROM dual UNION ALL
                 *
ERROR at line 3:
ORA-00936: missing expression

Drop Index: Teradata vs Oracle Syntax

Teradata

Syntax:

DROP INDEX index_name ON tablename;
--or
DROP INDEX(column1, column2, ..., columnN) ON tablename;

Example 1:

Dropping index named idx_dept from employee.

DROP INDEX idx_dept ON employee;

Example 2:

Dropping an index that is not named. The following drops the index on (dept_number, emp_number) columns in the employee table:

DROP INDEX (dept_number,emp_number) ON employee;

Oracle

Syntax:

DROP INDEX idx_dept;

 

 

How to get Session ID in Teradata vs Oracle

Getting Session ID in Teradata is trivial:

SELECT SESSION

In Oracle, you would need to use sys_context (Oracle 10g and up)  or userenv function (legacy function) if you are not permitted to reference v$mystat or some other dynamic performance views:

SELECT SYS_CONTEXT('userenv','sid') sid
FROM dual;

SELECT USERENV('sid') sid
FROM dual;

How to Copy a Table: Teradata vs Oracle syntax

In Oracle, you copy a table with the following CREATE TABLE statement:

CREATE TABLE <table_copy> AS 
SELECT * FROM <table>

And if you only want to copy the structure (with no content) you add WHERE clause that filters out all rows:

CREATE TABLE <table_copy> AS 
SELECT * FROM <table>
WHERE 1=2

In Teradata, you have two ways to do the same task:

CREATE TABLE <table_copy> AS (
SELECT * FROM <table>
) WITH [NO] DATA

and a shorter way:

CREATE TABLE <table_copy> AS <table> WITH [NO] DATA

In both cases, WITH DATA (or WITH NO DATA) clause is required.

 

 

Solutions to Puzzle of the Week #6

Puzzle of the Week #6

Find all employees who is paid one of top 4 salaries in the entire company without using sub-queries and in-line views/WITH clause.

Expected Result:

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850

 

Method/Workaround #1: Use Aggregation over Cartesian Product

This method works in all RDBMS systems, such as Oracle, SQL Server, Sybase, Teradata, etc.

SELECT a.ename, a.sal
FROM emp a, emp b
WHERE a.sal<=b.sal
GROUP BY a.ename, a.sal
HAVING COUNT(DISTINCT b.sal)<=4
ORDER BY 2 DESC;

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850

This method is described in details in my book Oracle SQL Tricks and Workarounds.

Method/Workaround #2: Use DENSE_RANK and MINUS

This method is more Oracle specific, but it is as good as the 1st one. It was suggested by one of the contest participants:

SELECT ename, 
       CASE WHEN DENSE_RANK()OVER(ORDER BY sal DESC)<=4 THEN sal END sal
FROM emp
MINUS
SELECT ename, NULL
FROM emp
ORDER BY 2 DESC;

ENAME             SAL
---------- ----------
KING             5000
FORD             3000
SCOTT            3000
JONES            2975
BLAKE            2850

This is a very elegant variation of the following query with MINUS substituting the filter for analitic function result:

SELECT ename, sal
FROM (SELECT ename, sal, DENSE_RANK()OVER(ORDER BY sal DESC) rk
      FROM emp)
WHERE rk<=4;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

Interestingly enough, Teradata SQL has a special clause QUALIFY for that matter. It is very elegant and maybe one day it will become an ANSII standard:

SELECT ename, sal
FROM emp
QUALIFY DENSE_RANK()OVER(ORDER BY sal DESC)<=4

Finally, I would like to share a couple of more traditional approaches that DO USE subqueries:

SELECT ename, sal
FROM emp a
WHERE 4>=(SELECT COUNT(DISTINCT b.sal)
          FROM emp b
          WHERE b.sal>=a.sal)
ORDER BY sal DESC;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

SELECT ename, sal
FROM emp a
WHERE 4>(SELECT COUNT(DISTINCT b.sal)
          FROM emp b
          WHERE b.sal>a.sal)
ORDER BY sal DESC;

ENAME             SAL
---------- ----------
KING             5000
SCOTT            3000
FORD             3000
JONES            2975
BLAKE            2850

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

Simulate LEAD and LAG functions using other analytic functions

Puzzle: Simulate LEAD and LAG functions using other analytic functions

Level: Intermediate

Solution:

Let’s say that we need to see every employee’s name and 2 more employees who were hired right before. The expected result may look like this:

ENAME      HIREDATE  EMPL1      EMPL2
---------- --------- ---------- ---------
SMITH      17-DEC-80
ALLEN      20-FEB-81 SMITH
WARD       22-FEB-81 ALLEN      SMITH
JONES      02-APR-81 WARD       ALLEN
BLAKE      01-MAY-81 JONES      WARD
CLARK      09-JUN-81 BLAKE      JONES
TURNER     08-SEP-81 CLARK      BLAKE
MARTIN     28-SEP-81 TURNER     CLARK
KING       17-NOV-81 MARTIN     TURNER
JAMES      03-DEC-81 KING       MARTIN
FORD       03-DEC-81 JAMES      KING
MILLER     23-JAN-82 FORD       JAMES
SCOTT      19-APR-87 MILLER     FORD
ADAMS      23-MAY-87 SCOTT      MILLER

It is a no-brainer task if we employ LAG function:

SELECT ename, hiredate, 
              LAG(ename,1) OVER(ORDER BY hiredate) empl1, 
              LAG(ename,2) OVER(ORDER BY hiredate) empl2
FROM emp;

One of the possible approaches is to use ROWS window attribute with MIN/MAX analytic functions:

SELECT ename, hiredate, 
       MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) empl1,
       MAX(ename) OVER(ORDER BY hiredate ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) empl2
FROM emp;

Common rule is:

LAG(value_expr,offset,default) OVER ([partition_clause] order_by_clause )

is the same as

NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause  
   ROWS BETWEEN offset PRECEDING AND offset PRECEDING), default)

and

LEAD(value_expr,offset,default) OVER ([partition_clause] order_by_clause )

is the same as

NVL(MIN(value_expr)OVER ([partition_clause] order_by_clause  
  ROWS BETWEEN offset FOLLOWING AND offset FOLLOWING), default)

This substitution becomes essential in other RDBMS where MIN/MAX analytic functions are supported while LEAD/LAG are not. Teradata is one of the examples.

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to Mimic Oracle’s SYSDATE function in Teradata

There is no direct counterpart of Oracle’s SYSDATE function in Teradata. There is CURRENT_DATE and DATE(both return date only), CURRENT_TIME (time only), CURRENT_TIMESTAMP (similar to SYSTIMESTAMP in Oracle), but in Order to mimic SYSDATE you need to concatenate results of two functions:

SELECT CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0))

or

SELECT CAST(CAST(CURRENT_DATE AS FORMAT 'YYYY-MM-DD') || ' ' || CAST(CAST(CURRENT_TIME AS FORMAT 'HH:MI:SS') AS CHAR(8)) AS TIMESTAMP(0))

The following function will encapsulate the above expression and give you a feeling of using Oracle’s SYSDATE function:

REPLACE FUNCTION SYSDATE()
   RETURNS TIMESTAMP
   LANGUAGE SQL
   CONTAINS SQL
   DETERMINISTIC
   SQL SECURITY DEFINER
   COLLATION INVOKER
   INLINE TYPE 1
   RETURN CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0));

Now, you can use it as follows:

SELECT SYSDATE()

Result:
03/24/2016 12:25:23

Add multiple columns to a table. Teradata vs Oracle syntax.

Today, I am starting a new topic in my blog – Teradata tips and Tricks for Oracle developers.

It is not very hard for a seasoned Oracle professional to learn Teradata SQL. At the same time, there are syntax nuances that need to be remembered. This is the first post that should help Oracle developers to be productive with Teradata SQL.

Oracle syntax:

ALTER TABLE [table name] ADD 
(
   column1 datatype1 ...,
   column2 datatype2 ...,
   ...
   columnN datatypeN ...
)

Teradata syntax:

ALTER TABLE [table name] 
ADD column1 datatype1 ...,
ADD column2 datatype2 ...,
   ...
ADD columnN datatypeN ...

Notice, that Teradata SQL syntax does not use parenthesis and requires “ADD” for every column being added.