Stack Overflow
Stack Overflow

Questions tagged [subquery-factoring]

Use this tag for questions related to the subquery factoring, i.e. the WITH clause, a part of the SQL-99 standard.

Most of the tagged questions are related to SQL-like topics and of course, Oracle.

The WITH clause, or subquery factoring clause, is part of the SQL-99 standard and was added into the Oracle SQL syntax in Oracle 9.2. The WITH clause may be processed as an inline view or resolved as a temporary table. The advantage of the latter is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table, rather than being requeried by each reference. You should assess the performance implications of the WITH clause on a case-by-case basis.

Source: WITH Clause : Subquery Factoring

19 questions
156
votes
7 answers

How do you use the "WITH" clause in MySQL?

I am converting all my SQL Server queries to MySQL and my queries that have WITH in them are all failing. Here's an example: WITH t1 AS ( SELECT article.*, userinfo.*, category.* FROM question INNER JOIN userinfo ON…
user161433
  • 4,419
  • 5
  • 32
  • 55
17
votes
1 answer

Oracle -- WITH CLAUSE => MERGE? (Syntax error, )

I'm trying to get the WITH clause to work with merge in Oracle, but for some reason I can't get it working. I'm sure it is something obvious, but I just haven't seen it. -- behold, the wonders of fake data WITH X AS ( SELECT 'moo' AS COW, …
cwallenpoole
  • 79,954
  • 26
  • 128
  • 166
12
votes
3 answers

Oracle DELETE statement with subquery factoring

Trying to do this (works in SQL Server): WITH X AS (), Y AS (), Z AS () DELETE FROM TBL WHERE TBL.ID IN (SELECT ID FROM Z); This works in Oracle: WITH X AS (), Y AS (), Z AS () SELECT * FROM TBL WHERE TBL.ID IN (SELECT ID FROM Z); But the DELETE…
Cade Roux
  • 88,164
  • 40
  • 182
  • 265
2
votes
1 answer

Oracle 12c Subquery Factoring Inline View now has bad plan?

Update 11/2 After some additional troubleshooting, my team was able to tie this Oracle bug directly to a parameter change that was made on the 12c database the night before the query stopped working. After experiencing some performance issues from…
user2858650
2
votes
2 answers

Does Perl DBI Support Oracle Subquery Factoring?

I have searched online for several days and cannot find an answer. Does Perl DBI support Oracle Subquery Factoring (i.e. WITH-clause)? As an example, the simple Perl DBI application further below fails with the error: DBD::Oracle::st fetchrow_array…
TuAmigoAlejandro
  • 75
  • 7
1
vote
0 answers

Oracle database slow execution of statement with multiple recursive common table expressions

I have an Oracle 19c database table with "resources" that are organized hierarchically like a nested folder tree. The table contains around 2.5 million rows and the tree is up to 10 levels deep. create table RESOURCES ( ID_ NUMBER(10) not…
ahu
  • 1,492
  • 12
  • 11
1
vote
1 answer

How to manipulate values and get a new result on in SQL?

I have this query: SELECT c.ID, c.Firstname, c.lastname, c.BDaY, c.gender, cp.code, cp.Citizenship, r.race, e.ethnicity FROM Client AS C (nolock) JOIN Citizenship AS cp (nolock) ON c.ID = cp.client_ID JOIN Race AS r (nolock)…
Pedram Salamati
  • 137
  • 10
1
vote
1 answer

oracle 12c using subquery factoring clause with plsql declaration

I'm a big fan of the subquery factoring clause. WITH t1 as (select 1 as id from dual) select * from t1; and oracle 12c now includes a PL/SQL declaration section in the WITH clause WITH FUNCTION with_function(p_id IN NUMBER) RETURN NUMBER IS …
Bryan Dellinger
  • 4,724
  • 7
  • 33
  • 79
1
vote
1 answer

ORA-00928 selecting from view over DB link, but works locally

I have a view with a query of the following form which works perfectly when I query it on the local server from any schema: WITH dates AS ( SELECT /*+ materialize */ ... FROM ( SELECT ... FROM table@link) UNION ALL SELECT * FROM (…
Charles Burns
  • 10,310
  • 7
  • 64
  • 81
0
votes
2 answers

Use result of select query into another multiple times

I have one complex select query(Query-1) which is executed using INTERSECT and it returns IDs of a particular column. Query #1: SELECT my_id FROM my_table INTERSECT SELECT my_id FROM other_table; Now there is another more complex query which…
Chetan Kondawle
  • 179
  • 12
0
votes
1 answer

using subquery factoring result in where clause

Why can't I use a subquery factoring clause result in the where clause of as depicted in the following sql: with rpt as( select * from reports where caseid = :case_id and rownum=1 order by created desc ) select distinct rt.trialid from …
Vivek
  • 461
  • 1
  • 3
  • 13
0
votes
1 answer

PL/SQL: Use 'IF' statement outside 'WITH ... AS' clause

I am trying to write a procedure where I use Subquery Factoring 'WITH .. AS', but when I use 'IF .. THEN' before it, I get syntax error, I don't know how should I write it, any help? BEGIN OPEN my_SYS_REFCURSOR FOR IF .. IS NULL THEN …
Safaa Mamdouh Salem
  • 119
  • 1
  • 8
0
votes
1 answer

Can someone please help me with a good article on CTE. Common table expression

can someone provide me with an article on CTE. Article should describe purpose of CTE and where it should be used.
sly_Chandan
  • 3,437
  • 12
  • 54
  • 83
0
votes
1 answer

Using a CTE in Visual Studio 3 Queries

I'm trying to get results from a subquery created as a SQL statement in a tableadapter in Visual Studio. The project is linked to an oracle database. I was originally trying to use a "with" clause, which works fine in Oracle, but not so much as a…
Raspberry
  • 3
  • 2
0
votes
1 answer

query limit constraint propagation into subquery

Here my streamlined situation create table t1 (i integer, d text); insert into t1 values (0,'aa0'); insert into t1 values (1,'aa1'); insert into t1 values (2,'aa2'); insert into t1 values (3,'aa3'); insert into t1 values (4,'aa4'); insert into t1…
Phi
  • 735
  • 7
  • 22
1
2