May 17, 2017 at 8:58 am
I created a large temp table and I'm able to query it just fine until adding a CASE statement, which guarantees it to fail. I have not been able to find anything online. Even passing a small (2K) rowset to another temp table and using this case statement will cause the problem.
Msg 0, Level 20, State 0, Line 207
A severe error occurred on the current command. The results, if any, should be discarded.
The case statement is:
, case when (select min(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2016-10-01' and '2016-10-03' then 1
when (select min(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-04-29' and '2017-05-01' then 1
when (select min(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-06-03' and '2016-06-05' then 1
when (select max(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2016-10-01' and '2016-10-03' then 1
when (select max(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-04-29' and '2017-05-01' then 1
when (select max(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-06-03' and '2016-06-05' then 1
else 0 end tied2conv
May 17, 2017 at 9:11 am
Express12 - Wednesday, May 17, 2017 8:58 AMI created a large temp table and I'm able to query it just fine until adding a CASE statement, which guarantees it to fail. I have not been able to find anything online. Even passing a small (2K) rowset to another temp table and using this case statement will cause the problem.
Msg 0, Level 20, State 0, Line 207
A severe error occurred on the current command. The results, if any, should be discarded.The case statement is:
, case when (select min(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2016-10-01' and '2016-10-03' then 1
when (select min(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-04-29' and '2017-05-01' then 1
when (select min(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-06-03' and '2016-06-05' then 1
when (select max(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2016-10-01' and '2016-10-03' then 1
when (select max(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-04-29' and '2017-05-01' then 1
when (select max(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-06-03' and '2016-06-05' then 1
else 0 end tied2conv
Works fine for meSELECT case
when (select min(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2016-10-01' and '2016-10-03' then 1
when (select min(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-04-29' and '2017-05-01' then 1
when (select min(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-06-03' and '2016-06-05' then 1
when (select max(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2016-10-01' and '2016-10-03' then 1
when (select max(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-04-29' and '2017-05-01' then 1
when (select max(d) from (values(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)) between '2017-06-03' and '2016-06-05' then 1
else 0 end tied2conv
FROM (
SELECT
complete_date = GETDATE(),
create_date = GETDATE(),
dispatch_date = GETDATE(),
schedule_date = GETDATE()
) x
But why on Earth write something so inefficient?SELECT CASE
when y.min_d between '2016-10-01' and '2016-10-03' then 1
when y.min_d between '2017-04-29' and '2017-05-01' then 1
when y.min_d between '2017-06-03' and '2016-06-05' then 1
when y.max_d between '2016-10-01' and '2016-10-03' then 1
when y.max_d between '2017-04-29' and '2017-05-01' then 1
when y.max_d between '2017-06-03' and '2016-06-05' then 1
else 0 end tied2conv
FROM (
SELECT
complete_date = GETDATE(),
create_date = GETDATE(),
dispatch_date = GETDATE(),
schedule_date = GETDATE()
) x
CROSS APPLY (
SELECT
min_d = min(d),
max_d = max(d)
FROM (VALUES(x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) as f(d)
) y
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
May 17, 2017 at 9:31 am
SELECT y.min_d, y.max_d, CASE
when y.min_d between '2016-10-01' and '2016-10-03' then 1
when y.min_d between '2017-04-29' and '2017-05-01' then 1
when y.min_d between '2017-06-03' and '2016-06-05' then 1 -- ??
when y.max_d between '2016-10-01' and '2016-10-03' then 1
when y.max_d between '2017-04-29' and '2017-05-01' then 1
when y.max_d between '2017-06-03' and '2016-06-05' then 1 -- ??
ELSE 0 END tied2conv
FROM (VALUES
(GETDATE(), GETDATE(), GETDATE(), GETDATE()),
(GETDATE()-18, GETDATE(), GETDATE(), GETDATE())
) x (complete_date, create_date, dispatch_date, schedule_date)
CROSS APPLY (
SELECT
min_d = min(d),
max_d = max(d)
FROM (VALUES (x.complete_date),(x.create_date),(x.dispatch_date),(x.schedule_date)) f (d)
) yFor 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
May 17, 2017 at 11:02 am
Chris - thank you! for taking the time to review this SQL. I've passed your info back to my colleague for review. This is really helpful, thanks again
May 21, 2017 at 7:09 am
Probably an SQL Server bug, no "regular" queries should raise that error, if you don't find it in microsoft connect, you should try to recreate with some sample data and submit a bug report.
With that said, i wasn't able to recreate your problem, perhaps you should post a @@Version of your enviroment, or perhaps the problem isn't in the CASE WHEN per se
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply