Good example/document for Correlated subquery


You can check the following page for Correlated Subqueries

Especially update query

https://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Fsqlp%2Frbafyexsub4.htm

basic syntax

UPDATE TABLE_NAME X
SET FIELD=(SELECT Y.ANYFIELD
FROM ANOTHERTABLE Y
WHERE X.ID= Y.FID)
WHERE X.ID IN (SELECT FID
FROM ANOTHERTABLE)

UPDATE EMPLOYEE X
SET NEWSALARY=( SELECT Y.NEWSALARY
FROM SALARIES Y
WHERE X.EMPID = Y.EMPID)
WHERE X.EMPID IN ( SELECT EMPID
FROM SALARIES)

Advertisements

How to get an Oracle table as a XML Document using XMLType


Here is the table creation script;

CREATE TABLE EMPLOYEE
(
ID               NUMBER,
EMPLOYEENAME     VARCHAR2(20 BYTE),
EMPLOYEESURNAME  VARCHAR2(20 BYTE),
GENDER           VARCHAR2(20 BYTE),
SALARY           VARCHAR2(5 BYTE),
EMPLOYEEID       VARCHAR2(20 BYTE)
)

And lets enter some data inside it;

INSERT INTO EMPLOYEE (
ID, EMPLOYEENAME, EMPLOYEESURNAME,
GENDER, SALARY, EMPLOYEEID)
VALUES ( 1, 'tuna' , 'tore' ,
'M',  '1000', 1)
INSERT INTO EMPLOYEE (
ID, EMPLOYEENAME, EMPLOYEESURNAME,
GENDER, SALARY, EMPLOYEEID)
VALUES ( 2, 'linus' , 'torvalds' ,
'M',  '1000', 2)
INSERT INTO EMPLOYEE (
ID, EMPLOYEENAME, EMPLOYEESURNAME,
GENDER, SALARY, EMPLOYEEID)
VALUES ( 3, 'bill' , 'gates' ,
'M',  '1000', 3)

By using XMLType Oracle build-in function we can convert the whole table and get the data inside as a XML document in the following example;

SELECT
XMLTYPE
(
CURSOR
(
SELECT * FROM EMPLOYEE
)
)
FROM
DUAL;

Here is the result;

<?xml version="1.0"?>
<ROWSET>
<ROW>
<ID>1</ID>
<EMPLOYEENAME>tuna</EMPLOYEENAME>
<EMPLOYEESURNAME>tore</EMPLOYEESURNAME>
<GENDER>M</GENDER>
<SALARY>1000</SALARY>
<EMPLOYEEID>1</EMPLOYEEID>
</ROW>
<ROW>
<ID>2</ID>
<EMPLOYEENAME>linus</EMPLOYEENAME>
<EMPLOYEESURNAME>torvalds</EMPLOYEESURNAME>
<GENDER>M</GENDER>
<SALARY>1000</SALARY>
<EMPLOYEEID>2</EMPLOYEEID>
</ROW>
<ROW>
<ID>3</ID>
<EMPLOYEENAME>bill</EMPLOYEENAME>
<EMPLOYEESURNAME>gates</EMPLOYEESURNAME>
<GENDER>M</GENDER>
<SALARY>1000</SALARY>
<EMPLOYEEID>3</EMPLOYEEID>
</ROW>
</ROWSET>

How to use EXTRACT(XML) function in ORACLE DB


Friends the following udemy course created by me;

      Click here to attend Spring Framework 4.x and certification course with a discount

Spring Framework and Core Spring Certification Udemy course with discount
Spring Framework and Core Spring Certification Udemy course with a discount coupon

Extract(XML) function is used to get a node or nodes inside a XML document stored in Oracle DB. The syntax for EXTRACT function is;

EXTRACT(XML-Document-Column, ‘XPath expression‘)

EXTRACT(XML-Document-Column, ‘XPath expression’, ‘namespace’)

Example usages;

CREATE TABLE LIBRARY
(
ID_COLUMN NUMBER PRIMARY KEY,
XML_DATA_COLUMN XMLType
);

