CODEWITHSUNDEEP
sqloracle-databaseplsql
Nianios
Nianios

Reputation: 1426

SQL select statement union all with itself

Hi I have a very complicated sql statement that returns some results.
Some of the results are in another table with the same Id.
I would like to have a result set like the first plus a duplicate of all the rows that belong to the other table.

The code below is wrong but shows what I am trying to achieve:

Select myRules.* 
From (Complicated sql statement) myRules
Union All
Select from myRules 
inner join Table2
on myRules.Id =Table2.Id;

Is this possible?

PS1. I don't want to touch the complicated sql statement.
PS2. The error that I get is that myRules Table or View does not exist

Edit: Based on your answers I try to create a procedure that returns a cursor but I got an error in the line I open the cursor:

procedure Get_Rules(in_Id      in tableA.Reconciliation_Id%type,
                    io_cursor  in out t_ref_cursor) is
begin
With  myRules as (
 complicated sql statement)

open io_cursor for   -- ERRORS here: missing SELECT keyword
select *
   from myRules
   union all
select * 
  from myRules 
  inner join Table2
  on myRules.Id = Table2.Id;
end Get_Rules;

Upvotes: 2

Views: 1724

Answers (2)

Martin Schapendonk
Martin Schapendonk

Reputation: 13486

Yes, it is perfectly valid to union all a query with another query on the same table. Better use with in that case:

with myRules as (
  complicated sql statement
)
select *
from myRules
union all
select * 
from myRules 
inner join Table2
on myRules.Id = Table2.Id;

If you want to use with in a cursor, embed it in the cursor, not the stored procedure:

procedure Get_Rules(in_Id      in tableA.Reconciliation_Id%type,
                    io_cursor  in out t_ref_cursor) is
begin
  open io_cursor for
  with  myRules as (
   complicated sql statement
  )
  select *
  from myRules
  union all
  select * 
  from myRules 
  inner join Table2
  on myRules.Id = Table2.Id;
end Get_Rules;

Upvotes: 4

jarlh
jarlh

Reputation: 44696

Use a cte (common table expression):

with myRules as
(
Complicated sql statement
)
Select myRules.* 
From myRules
Union All
Select from myRules 
inner join Table2
on myRules.Id = Table2.Id;

Upvotes: 3

Related Questions