Display Same Record Multiple time from SQL Query

Avinash Jaiswal
July 4, 2014
0

I would like to share a interview question where you have to display the same records multiple times using SQL query. This is very easy job with the help of PL/SQL block but here we will see, how we can do this using SQL query.

First check the sample data and desired output.

[sql]CREATE TABLE TMP (
Text VARCHAR2(10) ,
min NUMBER ,
max NUMBER) ;

INSERT INTO tmp VALUES (‘AAA’, 2,4) ;
INSERT INTO tmp VALUES (‘BBB’, 25,28) ;
INSERT INTO tmp VALUES (‘CCC’, 10,13) ;[/sql]

Now come to solution , if somehow we get the series of number (like 1,2,3,4… Max) as a data set and then we can join this with original table to get desired output.
We can get this data set of number(like 1,2,3…) with the help of “Connect by Level” .
Here LEVEL is a pseudo column which returns 1 for a root row, 2 for a child of a root, and so on. CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. We use all these in hierarchical query. Will explain hierarchical query in detail in different article but not here.
First check below example to print number 1 to 10 with help of CONNECT BY LEVEL. This is the common question which is asked during interview to print number 1 to 10 from dual.

[sql]SELECT LEVEL FROM dual CONNECT BY LEVEL <= 10 ;[/sql]

Above query return number 1 to 10.
We will use this method to solve given problem.

Method 1 :

[sql]SELECT tmp.text || ‘   Value   is   ‘ || b.L FROM tmp,
       (SELECT LEVEL L FROM dual
        CONNECT BY level <= (SELECT max (max) FROM tmp )
        ) b
WHERE   b.L >= min
AND     b.L <= max
ORDER BY 1[/sql]
In above query, line no 2, 3 will gives number series 1 to 28. and I am joining this with original table.

Method 2: This is same query as above ,just I am writing in different way.

[sql]WITH  cnt AS (
        SELECT LEVEL L FROM    dual
        CONNECT BY level <= (SELECT max (max) FROM tmp)
)
SELECT tmp.text || ‘   Value   is   ‘ || cnt.L
FROM    tmp , cnt
WHERE   tmp.min <= cnt.L
AND     tmp.max >= cnt.L
ORDER BY 1[/sql]
Method 3:
[sql]SELECT text || ‘ Value is ‘ || L
FROM (
SELECT distinct tmp.text, level L, tmp.min
from tmp
connect by level <= tmp.max
)
WHERE L >= min
order by text , L?[/sql]

Friends, please share your knowledge as well, if you know any other method.

Follow me at : Facebook

DECODE vs CASE in Oracle SQL

Avinash Jaiswal
June 14, 2014
0

DECODE and CASE both provides IF-THEN-ELSE functionality in Oracle SQL. Decode Function and Case Statement is used to transform data values at retrieval time. Before Oracle 8.1 version, only DECODE function was there for providing IF-THEN-ELSE functionality and this can compare only discrete values (Not in range). In Oracle version 8.1.6, Oracle introduced the CASE Statement, which allowed the use of operators like <,> and BETWEEN, IN etc.

Everything DECODE can do, CASE can. There is a lot else CASE can do though, which DECODE cannot. We�ll go through detailed examples in this article.

DECODE Function:

It is Extension to ANSI SQL.
It is like IF…THEN. . ELSE function.
It compares VALUE to IF value , one by one.
If equal match found, return corresponding THEN value.
If match is not found, the ELSE value is return, if coded.
If ELSE is not coded and a match is not found, NULL is returned.
VALUE, IF , THEN and ELSE can be expression (e.g. : SYSDATE – BIRTHDATE ) or function ( e.g. SIGN(salary � avg_salary) ).
In DECODE, Oracle consider two NULL to be equivalent.
Maximum number of item, including VALUE, IF, THEN and ELSE is 255.

Syntax :

