January 15, 2019 at 4:48 am
I finally have an opportunity to use CROSS APPLY in my code, but I can't seem to get it to work.
I've got a date table like so (I've removed columns not necessary for this question):
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [DT].[tblBillingCycleDates](
[CycleID] [int] NOT NULL,
[CalendarCycleStart] [datetime] NOT NULL,
[CalendarCycleEnd] [datetime] NOT NULL,
CONSTRAINT [PK_tblBillingCycleDatesCycleID] PRIMARY KEY CLUSTERED
(
[CycleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
I want to use this table's most recent record to compare against other tables data. So I'm trying this:
SELECT or.OrderID, or.OrderPaid, or.TotalTaxes, or.OrderTotal
FROM dbo.Orders or
CrossApply (SELECT CycleStartPlusOne, CycleEndPlusOne
FROM dt.tblBillingCycleDates
WHERE CycleID = @CycleID) cy
WHERE or.InvoiceDate >= cy.CalendarCycleStart
AND or.InvoiceDate <= cy.CalendarCycleEnd
AND or.OrderType IN (1,5);
But SQL does not like this code. It doesn't recognize the multipart identifier "cy" and won't let me use any data from the subquery for my WHERE clause. This is annoying because I'm trying to tune my code so I can get some performance gains.
Am I stuck with using an INNER JOIN?
January 15, 2019 at 5:06 am
1. CROSS APPLY needs a space between CROSS and APPLY
2. 'or' is not a valid alias to use for a table (without square brackets) as it is a reserved word for the logical OR operator.SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal, cy.CycleStartPlusOne, cy.CycleEndPlusOne
FROM dbo.Orders o
CROSS APPLY (SELECT TOP(1) x.CycleStartPlusOne, x.CycleEndPlusOne
FROM tblSCSCycleDates x
WHERE x.CycleID = @CycleID
AND o.InvoiceDate >= x.CalendarCycleStart
AND o.InvoiceDate <= x.CalendarCycleEnd
ORDER BY CalendarCycleStart DESC) cy
WHERE o.OrderType IN (1,5);
January 15, 2019 at 5:31 am
Jonathan AC Roberts - Tuesday, January 15, 2019 5:06 AM1. CROSS APPLY needs a space between CROSS and APPLY
2. 'or' is not a valid alias to use for a table (without square brackets) as it is a reserved word for the logical OR operator.SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal, cy.CycleStartPlusOne, cy.CycleEndPlusOne
FROM dbo.Orders o
CROSS APPLY (SELECT TOP(1) x.CycleStartPlusOne, x.CycleEndPlusOne
FROM tblBillingCycleDates x
WHERE x.CycleID = @CycleID
AND o.InvoiceDate >= x.CalendarCycleStart
AND o.InvoiceDate <= x.CalendarCycleEnd
ORDER BY CalendarCycleStart DESC) cy
WHERE o.OrderType IN (1,5);
Okay, it's early morning where I'm at and I was trying to alter code to be unrecognizable (and realize I missed a bit). So the space issue and the "or" alias are totally typos in transposing from my SSMS to this forum. Not real code issues.
But thanks for the code suggestion. I appreciate it.
January 15, 2019 at 5:39 am
Brandie Tarvin - Tuesday, January 15, 2019 5:31 AMJonathan AC Roberts - Tuesday, January 15, 2019 5:06 AM1. CROSS APPLY needs a space between CROSS and APPLY
2. 'or' is not a valid alias to use for a table (without square brackets) as it is a reserved word for the logical OR operator.SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal, cy.CycleStartPlusOne, cy.CycleEndPlusOne
FROM dbo.Orders o
CROSS APPLY (SELECT TOP(1) x.CycleStartPlusOne, x.CycleEndPlusOne
FROM tblBillingCycleDates x
WHERE x.CycleID = @CycleID
AND o.InvoiceDate >= x.CalendarCycleStart
AND o.InvoiceDate <= x.CalendarCycleEnd
ORDER BY CalendarCycleStart DESC) cy
WHERE o.OrderType IN (1,5);Okay, it's early morning where I'm at and I was trying to alter code to be unrecognizable (and realize I missed a bit). So the space issue and the "or" alias are totally typos in transposing from my SSMS to this forum. Not real code issues.
But thanks for the code suggestion. I appreciate it.
Yeah, I don't think that's going to work. I'm trying to filter the Order table so only the records that fit between the dates get pulled. Unfortunately your suggested code allows everything regardless of date to get pulled.
EDIT: Hang on. Don't respond to my last post yet. I might have another typo.
January 15, 2019 at 6:15 am
Brandie Tarvin - Tuesday, January 15, 2019 5:39 AMBrandie Tarvin - Tuesday, January 15, 2019 5:31 AMJonathan AC Roberts - Tuesday, January 15, 2019 5:06 AM1. CROSS APPLY needs a space between CROSS and APPLY
2. 'or' is not a valid alias to use for a table (without square brackets) as it is a reserved word for the logical OR operator.SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal, cy.CycleStartPlusOne, cy.CycleEndPlusOne
FROM dbo.Orders o
CROSS APPLY (SELECT TOP(1) x.CycleStartPlusOne, x.CycleEndPlusOne
FROM tblBillingCycleDates x
WHERE x.CycleID = @CycleID
AND o.InvoiceDate >= x.CalendarCycleStart
AND o.InvoiceDate <= x.CalendarCycleEnd
ORDER BY CalendarCycleStart DESC) cy
WHERE o.OrderType IN (1,5);Okay, it's early morning where I'm at and I was trying to alter code to be unrecognizable (and realize I missed a bit). So the space issue and the "or" alias are totally typos in transposing from my SSMS to this forum. Not real code issues.
But thanks for the code suggestion. I appreciate it.
Yeah, I don't think that's going to work. I'm trying to filter the Order table so only the records that fit between the dates get pulled. Unfortunately your suggested code allows everything regardless of date to get pulled.
EDIT: Hang on. Don't respond to my last post yet. I might have another typo.
"I'm trying to filter the Order table so only the records that fit between the dates get pulled. Unfortunately your suggested code allows everything regardless of date to get pulled".
Doesn't:AND o.InvoiceDate >= x.CalendarCycleStart
AND o.InvoiceDate <= x.CalendarCycleEnd
filter out the dates so that only rows with an InvoiceDate between CalendarCycleStart and CalendarCycleEnd are retrieved?
But if that is all you're trying to do you should maybe write the query with an EXISTS like this:SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal
FROM dbo.Orders o
WHERE EXISTS(SELECT *
FROM tblBillingCycleDates x
WHERE x.CycleID = @CycleID
AND o.InvoiceDate BETWEEN x.CalendarCycleStart AND x.CalendarCycleEnd)
AND o.OrderType IN (1,5);
January 15, 2019 at 8:18 am
CROSS APPLY isn't the correct approach here. CROSS APPLY should be used when the subquery is dependent in some way on the main query, and that's not the case here. You want the last record from tblBillingCycleDates. PERIOD. Use a CTE/ROW_NUMBER() to get the most recent record and then do a standard INNER JOIN.
WITH BillingCycleLastDate AS
(
SELECT CalendarCycleStart, CalendarCycleEnd
FROM tblBillingCycleDates bcd
ORDER BY bcd.CalendarCycleStart DESC
)
SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal
FROM dbo.Orders o
INNER JOIN BillingCycleLastDate bcld
ON o.InvoiceDate BETWEEN bcld.CalendarCycleStart AND bcld.CalendarCycleEnd
AND o.OrderType IN (1,5);
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 15, 2019 at 12:57 pm
A CROSS JOIN is good for this:
SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal,
cy.CycleStartPlusOne, cy.CycleEndPlusOne
FROM dbo.Orders o
CROSS JOIN (
SELECT TOP (1) CycleStartPlusOne, CycleEndPlusOne
FROM dbo.tblBillingCycleDates
WHERE CycleID = @CycleID
ORDER BY CalendarCycleStart DESC
) AS cy
WHERE o.OrderType IN (1,5)
AND o.InvoiceDate >= cy.CalendarCycleStart
AND o.InvoiceDate <= cy.CalendarCycleEnd
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 15, 2019 at 1:37 pm
ScottPletcher - Tuesday, January 15, 2019 12:57 PMA CROSS JOIN is good for this:
SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal,
cy.CycleStartPlusOne, cy.CycleEndPlusOne
FROM dbo.Orders o
CROSS JOIN (
SELECT TOP (1) CycleStartPlusOne, CycleEndPlusOne
FROM dbo.tblBillingCycleDates
WHERE CycleID = @CycleID
ORDER BY CalendarCycleStart DESC
) AS cy
WHERE o.OrderType IN (1,5)
AND o.InvoiceDate >= cy.CalendarCycleStart
AND o.InvoiceDate <= cy.CalendarCycleEnd
I prefer to keep criteria that encodes the relationship between tables and filters separate. Here, I think that last two criteria indicate a relationship between the two tables, so I think of this as a INNER JOIN even though the two are logically equivalent.
SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal,
cy.CycleStartPlusOne, cy.CycleEndPlusOne
FROM dbo.Orders o
INNER JOIN (
SELECT TOP (1) CycleStartPlusOne, CycleEndPlusOne
FROM dbo.tblBillingCycleDates
WHERE CycleID = @CycleID
ORDER BY CalendarCycleStart DESC
) AS cy
ON o.InvoiceDate >= cy.CalendarCycleStart
AND o.InvoiceDate <= cy.CalendarCycleEnd
WHERE o.OrderType IN (1,5)
And, yes, in my previous response, I should have put the one criterion in the WHERE clause instead of leaving it in the ON clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 15, 2019 at 2:02 pm
drew.allen - Tuesday, January 15, 2019 1:36 PMScottPletcher - Tuesday, January 15, 2019 12:57 PMA CROSS JOIN is good for this:
SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal,
cy.CycleStartPlusOne, cy.CycleEndPlusOne
FROM dbo.Orders o
CROSS JOIN (
SELECT TOP (1) CycleStartPlusOne, CycleEndPlusOne
FROM dbo.tblBillingCycleDates
WHERE CycleID = @CycleID
ORDER BY CalendarCycleStart DESC
) AS cy
WHERE o.OrderType IN (1,5)
AND o.InvoiceDate >= cy.CalendarCycleStart
AND o.InvoiceDate <= cy.CalendarCycleEndI prefer to keep criteria that encodes the relationship between tables and filters separate. Here, I think that last two criteria indicate a relationship between the two tables, so I think of this as a INNER JOIN even though the two are logically equivalent.
SELECT o.OrderID, o.OrderPaid, o.TotalTaxes, o.OrderTotal,
cy.CycleStartPlusOne, cy.CycleEndPlusOne
FROM dbo.Orders o
INNER JOIN (
SELECT TOP (1) CycleStartPlusOne, CycleEndPlusOne
FROM dbo.tblBillingCycleDates
WHERE CycleID = @CycleID
ORDER BY CalendarCycleStart DESC
) AS cy
ON o.InvoiceDate >= cy.CalendarCycleStart
AND o.InvoiceDate <= cy.CalendarCycleEnd
WHERE o.OrderType IN (1,5)And, yes, in my previous response, I should have put the one criterion in the WHERE clause instead of leaving it in the ON clause.
Drew
An INNER JOIN implies an ongoing matching between the two tables. There really isn't that here: you're picking one row from a table and forever matching to that. That is, there are no conditions or values outside the tblBillingCycleDates that affect which row gets chosen from that table. I believe a CROSS JOIN conveys that more clearly than an INNER JOIN.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 19, 2019 at 12:01 pm
I haven't seen any solutions where CalendarCycleStart or CalendarCycleEnd is included in the CROSS APPLY select statement, so they wouldn't be usable in the where clause right?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply