June 9, 2014 at 6:17 am
/*
The important bits of the tables I'm using
*/
Create table #Orders
(
OrderNo int
,DateOfReceipt date
,UglyGUID varchar(10)
,StatusID int ---- 1 equals active 3 = void
)
insert into #Orders (OrderNo, DateOfReceipt,UglyGUID, StatusID)
select 99997,'2013-12-28 14:00:00', 'GUID000001' ,1 union all
select 99998,'2013-12-29 14:00:00', 'GUID000002' ,1 union all
select 99999,'2013-12-30 14:00:00', 'GUID000003' ,1 union all
select 10000,'2013-12-31 14:00:00', 'GUID000004' ,1 union all
select 10001,'2013-12-31 14:21:00', 'GUID000005' ,1 union all
select 10002,'2013-12-31 14:31:00', 'GUID000006' ,1 union all
select 10003,'2013-12-31 14:41:00', 'GUID000007' ,1 union all
select 10004,'2013-12-31 14:42:00', 'GUID000008' ,1 union all
select 10005,'2014-01-01 14:42:00', 'GUID000009' ,1 union all
select 10006,'2014-01-02 14:42:00', 'GUID000010' ,1
create table #Pirates
(
PirateNo int ----This doesn't necessarily match the order number in the real world, hence the join on UglyGUID
,Captain Varchar(20) null
,UglyGUID varchar(10)
,TestBit bit ---- 0 = live pirate
)
insert into #Pirates (PirateNo, Captain, UglyGUID,TestBit)
select 99997, null, 'GUID000001' , 0 union all
select 99998, 'Bonny, Anne', 'GUID000002' , 0 union all
select 99999, 'Teach, Edward', 'GUID000003' , 0 union all
select 10000, 'Bonny, Anne', 'GUID000004' , 0 union all
select 10001, null, 'GUID000005' , 0 union all
select 10002,'Teach, Edward', 'GUID000006' , 0 union all
select 10003,null, 'GUID000007' , 0 union all
select 10004,'Kidd, William', 'GUID000008' , 0 union all
select 10005,null, 'GUID000009' , 0 union all
select 10006, 'Kidd, William', 'GUID000010' , 0
Create table #Visits
(
OrderNo int
,CrewOn datetime
,VisitType varchar (30)
)
insert into #Visits (OrderNo ,CrewOn ,VisitType)
select 10000,'2014-01-01 19:00:00','Weigh Anchor' union all
select 10001,'2014-01-01 19:01:00','Weigh Anchor' union all
select 10002,'2014-01-01 19:01:00','Weigh Anchor' union all
select 10003,'2014-01-01 19:01:00','Weigh Anchor' union all
select 10004,'2014-01-01 19:02:00','Weigh Anchor' union all
select 10003,'2014-01-05 19:01:00','Set sail' union all
select 10003,'2014-01-05 19:01:00','Splice the Mainbrace' union all
select 10003,'2014-01-05 19:01:00','Mutiny' union all
select 10003,'2014-01-10 19:01:00','Reach the Spanish Main' union all
select 10004,'2014-01-11 19:02:00','Hit submerged reef' union all
select 10004,'2014-01-11 19:02:00','Shipwrecked'
;
/*
The start of my query
*/
with times as
(
select
sv.OrderNo
,FirstVisit = min(sv.CrewOn)
,SecondVisit = (
select min(v.crewon) from #Visits v
where v.OrderNo= sv.OrderNo
and v.CrewOn > (
select min(v2.crewon) from #Visits v2 where v2.OrderNo = v.OrderNo
)
)
from #Visits sv
group by sv.OrderNo
)
,
multi as
(
select
RowNo = ROW_NUMBER () over (partition by vv.orderno order by vv.crewon)
,vv.OrderNo
,visittype = vv.VisitType
,ti.FirstVisit
,ti.SecondVisit
from #Visits vv
join times ti on vv.OrderNo = ti.OrderNo
where vv.CrewOn = ti.SecondVisit
and vv.VisitType is not null
)
,vstring as
(
select
OrderNo = m.OrderNo
,m.FirstVisit
,m.SecondVisit
,[1] = max(case when m.RowNo = 1 then m.visittype else null end)
,[2] = max(case when m.RowNo = 2 then m.visittype else null end)
,[3] = max(case when m.RowNo = 3 then m.visittype else null end)
,[4] = max(case when m.RowNo = 4 then m.visittype else null end)
from multi m
group by m.OrderNo,m.FirstVisit,m.SecondVisit
)
------Edited highlight of main query
select
so.OrderNo
,p.PirateNo
-----Where it gets weird
,Captain = ISNULL(
right(p.captain,LEN(p.captain)
-
charindex(',',p.captain)-1)
+ ' '+
left(p.captain,charindex(',',p.captain)-1)
,'None'
)
,vs.FirstVisit
,vs.SecondVisit
,SecondVisitType = 'Not the bit I''m interested in. Just here for background'-------
from
#Orders so
join #Piratespon p.UglyGUID = so.UglyGUID
left join vStringvson vs.OrderNo = so.OrderNo
where
p.TestBit = 0 ---- It works when this line is sectioned out.
and so.StatusID <> 3 ----Not Void
and so.DateOfReceipt>= '2013-12-31'
and so.DateOfReceipt <= '2013-12-31'
Drop table #Visits,#Orders,#Pirates
I've got a very odd problem with a join. The section I've marked as 'Where it gets weird' is intended to transpose the surname and forename of the captain. It works fine until I join in the vString CTE. I then get the error message below:
Invalid length parameter passed to the LEFT or SUBSTRING function.
I'm really puzzled by this. I know that I'm not accounting for the possibility that the comma is missing and that would cause that message but I'd expect that whether the vString CTE was joined or not. I have accounted for missing commas now by the way. Needless to say I can't get the problem to replicate here but I've tried to make what I've posted as close to live as possible. Can anybody shed any light on this please?
EDIT: Made more accurate
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 9, 2014 at 6:54 am
I haven't tested this but i think your issue is with the ISNULL in the weird bit. When the name is NULL you set it to None. this does not have a ',' in for the CHARINDEX to find, therefore it has nothing to pass as a parameter into the LEFT.
June 9, 2014 at 7:00 am
Is there any chance that your original query - the one with the error - had a filter for the comma in the WHERE clause? If you can answer "Yes", then there's a well-known explanation.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 9, 2014 at 7:05 am
dbalmf (6/9/2014)
I haven't tested this but i think your issue is with the ISNULL in the weird bit. When the name is NULL you set it to None. this does not have a ',' in for the CHARINDEX to find, therefore it has nothing to pass as a parameter into the LEFT.
If that was the case surely it would fall over all the time not just when the CTE was joined in. I tested it by using ISNULL(xxx,'No,ne') and it gave me the same message.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 9, 2014 at 7:08 am
ChrisM@Work (6/9/2014)
Is there any chance that your original query - the one with the error - had a filter for the comma in the WHERE clause? If you can answer "Yes", then there's a well-known explanation.
Nope, the original WHERE clause just looks for the date parameters.
I can't help thinking it's something obvious though.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 9, 2014 at 7:13 am
Maybe this code can help you. 😉
SELECT ISNULL(
REPLACE(
PARSENAME(
REPLACE(
REPLACE( Captain, '.', CHAR(7))--Change periods for something unlikely
, ', ', '.') --Change commas to periods
, 1) --Take the string on the right of the last comma (now a period)
, CHAR(7), '.') --Recover the periods
, 'None') --Validate null values
+ ISNULL( ' ' + REPLACE( PARSENAME( REPLACE( REPLACE( Captain, '.', CHAR(7)), ', ', '.'), 2), CHAR(7), '.'), '') --Same as above but with the other part of the string.
FROM #Pirates p
Note that if you have more than one comma, you'll suffer from silent truncation.
June 9, 2014 at 7:49 am
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
Is there any chance that your original query - the one with the error - had a filter for the comma in the WHERE clause? If you can answer "Yes", then there's a well-known explanation.Nope, the original WHERE clause just looks for the date parameters.
I can't help thinking it's something obvious though.
Do you still have the original query, raising the same error when it's run?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 9, 2014 at 8:41 am
ChrisM@Work (6/9/2014)
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
Is there any chance that your original query - the one with the error - had a filter for the comma in the WHERE clause? If you can answer "Yes", then there's a well-known explanation.Nope, the original WHERE clause just looks for the date parameters.
I can't help thinking it's something obvious though.
Do you still have the original query, raising the same error when it's run?
It's still returning the same error.
I didn't mention something earlier when you asked about the where clause because I'd sectioned them out by mistake! There are two clauses, one that excludes Void orders and another that excludes Test Pirates. I've changed the sample code to show this.
When I run the code with the line to exclude Test Pirates sectioned out it runs OK, when I put the line back in, it falls over. Now, there are some none-Test Pirates with Captains that don't have a comma in their name BUT they should be excluded by the Date of Receipt. I'm even more puzzled now.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 9, 2014 at 8:50 am
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
Is there any chance that your original query - the one with the error - had a filter for the comma in the WHERE clause? If you can answer "Yes", then there's a well-known explanation.Nope, the original WHERE clause just looks for the date parameters.
I can't help thinking it's something obvious though.
Do you still have the original query, raising the same error when it's run?
It's still returning the same error.
I didn't mention something earlier when you asked about the where clause because I'd sectioned them out by mistake! There are two clauses, one that excludes Void orders and another that excludes Test Pirates. I've changed the sample code to show this.
When I run the code with the line to exclude Test Pirates sectioned out it runs OK, when I put the line back in, it falls over. Now, there are some none-Test Pirates with Captains that don't have a comma in their name BUT they should be excluded by the Date of Receipt. I'm even more puzzled now.
Enlightenment moment imminent.
SQL Server will shove predicates around if it can, attempting to apply expensive filters to as few rows as possible. It's quite likely that the LEFT expression was evaluated before the date exclusion. Here's the best bit: any change to the plan can change the order in which the predicates are evaluated. In other words, the plan for the query without the CTE could evaluate the date filter before the LEFT() expression. Adding the CTE to the query could prompt SQL Server to choose a plan where the predicates are evaluated in the reverse order, giving rise to the error when commaless captains exist in the data.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 9, 2014 at 8:55 am
ChrisM@Work (6/9/2014)
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
Is there any chance that your original query - the one with the error - had a filter for the comma in the WHERE clause? If you can answer "Yes", then there's a well-known explanation.Nope, the original WHERE clause just looks for the date parameters.
I can't help thinking it's something obvious though.
Do you still have the original query, raising the same error when it's run?
It's still returning the same error.
I didn't mention something earlier when you asked about the where clause because I'd sectioned them out by mistake! There are two clauses, one that excludes Void orders and another that excludes Test Pirates. I've changed the sample code to show this.
When I run the code with the line to exclude Test Pirates sectioned out it runs OK, when I put the line back in, it falls over. Now, there are some none-Test Pirates with Captains that don't have a comma in their name BUT they should be excluded by the Date of Receipt. I'm even more puzzled now.
Enlightenment moment imminent.
SQL Server will shove predicates around if it can, attempting to apply expensive filters to as few rows as possible. It's quite likely that the LEFT expression was evaluated before the date exclusion. Here's the best bit: any change to the plan can change the order in which the predicates are evaluated. In other words, the plan for the query without the CTE could evaluate the date filter before the LEFT() expression. Adding the CTE to the query could prompt SQL Server to choose a plan where the predicates are evaluated in the reverse order, giving rise to the error when commaless captains exist in the data.
I seeeeeee 🙂 So it wasn't necessarily a problem with what I'd originally done then?
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 9, 2014 at 9:27 am
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
Is there any chance that your original query - the one with the error - had a filter for the comma in the WHERE clause? If you can answer "Yes", then there's a well-known explanation.Nope, the original WHERE clause just looks for the date parameters.
I can't help thinking it's something obvious though.
Do you still have the original query, raising the same error when it's run?
It's still returning the same error.
I didn't mention something earlier when you asked about the where clause because I'd sectioned them out by mistake! There are two clauses, one that excludes Void orders and another that excludes Test Pirates. I've changed the sample code to show this.
When I run the code with the line to exclude Test Pirates sectioned out it runs OK, when I put the line back in, it falls over. Now, there are some none-Test Pirates with Captains that don't have a comma in their name BUT they should be excluded by the Date of Receipt. I'm even more puzzled now.
Enlightenment moment imminent.
SQL Server will shove predicates around if it can, attempting to apply expensive filters to as few rows as possible. It's quite likely that the LEFT expression was evaluated before the date exclusion. Here's the best bit: any change to the plan can change the order in which the predicates are evaluated. In other words, the plan for the query without the CTE could evaluate the date filter before the LEFT() expression. Adding the CTE to the query could prompt SQL Server to choose a plan where the predicates are evaluated in the reverse order, giving rise to the error when commaless captains exist in the data.
I seeeeeee 🙂 So it wasn't necessarily a problem with what I'd originally done then?
Yes and no. It's a feature which you will benefit from understanding.
Two catch-phrases you can use to find out more:
“Predicate pushdown” http://www.sqlskills.com/blogs/conor/how-to-write-non-join-where-clauses/
and “deferred expression evaluation”.
In practice, it’s sufficient to remember that if you have an expression in your output list which is dependent upon a filter in your WHERE clause, then your query is unstable and likely to fail. Unstable in this context means that although it might work today, quite a lot of factors could cause it to fail in the future. Anything which would trigger a recompile, for instance.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 10, 2014 at 1:04 am
Thanks Chris, I'll definitely look that up and it's certainly something to bear in mind for the future. I think both my colleague and I learned something there. At least it wasn't a missing set of brackets or something very obvious though.
How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537
June 13, 2014 at 9:42 am
ChrisM@Work (6/9/2014)
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
BWFC (6/9/2014)
ChrisM@Work (6/9/2014)
Is there any chance that your original query - the one with the error - had a filter for the comma in the WHERE clause? If you can answer "Yes", then there's a well-known explanation.Nope, the original WHERE clause just looks for the date parameters.
I can't help thinking it's something obvious though.
Do you still have the original query, raising the same error when it's run?
It's still returning the same error.
I didn't mention something earlier when you asked about the where clause because I'd sectioned them out by mistake! There are two clauses, one that excludes Void orders and another that excludes Test Pirates. I've changed the sample code to show this.
When I run the code with the line to exclude Test Pirates sectioned out it runs OK, when I put the line back in, it falls over. Now, there are some none-Test Pirates with Captains that don't have a comma in their name BUT they should be excluded by the Date of Receipt. I'm even more puzzled now.
Enlightenment moment imminent.
SQL Server will shove predicates around if it can, attempting to apply expensive filters to as few rows as possible. It's quite likely that the LEFT expression was evaluated before the date exclusion. Here's the best bit: any change to the plan can change the order in which the predicates are evaluated. In other words, the plan for the query without the CTE could evaluate the date filter before the LEFT() expression. Adding the CTE to the query could prompt SQL Server to choose a plan where the predicates are evaluated in the reverse order, giving rise to the error when commaless captains exist in the data.
I seeeeeee 🙂 So it wasn't necessarily a problem with what I'd originally done then?
Yes and no. It's a feature which you will benefit from understanding.
Two catch-phrases you can use to find out more:
“Predicate pushdown” http://www.sqlskills.com/blogs/conor/how-to-write-non-join-where-clauses/
and “deferred expression evaluation”.
In practice, it’s sufficient to remember that if you have an expression in your output list which is dependent upon a filter in your WHERE clause, then your query is unstable and likely to fail. Unstable in this context means that although it might work today, quite a lot of factors could cause it to fail in the future. Anything which would trigger a recompile, for instance.
I just flashed back to every time I wrote a join like that, hit F5 a dozen times, and wondered why I got 0 rows back.
Thank you for the new brain wrinkle.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply