Questions tagged [outer-apply]
65 questions
1
vote
0 answers
How to force Linq-To-Sql to use Outer Apply
I'm trying to create a linq query that gives me a list with the number column from a master table with the count of detail records. My problem is that linq spits out a query without an outer apply which makes the query take 15 seconds. If I create…
Zer0ne
- 11
- 2
1
vote
1 answer
Outer apply (and cross apply ) against the same table result
Considering the following script (with SQL Server 2017):
declare @mytable as table (n int identity(1,1), name varchar(10),mydate date)
insert into @mytable(name,mydate) values('a','01/01/2019')
select * from @mytable t1
cross apply (select *…
Kemal AL GAZZAH
- 967
- 6
- 15
1
vote
0 answers
CROSS APPLY vs OUTER APPLY when CROSS APPLY matches all rows
Example:
UPDATE t
SET Amount = applied.MaxAmount
FROM @test t
CROSS APPLY
(
SELECT MAX(t2.Amount) AS MaxAmount
FROM @test t2
WHERE t2.Id = t.ForeignId
) AS applied
vs
UPDATE t
SET Amount = applied.MaxAmount
FROM @test t
OUTER APPLY
(
…
Kyle
- 63
- 1
- 7
1
vote
1 answer
Oracle OUTER APPLY with TABLE constructor behaves like CROSS APPLY. Bug?
Consider the following setup:
CREATE TYPE list_t AS VARRAY(10) OF NUMBER(10);
/
CREATE TABLE x (
id NUMBER(10),
list list_t
);
INSERT INTO x VALUES (0, list_t());
INSERT INTO x VALUES (1, list_t(1));
INSERT INTO x VALUES (2, list_t(1, 2));
My…
Lukas Eder
- 211,314
- 129
- 689
- 1,509
1
vote
3 answers
Getting the latest entry per day / SQL Optimizing
Given the following database table, which records events (status) for different objects (id) with its timestamp:
ID | Date | Time | Status
-------------------------------
7 | 2016-10-10 | 8:23 | Passed
7 | 2016-10-10 | 8:29 | Failed
7 |…
rcvd
- 51
- 1
- 6
1
vote
2 answers
MS SQL SERVER 2008 RS OUTER APPLY and empty RS
Longtime lurker here with my first question.
I have the following tables:
Accounts
Emails
PhoneNumbers
I want to grab everything from Accounts and, for each Accounts record, the most recent Top 1 from Emails and PhoneNumbers where Accounts.ID =…
nbardach
- 123
- 1
- 10
0
votes
2 answers
How can I union values across multiple columns of a result set without repeated table scans in the query plan?
I have data in table Foo in columns x1 and x2 and x3, and more data in table Bar in x4. Both tables are primary-keyed by a unique id, and each Bar has a foreign key to a single Foo, as in the schema below:
CREATE TABLE Foo (id INT, x1 INT, x2 INT,…
Sean Werkema
- 5,810
- 2
- 38
- 42
0
votes
0 answers
Convert costly OUTER APPLY to LEFT JOIN
I've a query that does the below
select * from main_table mt
OUTER APPLY
(
select top 1 * from sub_table st
where mt.one_id = st.another_id
and st.one_date > mt.another_date
and st.another_date < mt.one_date
ORDER BY st.yet_another_date…
codingIsCool
- 64
- 1
- 9
0
votes
0 answers
DISTINCT Results of CTE Outer Apply return empty set
Summary:
Results of CTE using OUTER APPLY return duplicates due to dirty nature of data and results of RegexFind to split out commingled key=value pairs.
When using DISTINCT on that CTE, the result is empty. Is this a bug in SQLServer…
Joseph Shirk
- 45
- 5
0
votes
1 answer
How can I use with inside apply in sql server? I know another solution without apply. but can we do with outer apply? if 'yes' then how?
Can we use with inside any apply in the SQL server?
For traversing all nodes in the XML file I am trying to use outer apply so I can traverse in one go.
DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
SELECT @XML = XMLData FROM…
Sunil Jadhav
- 71
- 6
0
votes
0 answers
How do I modify a Stored Procedure to Join an additional table?
I have a fairly complex Stored Procedure that is joining several tables together, but I need yet another column called in from a table that is yet to be joined.
Here's the Stored Procedure as it stands:
CREATE PROCEDURE…
Justin Bonebrake
- 1
- 2
0
votes
0 answers
Is it possible to do a outer apply on two tables from different database engines to get unmatched records?
I am trying to find records that are in table C but not in table M in a column called records. I am doing an outer apply but the query does not finish executing. It does not give any errors. The two tables are in different database engines. I am…
Pauli du Plooy
- 21
- 4
0
votes
1 answer
Oracle Create Materialized View With Outer Apply Throws "missing right parenthesis" Error
I'm trying to create materialized view in Oracle DB (version: 19c), but I get the following error:
ORA-00907: missing right parenthesis
I'm kinda suspecting maybe outer apply has something to do with it? Maybe it does not allow it?
The query is:…
aquawhale
- 11
- 3
0
votes
1 answer
Replace OUTER APPLY
I want to replace some OUTER APPLYs in my SQL because they seem to be a little bit slow and eating resources () on a poor VPS. I have no idea what to use instead? LEFT OUTER JOIN (??)
Here's my code
SELECT e.Id,
Decision.Comment,
…
Cătălin Rădoi
- 1,804
- 23
- 43
0
votes
1 answer
I have a syntax error in my Outer Apply statement, I can't understand why
This is my code
SELECT
A.* ,
B.ID as CB_ID,
FROM
`TABLE_1` A
OUTER APPLY (
SELECT TOP 1
FROM `TABLE_2` B
WHERE A.business_ID = B.company_ID)
I'm getting this error
Syntax error: Expected end of input but got keyword OUTER at…
Elie Antoun
- 3
- 4