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
2
votes
1 answer
Need json value without re-joining the table in SQL Server
I have written the code shown below and I got the expected output. but,need the same output without use the same tables in inline view (such as tables join for UserPhoneDetail_JSON). thanks in advance
Code:
BEGIN
DROP TABLE #USERMASTER;
DROP TABLE…
SARAVANAN CHINNU
- 59
- 5
2
votes
1 answer
Oracle WITH DELETE by row number
I am writing a procedure which removes old rows by their viewed date:
;WITH pv AS (
SELECT
RN = ROW_NUMBER() OVER (ORDER BY viewed DESC)
FROM
previouslyViewed
WHERE
userId = @userId
)
DELETE FROM pv WHERE RN >=…
user140628
2
votes
2 answers
mySQL version of 'with' clause
The object of my query is to search for a long string in a database. To speed up this process, all records of the longstring table have a hash of that string on the same record. I want to first find all records in the table where my search string's…
Nate
- 2,035
- 8
- 23
- 33
2
votes
1 answer
recursive WITH clause must reference itself directly in one of the UNION ALL branches
The following query is giving error : recursive WITH clause must reference itself directly in one of the UNION ALL branches
insert into tt_q_items
WITH CTE
(
REFUND_TRANSMISSION_QUEUE_KEY,
FINANCIAL_REFUND_REQUEST_KEY,
…
Shankari vatsalkumar
- 698
- 7
- 26
2
votes
1 answer
is it possible to use a `WITH` clause in a DataWindow query?
I have a DataWindow in a PB10.5 application which works perfectly until I add a WITH clause to the top of the SQL query that sits behind. The query painter accepts it as valid syntax, and if I manually retrieve the data in the DataWindow painter, it…
ninesided
- 23,085
- 14
- 83
- 107
1
vote
1 answer
How to use With and Values in Mysql
I have a question, how can I use 'with clause' with params in mysql as I used to do in postgre sql, i.e. using with and values, like below example :
Postgresql:
with params(idmodelo) as (values(10))
select * from modelo where idmodelo = (select…
Abel Huanacchiri
- 23
- 2
1
vote
1 answer
How can I assign a function result to a name in the WITH clause of a SELECT?
How can I assign a function result to a name in the WITH clause?
Tried:
with
has_perm as ( has_perm(:user) )
select * from my_table where has_perm = 'Y'
user5507535
- 1,580
- 1
- 18
- 39
1
vote
1 answer
BigQuery with clause in view
currently I'm working on a query which looks like following
WITH subquery1 AS (
SELECT ...
FROM rawdata
WHERE DATE(_PARTITIONTIME) = "2021-03-09"
),
subquery2 AS (
SELECT ...
FROM subquery 1
),
subquery3 AS (
SELECT ...
FROM…
pegu
- 21
- 4
1
vote
2 answers
Create view from query - oracle
I have query which use in oracle apex. Is it possible from this query create view where is for jasper report (Jasper not support with cte).
Note: P99_DATE_FROM and P99_DATE_TO are parameters!
with cte as (
SELECT
a.name,
w.order as order,
…
Pointer
- 2,123
- 3
- 32
- 59
1
vote
1 answer
rewrite sql without with clause
I've fallen down the SQL rabit hole, please help me
I need to rewrite this query without the with clause
with dept_total(dept_name, value) as
(select dept_name, sum(salary)
from instructor
group by dept_name),
dept_total_avg(value) as…
Missy
- 11
- 1
- 4
1
vote
1 answer
SQL Server 2008 - With Clause
In the Oracle database we had extensively used WITH clause in earlier project.
Now we are on SQL Server 2008 database. Do you suggest we can still use the WITH Clause in our SQL codes ? Does SQL Server 2008 support WITH clause ?
I mean does it…
Jocky
- 25
- 4
1
vote
2 answers
How to use UPDATE and WITH in PLSQL
Does anyone have a sample of how to use WITH and UPDATE in a query in PLSQL?
I binged and googled without success.
Here is what I am trying to do
WITH
OldRecords AS
(
SELECT 'New Records' as RecordLabel, rowid, OrDev.*
FROM CDR.MSRS_OR_ORDEV…
Raj More
- 47,048
- 33
- 131
- 198
1
vote
2 answers
Does Teradata support -Multiple With Clause?
Trying to execute in SQLAssitant (v 15.x Teradata):
WITH TEMP1 (EMP_ID,E_NAME,E_SAL) AS (WITH TEMP (EMP_ID,E_NAME,E_SAL) AS (SELECT EMP_ID,E_NAME,E_SAL FROM EMP_TABLE_TEST)
SELECT EMP_ID,E_NAME,E_SAL FROM TEMP) SELECT EMP_ID,E_NAME,E_SAL FROM…
Ganesh Kumar
- 133
- 1
- 3
- 12
1
vote
1 answer
missing FROM-clause entry for table in postgresql
I get this error when i run this query .please help me understand the error.But when i use count (*) i dont get this error.I modified the query and it is working but count i am not getting correct
select…
Sudarshan kumar
- 1,503
- 4
- 36
- 83
1
vote
1 answer
How can I enable ENABLE_WITH_CLAUSE_MATERIALIZATION?
I tried the following
mip_owner=> SELECT add_vertica_options('OPT', 'ENABLE_WITH_CLAUSE_MATERIALIZATION');
ERROR 4296: Options not set
HINT: ENABLE_WITH_CLAUSE_MATERIALIZATION is not a valid Opt Vertica option
Also after setting the dbdrole I…
minatverma
- 1,090
- 13
- 24