[sql]DECODE( VALUE, IF_1 , THEN_1
               [,IF_2 ,THEN_2]
   . . .
   [,IF_N ,THEN_N]
   [,ELSE]
)[/sql]Above syntax can be understand like below:[sql]DECODE( VALUE, search_1 ,result_1
[,search_2 ,result_2]
. . .
[,search_N ,result_N]
[,default]
)[/sql]Example: [sql]SELECT supplier_name,
DECODE(supplier_id, 10000, ‘IBM’,
                    10001, ‘Microsoft’,
                    10002, ‘Hewlett Packard’,
                    ‘Gateway’) result
FROM suppliers;[/sql] Above DECODE statement is equivalent to the following IF-THEN-ELSE statement:
[sql]IF supplier_id = 10000 THEN
   result := ‘IBM’;
ELSIF supplier_id = 10001 THEN
result := ‘Microsoft’;
ELSIF supplier_id = 10002 THEN
result := ‘Hewlett Packard’;
ELSE
result := ‘Gateway’;
END IF;[/sql]

CASE Statement:

The CASE expression was first added to SQL in Oracle8i. Oracle9i extends its support to PL/SQL to allow CASE to be used as an expression or statement. The CASE expression is a more flexible version of the DECODE function. Oracle support two flavors of CASE, simple and searched.

A) Simple CASE statement:[sql]CASE expression WHEN this1 THEN that1
               WHEN this2 THEN that2 . . .
               [ ELSE that]
END[/sql] Simple case expression use for an equal condition on the given value or expression.The first WHEN value which is match with given value, return corresponding THEN value. If none of the WHEN value match with given value/expression , the ELSE values is returned. If the ELSE is not coded, NULL is returned.
Example: [sql]SELECT ename, empno,
(CASE deptno
     WHEN 10 THEN ‘Accounting’
     WHEN 20 THEN ‘Research’
     WHEN 30 THEN ‘Sales’
     WHEN 40 THEN ‘Operations’
     ELSE ‘Unknown’
   END) department
FROM emp
ORDER BY ename;[/sql]?
B) Searched CASE statement: [sql]CASE
   WHEN condition_1 THEN return_value1
   WHEN condition_2 THEN return_value2 . . .
       [ ELSE return_value]
END[/sql] The searched CASE allow multiple comparison expression (<, > , <=, >=, BETWEEN, LIKE, IN, IS NULL, etc.). The first TRUE expression cause oracle to return the corresponding THEN value. If none of the WHEN values match the given expression, the ELSE value is returned. If the ELSE is not coded, NULL is returned.
Example:[sql]SELECT
   CASE WHEN salary BETWEEN 6000 and 8000 THEN ‘6K-8K’
   WHEN salary IN (9000,10000) THEN ‘9K-10K’
   WHEN EXISTS (SELECT NULL FROM avg_sal WHERE avg_sal = salary) THEN ‘EXISTS’
   WHEN TO_CHAR(salary) LIKE ‘3%’ THEN ‘Like 3’
   WHEN SALARY IS NULL THEN ‘Null’
   WHEN EMP_NO IN (SELECT mgr_no FROM department) THEN ‘Dept_Mgr’
   ELSE ‘Unknown’
   END Salry_Range
FROM employee, avg_sal;[/sql]
Note: CASE is limited to 128 WHEN/THEN pairs(255 total values). this limitation can be overcome by nesting cASE within CASE.

Difference Between DECODE and CASE:

1) CASE can work with logical operators other than �=� :

   DECODE performs an equality check only. CASE is capable of other logical comparisons such as < ,> ,BETWEEN , LIKE etc.[sql]SELECT ename, empno,
DECODE( deptno ,10 ,’Accounting’
       ,20 ,’Research’
     ,30 ,’Sales’
       ,40 ,’Operations’
              ,’Unknown’
) department
FROM emp
ORDER BY ename;[/sql][sql]SELECT ename, empno,
(CASE
WHEN sal < 1000 THEN ‘Low’
WHEN sal BETWEEN 1000 AND 3000 THEN ‘Medium’
WHEN sal > 3000 THEN ‘High’
ELSE ‘N/A’
END) salary
FROM emp
ORDER BY ename;[/sql]

