Stack Overflow
Stack Overflow

Questions tagged [cross-apply]

The T-SQL APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. CROSS APPLY acts as a replacement for an INNER JOIN and only returns rows if there is a match on the join condition. Using CROSS APPLY in some instances will perform better than an equivalent JOIN statement. It is similar to a `CROSS JOIN LATERAL` in the SQL standard

358 questions
0
votes
2 answers

SQL Server 2005 - WHERE NOT EXISTS and a complicated script inside of the NOT EXISTS

Script objective: get a list of numbers from a list where the numbers don't show up in another list. The complications: the other list of numbers can only be obtained via a complicated script - and for some reason I'm getting no results when I KNOW…
Lockindal Linantal
  • 3
  • 1
0
votes
1 answer

Query XML Data column using SQL and XML methods

I am performing a query against an XML data type column in SQL Server 2012. An example of the data is: Home …
Ivica Čevis
  • 33
  • 6
0
votes
0 answers

How to handle result set like a table?

DECLARE @Test TABLE (id INT,smo NVARCHAR(100),grad NVARCHAR(100),lst int) INSERT INTO @Test SELECT 11,'Ali','G',1 UNION ALL SELECT 11,'Ali','C',1 UNION ALL SELECT 11,'Ali','F',0 UNION ALL SELECT 12,'Veli','C',1 UNION ALL SELECT…
Burak
  • 45
  • 4
0
votes
1 answer

Customer can only have 01 orderOnSale subquery / CROSS APPLY in SQL Server 2008

I have created a dummy scenario that reflect many of the queries I have to write to check that some data we are importing is correct. The example would be when you have 3 tables Store Customer CustomerOrder A Customer can belong to many stores…
user9969
  • 15,632
  • 39
  • 107
  • 175
0
votes
1 answer

How to apply .nodes() to a column's cell instead of XML variable?

Is there a way to apply a .node() method to a specific column instead of a XML variable? I'm working on transferring XML files into a SQL Table. Some of my XML files have child-nodes. So I'm using the Cross Apply function to connect the children…
user3281388
  • 267
  • 1
  • 5
  • 20
0
votes
1 answer

Have Table Value Function return NULLS if no record exists

I am using SQL Server 2008 R2 to retrieve records which I perform multiple CROSS APPLY's to various table valued functions to expand on values from the original table. In the event that the TVF does not return results, I still want to see the…
Ryan Dunphy
  • 792
  • 2
  • 10
  • 33
0
votes
1 answer

Getting rows of a table based on query on another table

I have these 2 tables [Products] ID CompanyID, Name, PartNo, IDSGroup, ChartNo [Company] ID, Name, RegistrationNo, RegistrationDate I want to show these columns from tables when user search for a company name: Company.Name, Product.Name,…
Hadi Sharghi
  • 903
  • 16
  • 33
0
votes
1 answer

SQL Convert Columns into Rows

I have a table with three columns and 18 rows. I want to change into the 18 columns with two rows. Here is the table Category Bands Points 20_CAT1 21-22 10 20_CAT2 23-25 20 20_CAT3 26- 30 30_CAT1 31-33 10 30_CAT2 …
Mazhar
  • 3,797
  • 1
  • 12
  • 29
0
votes
3 answers

Cross Apply Syntax

I have a query where I think it would be more efficient to use cross apply. I've changed the syntax from join to cross apply, but I'm getting a generic syntax error: Msg 170, Level 15, State 1, Line 14 Line 14: Incorrect syntax near 'apply'. …
user3253723
  • 11
  • 1
  • 3
0
votes
0 answers

How to INSERT from a CROSS APPLY and do an INTEGER INCREMENT while at it for a specific column in SQL Server?

I have this query (which works just fine): INSERT INTO @CreditDebitAdjustmentDetail ( InvoiceNum, ClaimNum, ClaimantGLNNum, LineNum, …
JJ.
  • 9,580
  • 37
  • 116
  • 189
0
votes
1 answer

Search using CROSS APPLY SQL Statement

I have the following SQL statement which returns results from a database in the format I need. However, I would like to use this query but add a search for where JobProducts.Serial = x How can I add such a search? SELECT J.CustomerID, J.JobID,…
user1711657
  • 83
  • 1
  • 7
0
votes
1 answer

How do I select the node values from the following XML using SQL?

I have the following: DECLARE @XML XML SET @XML = ' Jorge Bond …
JJ.
  • 9,580
  • 37
  • 116
  • 189
0
votes
1 answer

How to build a SQL Server 2005 query that combines three tables' data into a single grid output?

I have an Items table with about 30000 records in it, a Groupings table into which my clients are grouped, a Categories table into which my Products are sorted, and a Product_Categories table that stores which Items go into which Categories for…
Frank Bailey
  • 185
  • 2
  • 17
0
votes
1 answer

Incorrect syntax near the keyword 'VALUES' when using CROSS APPLY

I have some trouble, in seeing the syntax error below statement. I tried to run this on fiddle demo and it works. I'm using Sql Server 2005 and the compatibility mode is 90. SELECT C.col,C.value,C.sortorder FROM PAY_REGISTRY PR CROSS…
Ice_Drop
  • 452
  • 1
  • 9
  • 19
0
votes
2 answers

Sql Query with CROSS APPLY throwing error

I have the following sql query which has a cross apply with a sub query. SELECT pm.[FileName] ,REPLACE([Message], 'Upload End. Total Row:', '') cnt_char ,CAST(REPLACE([Message], 'Upload End. Total Row:', '') AS INT) AS row_count FROM…
Satish
  • 3,020
  • 7
  • 35
  • 47
1 2 3
…
23 24