Creating a Cte in Oracle

Creating a CTE in Oracle

You can create your common table expression (CTE, subquery factoring, etc.) by selecting the date values from dual, and unioning them all together:

with RTG_YEARS (YR) as (
  select to_date('2013-01-01', 'yyyy-mm-dd') from dual
  union all select to_date('2013-12-31', 'yyyy-mm-dd') from dual
  union all select to_date('2014-01-01', 'yyyy-mm-dd') from dual
  union all select to_date('2014-12-31', 'yyyy-mm-dd') from dual
  union all select to_date('2015-01-01', 'yyyy-mm-dd') from dual
  union all select to_date('2015-12-31', 'yyyy-mm-dd') from dual
)
select * from RTG_YEARS;

YR       
----------
2013-01-01
2013-12-31
2014-01-01
2014-12-31
2015-01-01
2015-12-31

Not related to it being a CTE, but you can reduce the typing a bit by using date literals:

with RTG_YEARS (YR) as (
  select date '2013-01-01' from dual
  union all select date '2013-12-31' from dual
  union all select date '2014-01-01' from dual
  union all select date '2014-12-31' from dual
  union all select date '2015-01-01' from dual
  union all select date '2015-12-31' from dual
)
select * from RTG_YEARS;

How to update a table using a CTE in Oracle

Well, as you asked how to use a CTE in UPDATE, then:

update person p set
  p.name = (with cte_person as
              (select personalidentificationnumber, name
               from employee
              )
            select c.name
            from cte_person c
            where c.personalidentificationnumber = p.personalidentificationnumber
           )
  where exists (select null
                from employee e
                where e.personalidentificationnumber = p.personalidentificationnumber
               );

Though, merge is somewhat simpler as you don't have to additionally check which rows to update (see the exists clause in update example):

merge into person p
  using (with cte_person as
              (select personalidentificationnumber, name
               from employee
              )
         select c.personalidentificationnumber,
                c.name
         from cte_person c
        ) x
  on (p.personalidentificationnumber = x.personalidentificationnumber)
  when matched then update set 
    p.name = x.name;        

---

However, this can be simplified - see code Ankit posted (but - as I said - if you want to know how to use a CTE, then that's how).

How does a simple CTE work?

Your CTE is just a syntax variation of a Derived Table (which Oracle calls Inline View):

select *
from table2
inner join
 (
   select *
   from table1
   where conditions1
 ) AS test_cte
on conditions2
where conditions3

A decent optmizer will not always create the result of the CTE/DT first (only if it's too complex), so in your case the plan should be similar to your 2nd query. Simply compare the plan of both queries.

And as you noticed CTEs/DTs are mainly used to simplify writing of more complex queries by splitting it into smaller groups of logic or because you need something you can't write in a single level, like an Aggregate on top of a Windowed Aggregate.

Generate range of dates using CTE Oracle

This was a known bug in recursive CTE's in Oracle 11 (specifically with regard to date arithmetic). Fixed in Oracle 12. Exactly that behavior: whether you add or subtract in your code, the engine always subtracts, it never adds.

EDIT: Actually, as Alex Poole pointed out in a Comment to the original post, the bug exists through Oracle 11.2.0.2 and it was fixed in 11.2.0.3. End edit

Alas I am not a paying customer, so I can't quote chapter and verse, but with a little bit of Googling you will find links to this (including on OTN where I was involved in a few threads discussing this and other bugs in recursive CTEs - some were fixed, some are still bugs in Oracle 12.1).

Added - here is one of those discussions: https://community.oracle.com/thread/3974408

Select into variables from cte using if oracle

In your code:

-- Start of first SELECT statement.
WITH test_cte AS (
  SELECT *
  from(
    SELECT date_a,lastName,firstName,birthDate, rank() over(ORDER BY date_a desc) rnk
    FROM test_table a
    join test_table b ON b.id = a.bid 
  )a1
  WHERE rnk =1
)
SELECT count(*) into count_a 
FROM test_table a
join test_table b ON b.id = a.bid
WHERE a.code = code_name;
-- End of first SELECT statement.

IF count_a > 0 THEN
  -- Start of second SELECT statement.
  SELECT date_a,lastName,firstName,birthDate
  into date_a_var,lastName_var,firstName_var,birthDate_var
  FROM test_cte;
  -- End of second SELECT statement.
ELSE 
  date_a_var := NULL;
  lastName_var := NULL;
  firstName_var := NULL;
  birthDate_var := NULL;
END IF;

It won't work because the test_cte only exists for the one statement and when you finish the statement's final SELECT statement then it no longer exists for the subsequent statements. (However, you do not use the sub-query factoring clause [a.k.a. CTE] in the SELECT for that statement so it is not clear why you need the WITH clause.)

Instead of trying to use COUNT, just get the data and handle the NO_DATA_FOUND exception if it occurs (also, from Oracle 12, you don't need to use RANK and can use FETCH FIRST ROW WITH TIES instead):

DECLARE
  date_a_var    test_table.date_a%TYPE;
  lastName_var  test_table.lastname%TYPE;
  firstName_var test_table.firstname%TYPE;
  birthDate_var test_table.birthdate%TYPE;
BEGIN
  BEGIN
    SELECT date_a,
           lastName,
           firstName,
           birthDate
    INTO   date_a_var,
           lastName_var,
           firstName_var,
           birthDate_var
    FROM   test_table a
           join test_table b ON b.id = a.bid 
    ORDER BY date_a DESC
    FETCH FIRST ROW WITH TIES;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      date_a_var    := NULL;
      lastName_var  := NULL;
      firstName_var := NULL;
      birthDate_var := NULL;
  END;

  -- Continue processing
END;

(Note: You may also get a TOO_MANY_ROWS exception if there are duplicate dates. Either use FETCH FIRST ROW ONLY or, before Oracle 12, the ROW_NUMBER analytic function.)

How to insert CTE output to temp table in Oracle?

Just Replace the order for create table statement like below :

create table temp_recent_order as
with cte as (
select ORDER_ID,
       STATUS_ID,
       CALL_DATE,
       SHIP_DATE,
       UPDATE_USER_ID,
       UPDATE_TIMESTAMP,
       row_number() over(partition by ORDER_ID order by update_timestamp desc) as rowno
  from ORDER_HISTORY
 where ORDER_ID in (1001, 1002, 1003)
)
select * from cte where rowno=1;

---

Related Topics