INSERT INTO LIBRARY(ID_COLUMN ,XML_DATA_COLUMN) VALUES
( 1
, XMLType(‘<?xml version=”1.0″ encoding=”UTF-8″?>
<LIBRARY>
    <BOOKS>
        <BOOK isbn=”ABCD7327923″>
            <NAME>Java Programing</NAME>
            <SUBJECT>Java J2EE</SUBJECT>
            <AUTHORS>
                <AUTHOR>Tuna TORE</AUTHOR>
                <AUTHOR>Linus Torvalds</AUTHOR>
                <AUTHOR>James Gosling</AUTHOR>
            </AUTHORS>
        </BOOK>
        <BOOK isbn=”DFGH09093232″>
            <NAME>XPATH for Dummies</NAME>
            <SUBJECT>XPATH development</SUBJECT>
            <AUTHORS>
                <AUTHOR>Linus Torvalds</AUTHOR>
                <AUTHOR>John Hawking</AUTHOR>
            </AUTHORS>
        </BOOK>
        <BOOK isbn=”DSKL2393A”>
            <NAME>J2EE Patterns</NAME>
            <SUBJECT>Design Patterns</SUBJECT>
            <AUTHORS>
                <AUTHOR>Aka Tuna</AUTHOR>
            </AUTHORS>
        </BOOK>
    </BOOKS>
    <DVDS>
        <DVD id=”123456″>
            <NAME>Music DVD</NAME>
            <CONTENT>Music</CONTENT>
            <AUTHORS>
                <AUTHOR>James Gosling</AUTHOR>
                <AUTHOR>Bill Gates</AUTHOR>
            </AUTHORS>
        </DVD>
        <DVD id=”3213324″>
            <NAME>Natural Science</NAME>
            <CONTENT>Science</CONTENT>
            <AUTHORS>
                <AUTHOR>John Green</AUTHOR>
                <AUTHOR>Bill Gates</AUTHOR>
            </AUTHORS>
        </DVD>
        <DVD id=”4353534″>
            <NAME>Rally</NAME>
            <CONTENT>Race</CONTENT>
            <AUTHORS>
                <AUTHOR>Tuna</AUTHOR>
            </AUTHORS>
        </DVD>
    </DVDS>
</LIBRARY>
‘));

–you can get all DVDs in the library with the following query
SELECT ID_COLUMN, EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD‘)
FROM LIBRARY

–result will be

<DVD id=”123456″>
    <NAME>Music DVD</NAME>
    <CONTENT>Music</CONTENT>
    <AUTHORS>
        <AUTHOR>James Gosling</AUTHOR>
        <AUTHOR>Bill Gates</AUTHOR>
    </AUTHORS>
</DVD>
<DVD id=”3213324″>
    <NAME>Natural Science</NAME>
    <CONTENT>Science</CONTENT>
    <AUTHORS>
        <AUTHOR>John Green</AUTHOR>
        <AUTHOR>Bill Gates</AUTHOR>
    </AUTHORS>
</DVD>
<DVD id=”4353534″>
    <NAME>Rally</NAME>
    <CONTENT>Race</CONTENT>
    <AUTHORS>
        <AUTHOR>Tuna</AUTHOR>
    </AUTHORS>
</DVD>

–or you can get a specific DVD with the following query DVD having id –> 4353534
SELECT ID_COLUMN, EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=4353534]‘)
FROM LIBRARY

— the result is
<DVD id=”4353534″>
    <NAME>Rally</NAME>
    <CONTENT>Race</CONTENT>
    <AUTHORS>
        <AUTHOR>Tuna</AUTHOR>
    </AUTHORS>
</DVD>

You can also use the EXTRACTVALUE function for getting only the value inside XML tags
If you want to get value (name) for the DVD having id number –> 3213324

SELECT ID_COLUMN, EXTRACTVALUE(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]/NAME‘)
FROM LIBRARY

— the result is
Natural Science

And if you want to get XML tags for the above operation use EXTRACT instead of using EXTRACTVALUE

SELECT ID_COLUMN, EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]/NAME‘)
FROM LIBRARY