2) CASE can work with predicates and searchable subqueries:

   DECODE works with expressions that are scalar values only. CASE can work with predicates and subqueries in searchable form. [sql]SELECT CASE
       — predicate with “IN”
       WHEN salary IN (9000,10000) THEN ‘9K-10K’
       —-searchable subquery
       WHEN EMP_NO IN (SELECT mgr_no FROM department) THEN ‘Dept_Mgr’
       ELSE ‘Unknown’
       END category
   FROM employee ;[/sql]
3) CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.

DECODE can work as a function inside SQL only. CASE can be an efficient substitute for IF-THEN-ELSE in PL/SQL. In below example, if you replace CASE with DECODE then it gives error.

[sql]DECLARE
  deptno   NUMBER := 20;
  dept_desc  VARCHAR2(20);
BEGIN
  dept_desc := CASE deptno
WHEN 10 THEN ‘Accounting’
WHEN 20 THEN ‘Research’
WHEN 30 THEN ‘Sales’
WHEN 40 THEN ‘Operations’
ELSE ‘Unknown’
END;
DBMS_OUTPUT.PUT_LINE(dept_desc);
END;[/sql]
4) CASE can be used as parameter of a function/procedure.

   CASE can even work as a parameter to a procedure call, while DECODE cannot.
           [sql]exec proc_test(case :a when ‘THREE’ then 3 else 0 end);[/sql]
   Above statement will not give error but below statement gives error.
           [sql]exec proc_test(decode(:a,’THREE’,3,0));[/sql]
5) CASE expects datatype consistency, DECODE does not.
       [sql]SELECT DECODE(200,100,100
                   ,’200′,’200′
                   ,’300′) TEST
FROM dual;
——–Output:

TEST
—-
200[/sql][sql]SELECT CASE 200 WHEN 100 THEN 100
              WHEN ‘200’ THEN ‘200’
              ELSE ‘300’
               END TEST
FROM dual;
————
Error on line 2 at position 14 WHEN ‘200’ THEN ‘200’
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
[/sql]
6) CASE handles NULL differently :
[sql]SELECT DECODE(NULL,NULL,’This is Null’
                ,’This is Not Null’) TEST
FROM dual;
————–Output:
TEST
—-
This is Null
[/sql][sql]SELECT CASE NULL WHEN NULL THEN ‘This is Null’
          ELSE ‘This is Not Null’
               END TEST
FROM dual;
————–Output:
TEST
—-
This is Not Null
[/sql][sql]SELECT CASE WHEN NULL is NULL THEN ‘This is Null’
       ELSE ‘This is Not Null’
          END TEST
FROM dual;
———-Output:
TEST
—-
This is Null[/sql]
7) CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
8) CASE executes faster in the Optimizer than does DECODE.
9) CASE is a statement while DECODE is a function.

Follow me at : Facebook

Convert Rows into Columns or Transpose Rows to Columns In Oracle SQL

Avinash Jaiswal
June 6, 2014
1

SQL Database Language Hello Friends,
Have you ever been in a situation where you as a developer knows that your data is stored in your table rows, but you would like to present the data as a column ??

There are many way to handle this situation. Here I am sharing few of them which I know.?

Below is the sample table and data :
————————————————————
[sql]CREATE TABLE yr_table (
Roll_No NUMBER,
Subject VARCHAR2(20),
Marks NUMBER
);
[/sql]
[sql]insert into yr_table VALUES (1212324,’MTH’,90);
insert into yr_table VALUES (1212324,’PHY’,72);
insert into yr_table VALUES (1212324,’CHE’,85);
insert into yr_table VALUES (1212324,’BIO’,78);
insert into yr_table VALUES (1212334,’MTH’,85);
insert into yr_table VALUES (1212334,’PHY’,65);
insert into yr_table VALUES (1212334,’CHE’,74);
insert into yr_table VALUES (1212672,’MTH’,88);
insert into yr_table VALUES (1212672,’PHY’,42);
insert into yr_table VALUES (1212672,’BIO’,12);
COMMIT;
[/sql]
——————————————————————-

Now I would like to represent above data into below format :

We can achieve above output in different way.

# Method 1 -> Using DECODE function

