A WITH clause is the ISO/ANSI terminology for "common table expression". A WITH clause provides a way to write named queries for use in a larger query
Questions tagged [with-clause]
67 questions
0
votes
1 answer
Snowflake queries with CTE seems not to cache results
When I execute a query containing a CTE (common table expression defined by WITH clause) in Snowflake, the result is not cached.
The question now is: is this how Snowflake works-as-designed, or do I need to consider something to force a result…
ralfbecher
- 228
- 1
- 9
0
votes
1 answer
Problem with MySQL while using 'WITH' clause to manage hierachical data
I am having a hard time getting the following query to work correctly. I feel like I have everything set up correctly. I read that earlier versions of MySQL Workbench did not support 'with'. I updated Ubuntu and am running MySQL Workbench 8.0.20.…
WorkinOnIt
- 9
- 3
0
votes
0 answers
PL SQL fuzzy search with clause
I am working on call center customer search query. If user enter last name and driving license I have to find out the most close matching customer(s) from database. I am using fuzzy search, soundex() to compare customer last name. Base on…
ksam
- 59
- 1
- 7
0
votes
3 answers
Replace temporary table with with clause
in DB#1 I am creating table tC by joining table A with table B where table B includes a list of values inserted by a remote query from a different DB#2 (DB link).
create table tC
as
(
select ta.col3, tb.col4
from
tA, tB
where
…
Amir
- 1
- 2
0
votes
0 answers
"no more data to read from socket" message when using with clause (in oracle sql developer)
I am trying to simplify a select statement by using with clause in oracle sql. Below is the code; when I run it, first it opens a pop-up window saying "Your database connection has been reset. Any pending transactions or session state has been…
kzmlbyrk
- 583
- 1
- 4
- 17
0
votes
2 answers
Several usage of sql with alias
I want to use several time alias of with clause on SQL server.
Example:
WITH name1 AS (
SELECT ...
)
SELECT * from name1
SELECT * from name1 ORDER BY name
Is it possible?
I'm getting "Invalid object name " error
mondayguy
- 973
- 2
- 12
- 34
0
votes
1 answer
Passing a parameter to a WITH clause query in Oracle
I'm wondering if it's possible to pass one or more parameters to a WITH clause query; in a very simple way, doing something like this (taht, obviously, is not working!):
with qq(a) as (
select a+1 as increment
from dual
)
select qq.increment…
Cicca
- 11
- 1
- 2
0
votes
1 answer
sql server with clause issue
sql below runs without problem in db2
with mytable(a,b) as (
values(
(select current timestamp from sysibm.sysdummy1), (select current timestamp from sysibm.sysdummy1))
)
select * from mytable
I want to run something similar in sql server,…
clickit
- 21
- 3
0
votes
1 answer
Oracle PL/SQL Select into variable using WITH clause
I have a WITH clause that gives me the desired result, and i'm trying to put it in a variable. I have omitted code for simplicity. The last line of code is most relevant.
WITH ALL_VE_ERRORS AS (
SELECT *
FROM…
SomeRandomDeveloper
- 489
- 2
- 13
- 33
0
votes
2 answers
Using the results of WITH clause IN where STATEMENT of main query
I am relatively new at SQL so I apologise if this is obvious but I cannot work out how to use the results of the WITH clause query in the where statement of my main query.
My with query pulls the first record for each customer and gives the sale…
Caroline Allen
- 183
- 14
0
votes
1 answer
Need more info on SQL "WITH" clause
I was attempting to create a table custom_table
Product doesn't have price column and both pc and laptop doesn't have a type column.
So i need to know how to create a table using with clause during such a scenario?
Here is my query
with…
0
votes
1 answer
Create table using two with clauses
I want to create table and use two with, here is my code:
Create Table profitTry
As
With ctsWithSplit as (select c.Tdate, c.Symbol, c.close * coalesce(s.post/s.pre, 1) as new_close
from ctsTry c left join splits s
on c.Tdate = s.Tdate and c.symbol…
user4441082
- 371
- 1
- 5
- 21
0
votes
1 answer
Creating a table in Oracle using a query that contains a WITH clause within it
I am basically trying to run a create table statement using a query that has a with clause within it, but I am getting an error. Is there a different way to run this? The query statement is something like this:
CREATE TABLE DATA_TABLE AS
(
WITH X…
dorianpc
- 319
- 2
- 5
- 10
0
votes
3 answers
Oracle SQL WITH clause select joined column
SQL:
WITH joined AS (
SELECT *
FROM table_a a
JOIN table_b b ON (a.a_id = b.a_id)
)
SELECT a_id
FROM joined
returns invalid identifier.
How can you select joined column when using WITH clause? I have tried aliases, prefixing and…
B.Gen.Jack.O.Neill
- 8,169
- 12
- 51
- 79
0
votes
2 answers
Can ASP's objConn.execute handle a SQL query that uses the subquery factoring with clause?
I have a long SQL statement that's essentially:
with a as (select * from t1),
b as (select * from a, t2 where a.id=t2.id)
select * from b
This statement executes perfectly fine in my TOAD application. But, when I try to stuff the above into a…
TrMako
- 31
- 1
- 1
- 3