— the result is
<NAME>Natural Science</NAME>

You can also use EXTRACTVALUE in the WHERE clause of a SQL
like the following query

SELECT EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]‘) FROM LIBRARY
WHERE EXTRACTVALUE(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]/NAME‘) = ‘Natural Science’;

— the result is
<DVD id=”3213324″>
    <NAME>Natural Science</NAME>
    <CONTENT>Science</CONTENT>
    <AUTHORS>
        <AUTHOR>John Green</AUTHOR>
        <AUTHOR>Bill Gates</AUTHOR>
    </AUTHORS>
</DVD>


How to test a column’s value numeric or not in Oracle table (ISNUMERIC equivalent for Oracle DB)


Use the following example SQL example for testing a column’s value is numeric or not using Regular Expression in Oracle

WITH TEST_TABLE
AS
(
SELECT * FROM (
SELECT ‘aa9’ AS TESTCOLUMN –> NUMERIC IS FALSE
FROM DUAL UNION
SELECT ‘abc’ –> NUMERIC IS FALSE
FROM DUAL UNION
SELECT ‘bcd12’ –> NUMERIC IS FALSE
FROM DUAL UNION
SELECT ‘001’ –> NUMERIC IS TRUE
FROM DUAL UNION
SELECT ‘123’ –> NUMBERIC IS TRUE
FROM DUAL UNION
SELECT ‘999’ –> NUMERIC IS TRUE
FROM DUAL
)
TABLEDUAL
ORDER BY TESTCOLUMN
)
SELECT  TEST_TABLE.TESTCOLUMN,
CASE WHEN regexp_like(TEST_TABLE.TESTCOLUMN, ‘^-?[[:digit:],.]*$’THEN
   ‘NUMERIC IS TRUE’
ELSE
‘NUMERIC IS FALSE’
END AS NUMERIC_OR_NOT
FROM TEST_TABLE;

Output for the SQL;

TESTCOLUMN NUMERIC_OR_NOT
aa9 NUMERIC IS FALSE
abc NUMERIC IS FALSE
bcd12 NUMERIC IS FALSE
001 NUMERIC IS TRUE
123 NUMERIC IS TRUE
999 NUMERIC IS TRUE

ORACLE Exists usages with an example


In this post, I will explain usages of EXISTS condition;

First, EXISTS condition is used to filter the main data (resultset) based on the other query coded by application developers.

By using EXISTS, resultset displays all matching rows returned by both queries. However, keep in mind that this could be also accomplished by coding a standard JOIN syntax.  Let me explain with a simple example; I have 2 tables;

 EMPLOYEES and SUCCESSFUL_EMPLOYEES  tables

EMPLOYEES TABLE

EMPLOYEE_ID NUMBER
  FIRST_NAME VARCHAR2
  LAST_NAME VARCHAR2
  EMAIL VARCHAR2
  PHONE_NUMBER
  HIRE_DATE
  JOB_ID
  SALARY
  COMMISSION_PCT
  MANAGER_ID
  DEPARTMENT_ID
  ACTIVE –> stores if the employee is working or not (default = Y otherwise = N)

  SUCCESSFUL_EMPLOYEES  TABLE–> stores  successful employees and their projects in which they are successful    

  SUCCESSFUL_EMPLOYEE_ID NUMBER 
  EMPLOYEE_ID NUMBER
  PROJECT_ID NUMBER
  PROJECTDESC VARCHAR2 
  SELECTEDDATE DATE 
  COMMENTS

The first query returns the employees who are succesful on various projects

SELECT SUCCESSFUL_EMPLOYEE_ID, EMPLOYEE_ID, PROJECT_ID, PROJECTDESC 
FROM SUCCESSFUL_EMPLOYEES

SUCCESSFUL_EMPLOYEE_ID EMPLOYEE_ID PROJECT_ID PROJECTDESC
1 130 1 SQL Workshop
2 131 2 SQL Workshop2
3 132 2 SQL Workshop2
4 138 2 SQL Workshop2
5 142 2 SQL Wworkshop2

Just test if these employees are active with a simple query;

SELECT EMPLOYEE_ID, FIRST_NAMELAST_NAME, ACTIVE
FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (130,131,132,138,142);

And the result is;

EMPLOYEE_ID FIRST_NAME LAST_NAME ACTIVE
130 Mozhe Atkinson N
131 James Marlow N
132 TJ Olson N
138 Stephen Stiles Y
142 Curtis Davies Y

Now, I want rewrite the query and get the  firstname and lastname of employees who are active (working now) with
the help of EXISTS condition

This could be accomplished by writing a EXISTS condition

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME –> This query will run first
FROM EMPLOYEES
WHERE ACTIVE = ‘Y’
AND EXISTS (
 SELECT 1 FROM SUCCESSFUL_EMPLOYEES –> This query will run after the first query
 WHERE EMPLOYEE_ID = EMPLOYEES.EMPLOYEE_ID
)

Oracle will execute (parsing or changing) the above query like below;

FOR ALL_ACTIVE_EMPLOYEES IN
(SELECT EMPLOYEE_ID,FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE ACTIVE = ‘Y’ )
   LOOP
      IF( EXISTS ( SELECT 1 from
                               SUCCESSFUL_EMPLOYEES
                              WHERE EMPLOYEE_ID = ALL_ACTIVE_EMPLOYEES.EMPLOYEE_ID )
      THEN                                                                
         OUTPUT THE RECORD
      END IF
   END IF

And the result is;

EMPLOYEE_ID FIRST_NAME LAST_NAME
138 Stephen Stiles
142 Curtis Davies

Lets rewrite the query with a standard JOIN syntax

SELECT EMPLOYEES.EMPLOYEE_ID,EMPLOYEES.FIRST_NAME,EMPLOYEES.LAST_NAME
FROM EMPLOYEES, SUCCESSFUL_EMPLOYEES
WHERE EMPLOYEES.EMPLOYEE_ID = SUCCESSFUL_EMPLOYEES.EMPLOYEE_ID
AND EMPLOYEES.ACTIVE = ‘Y’;

And the result is same as the above one;

EMPLOYEE_ID FIRST_NAME LAST_NAME
138 Stephen Stiles
142 Curtis Davies
  • EXISTS condition could be used whenever a standard JOIN syntax is not available to code such as predefined reports having only one general SELECT  statement with an editable WHERE clause only. By the help of EXISTS condition, WHERE statement’s content could include a new condition based on the other table and this approach provides developers to have two tables joined.

Oracle’s COALESCE function usage


COALESCE(expression1, expression2, expression(n)…)

–returns the first not NULL value in coalesce function list
–following example will return the address1 if is not null, or the second expression’s value will return
–if the address2 is NULL  then the last expression’s value ‘doesn’t have an address’ will return from the function

SELECT first_name, last_name, COALESCE(address1,address2,’doesn’t  have an address’ )
FROM employees
WHERE employee_id < 105;

FIRST_NAME           LAST_NAME                 HASANADDRESS                             
——————– ————————- —————————————-
Steven                     King                            doesn’t  have an address               
Neena                      Kochhar                    King St. …
Lex                          De Haan                     Main St. …                                      
Alexander                 Hunold                       Allday St. ..

Oracle’s NULLIF function


NULLIF(expression1,expression2)

–ORACLE’s NULLIF function returns a NULL value if two expressions are equal
–otherwise expression1 is returned
–expression1 and expresion2 must have the same data types

example

–returns NULL if two expressions are equal
–I mean if max-salary – min_salary is 3000 then NULLIF returns NULL
–otherwise (max_salary- min_salary) result will return from the function

SELECT NULLIF((max_salary- min_salary), 3000)
FROM  jobs
WHERE max_salary < 10000;

NULLIF((MAX_SALARY-MIN_SALARY),3000)
————————————
NULL–max_salary and min_salary difference is 3000 here
4800
4800
NULL –max_salary and min_salary difference is 3000 here
NULL –max_salary and min_salary difference is 3000 here
NULL –max_salary and min_salary difference is 3000 here
NULL –max_salary and min_salary difference is 3000 here
5000
5000