[sql]SELECT ROLL_NO,
MAX( DECODE( SUBJECT , ‘MTH’ , MARKS ) ) AS MTH ,
MAX( DECODE( SUBJECT , ‘PHY’ , MARKS ) ) AS PHY ,
MAX( DECODE( SUBJECT , ‘CHE’ , MARKS ) ) AS CHE ,
MAX( DECODE( SUBJECT , ‘BIO’ , MARKS ) ) AS BIO
FROM yr_table
GROUP BY ROLL_NO ORDER BY 1 ;?
[/sql]
To understand the above query, first see the below output of above query without using MAX function then you can understand how above query works.

# Method 2 -> Using CASE? statement
[sql]SELECT ROLL_NO,
MAX( CASE WHEN SUBJECT = ‘MTH’ THEN MARKS END) MTH ,
MAX( CASE WHEN SUBJECT = ‘PHY’ THEN MARKS END) PHY ,
MAX( CASE WHEN SUBJECT = ‘CHE’ THEN MARKS END) CHE ,
MAX( CASE WHEN SUBJECT = ‘BIO’ THEN MARKS END) BIO
FROM yr_table
GROUP BY ROLL_NO ORDER BY 1 ;?
[/sql]
Here CASE statement works same as DECODE function.

# Method 3 -> Using PIVOT Operator?

The PIVOT and the UNPIVOT operators were introduced in Oracle version 11g. The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. The following query will give the same result as the query above, just by using the PIVOT operator.
[sql]SELECT * FROM yr_table
PIVOT (
MAX ( MARKS ) FOR (SUBJECT) IN (‘MTH’ AS MTH, ‘PHY’ AS PHY, ‘CHE’ AS CHE, ‘BIO’ AS BIO)
)
ORDER BY 1??
[/sql]
You can check below link for more clarification on PIVOT Operator. http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html

# Method 4 -> Using WITH clause and PIVOT Operator? ?
[sql]WITH TMP AS (
SELECT ROLL_NO,SUBJECT , MARKS FROM yr_table
)
SELECT * FROM TMP
PIVOT (
MAX(MARKS) FOR (SUBJECT) IN (‘MTH’ AS MTH, ‘PHY’ AS PHY, ‘CHE’ AS CHE, ‘BIO’ AS BIO)
)
ORDER BY 1
[/sql]

The WITH clause, was added into the Oracle SQL syntax in Oracle 9.2 . The WITH clause can be used to reduce repetition and simplify complex SQL statements. Here don’t get confuse with WITH Clause; just think that it create a temporary table which we can use it in select statement.

# Method 5 -> Using WITH clause and Sub-query
[sql]WITH TMP AS (
SELECT ROLL_NO,SUBJECT , MARKS FROM yr_table
)
SELECT Y.ROLL_NO ,
( SELECT TMP.MARKS FROM TMP WHERE TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = ‘MTH’) AS MTH ,
( SELECT TMP.MARKS FROM TMP WHERE TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = ‘PHY’) AS PHY ,
( SELECT TMP.MARKS FROM TMP WHERE TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = ‘CHE’) AS CHE ,
( SELECT TMP.MARKS FROM TMP WHERE TMP.ROLL_NO = Y.ROLL_NO AND TMP.SUBJECT = ‘BIO’) AS BIO
FROM (SELECT DISTINCT ROLL_NO FROM yr_table ) Y
ORDER BY 1 ;
[/sql]
# Method 6 -> Using Multiple Joins

[sql]SELECT DISTINCT
A.ROLL_NO ,
B.MARKS AS MTH ,
C.MARKS AS PHY ,
D.MARKS AS CHE ,
E.MARKS AS BIO
FROM yr_table A
LEFT JOIN yr_table B
   ON A.ROLL_NO = B.ROLL_NO AND B.SUBJECT = ‘MTH’
LEFT JOIN yr_table C
   ON A.ROLL_NO = C.ROLL_NO AND C.SUBJECT = ‘PHY’
LEFT JOIN yr_table D
   ON A.ROLL_NO = D.ROLL_NO AND D.SUBJECT = ‘CHE’
LEFT JOIN yr_table E
   ON A.ROLL_NO = E.ROLL_NO AND E.SUBJECT = ‘BIO’
ORDER BY 1;
[/sql]

Friends, please share your knowledge as well, if you know any other method.