December 14, 2016 at 1:36 pm
Here's the Query:
SELECT
[description],
CASE WHEN startDate IS NULL
THEN
CASE WHEN endDate IS NULL
THEN '(''' + [description] + ''',''' + 'NULL' + ''',' + '''' + 'NULL' + ''',''' + code + ''')'
ELSE '(''' + [description] + ''',''' + 'NULL' + ''',' + '''' + CONVERT(VARCHAR(8), enddate, 112) + ''',''' + code + ''')'
END
ELSE
'(''' + [description] + ''',''' + CONVERT(VARCHAR(8), startdate, 112) + ''',' + '''' + CONVERT(VARCHAR(8), enddate, 112) + ''',''' + code + ''')'
END
FROM POS_CODEVALUE WHERE CODETABLE_FK = 12
I was thinking the nested CASE Statements would account for the NULL values within start/end date - but it's not. During the conversion, any record which has a NULL in those dates (in this instance, End Date) returns the full string as NULL.
If we use these following scripts:
DECLARE @TESTDATE DATETIME;
SET @TESTDATE = GETDATE();
SELECT 'TEST-' + CONVERT(VARCHAR(8),@TESTDATE,112)
GO
DECLARE @TESTDATE DATETIME;
SET @TESTDATE = NULL;
SELECT 'TEST-' + CONVERT(VARCHAR(8),@TESTDATE,112)
GO
DECLARE @TESTDATE DATETIME;
SET @TESTDATE = NULL;
SELECT
CASE
WHEN @TESTDATE IS NULL
THEN 'TEST-' + 'NULL'
ELSE
'TEST-' + CONVERT(VARCHAR(8),@TESTDATE,112)
END AS testconvertDATE
GO
DECLARE @TESTDATE DATETIME;
DECLARE @TESTENDDATE DATETIME;
SET @TESTDATE = NULL;
SET @TESTENDDATE = NULL;
SELECT
CASE
WHEN @TESTDATE IS NULL
THEN
CASE
WHEN @TESTENDDATE IS NULL
THEN 'TEST-' + 'NULL'
ELSE 'TESTDATE IS NULL, BUT TESTENDDATE IS NOT NULL'
END
ELSE 'TESTDATE IS NOT NULL, AND TESTENDDATE IS NOT NULL'
END AS testconvertDATE
GO
We can see that the nested CASE logic SHOULD work.
I'm not sure what I'm missing, can anyone identify where my logic is flawed?
December 14, 2016 at 1:49 pm
Doug.F (12/14/2016)
Here's the Query:
SELECT[description],
CASE WHEN startDate IS NULL
THEN
CASE WHEN endDate IS NULL
THEN '(''' + [description] + ''',''' + 'NULL' + ''',' + '''' + 'NULL' + ''',''' + code + ''')'
ELSE '(''' + [description] + ''',''' + 'NULL' + ''',' + '''' + CONVERT(VARCHAR(8), enddate, 112) + ''',''' + code + ''')'
END
ELSE
'(''' + [description] + ''',''' + CONVERT(VARCHAR(8), startdate, 112) + ''',' + '''' + CONVERT(VARCHAR(8), enddate, 112) + ''',''' + code + ''')'
END
FROM POS_CODEVALUE WHERE CODETABLE_FK = 12
I was thinking the nested CASE Statements would account for the NULL values within start/end date - but it's not. During the conversion, any record which has a NULL in those dates (in this instance, End Date) returns the full string as NULL.
If we use these following scripts:
DECLARE @TESTDATE DATETIME;SET @TESTDATE = GETDATE();
SELECT 'TEST-' + CONVERT(VARCHAR(8),@TESTDATE,112)
GO
DECLARE @TESTDATE DATETIME;
SET @TESTDATE = NULL;
SELECT 'TEST-' + CONVERT(VARCHAR(8),@TESTDATE,112)
GO
DECLARE @TESTDATE DATETIME;
SET @TESTDATE = NULL;
SELECT
CASE
WHEN @TESTDATE IS NULL
THEN 'TEST-' + 'NULL'
ELSE
'TEST-' + CONVERT(VARCHAR(8),@TESTDATE,112)
END AS testconvertDATE
GO
DECLARE @TESTDATE DATETIME;
DECLARE @TESTENDDATE DATETIME;
SET @TESTDATE = NULL;
SET @TESTENDDATE = NULL;
SELECT
CASE
WHEN @TESTDATE IS NULL
THEN
CASE
WHEN @TESTENDDATE IS NULL
THEN 'TEST-' + 'NULL'
ELSE 'TESTDATE IS NULL, BUT TESTENDDATE IS NOT NULL'
END
ELSE 'TESTDATE IS NOT NULL, AND TESTENDDATE IS NOT NULL'
END AS testconvertDATE
GO
We can see that the nested CASE logic SHOULD work.
I'm not sure what I'm missing, can anyone identify where my logic is flawed?
Follow your ELSE when startDate is not null. You aren't testing for a null endDate in that path.
December 14, 2016 at 1:52 pm
The problem is that when StartDate is not null, you convert EndDate to VARCHAR and then concatenate it with a bunch of strings without checking whether it is null or not.
When StartDate is not null and EndDate is null, you'll get the result you're describing.
This shows up even in your test script, where the second SELECT returns a NULL.
Since it looks like you're not actually changing the structure of the concatenated string in any of the result expressions, it might make more sense to get rid of the CASE expressions and just use ISNULL or COALESCE.
Something like this:
SELECT '('''+[description]+''','''+ISNULL(CONVERT(VARCHAR(8), startDate, 112),'NULL')+''','''+ISNULL(CONVERT(VARCHAR(8), startDate, 112),'NULL')+''','''+code+''')'
FROM pos_codevalue;
Cheers!
EDIT: Lynn beat me to it 🙂
December 14, 2016 at 2:00 pm
This works:
DECLARE @startDate DATETIME,
@endDate DATETIME,
@Description VARCHAR(10),
@Code VARCHAR(10);
SET @startDate = NULL;
SET @endDate = NULL;
SET @Description = 'TEST';
SET @Code = 'code';
SELECT @Description + ISNULL(',' + CONVERT(VARCHAR(8),@startDate,112),',NULL') + ISNULL(',' + CONVERT(VARCHAR(8),@endDate,112),',NULL') + ',' + @Code;
SET @startDate = DATEADD(DAY,-1,GETDATE());
SET @endDate = NULL;
SET @Description = 'TEST';
SET @Code = 'code';
SELECT @Description + ISNULL(',' + CONVERT(VARCHAR(8),@startDate,112),',NULL') + ISNULL(',' + CONVERT(VARCHAR(8),@endDate,112),',NULL') + ',' + @Code;
SET @startDate = DATEADD(DAY,-1,GETDATE());
SET @endDate = GETDATE();
SET @Description = 'TEST';
SET @Code = 'code';
SELECT @Description + ISNULL(',' + CONVERT(VARCHAR(8),@startDate,112),',NULL') + ISNULL(',' + CONVERT(VARCHAR(8),@endDate,112),',NULL') + ',' + @Code;
SET @startDate = NULL;
SET @endDate = GETDATE();
SET @Description = 'TEST';
SET @Code = 'code';
SELECT @Description + ISNULL(',' + CONVERT(VARCHAR(8),@startDate,112),',NULL') + ISNULL(',' + CONVERT(VARCHAR(8),@endDate,112),',NULL') + ',' + @Code;
December 14, 2016 at 2:02 pm
Jacob Wilkins (12/14/2016)
The problem is that when StartDate is not null, you convert EndDate to VARCHAR and then concatenate it with a bunch of strings without checking whether it is null or not.When StartDate is not null and EndDate is null, you'll get the result you're describing.
This shows up even in your test script, where the second SELECT returns a NULL.
Cheers!
EDIT: Lynn beat me to it 🙂
Actually, if startDate is not null and endDate is null you get null.
Misread the quote, sorry.
December 14, 2016 at 2:03 pm
I would just handle everything with ISNULLs and forget about CASE.
CREATE TABLE POS_CODEVALUE(
code char(3),
CODETABLE_FK int,
[description] varchar(50),
startDate datetime,
enddate datetime
);
INSERT INTO POS_CODEVALUE
VALUES
('001', 12, 'both dates null', null, null),
('002', 12, 'dates not null', '20160101', '20161231'),
('003', 12, 'start date null', '20160101', null),
('004', 12, 'end date null', null, '20161231');
SELECT
[description],
CASE WHEN startDate IS NULL
THEN
CASE WHEN endDate IS NULL
THEN '(''' + [description] + ''',''' + 'NULL' + ''',' + '''' + 'NULL' + ''',''' + code + ''')'
ELSE '(''' + [description] + ''',''' + 'NULL' + ''',' + '''' + CONVERT(VARCHAR(8), enddate, 112) + ''',''' + code + ''')'
END
ELSE
'(''' + [description] + ''',''' + CONVERT(VARCHAR(8), startdate, 112) + ''',' + '''' + CONVERT(VARCHAR(8), enddate, 112) + ''',''' + code + ''')'
END,
'(''' + [description] + ''','
+ ISNULL( QUOTENAME(CONVERT(CHAR(8), startDate, 112), ''''), 'NULL') + ','
+ ISNULL( QUOTENAME(CONVERT(CHAR(8), enddate, 112), ''''), 'NULL') + ''','''
+ code + ''')'
FROM POS_CODEVALUE WHERE CODETABLE_FK = 12;
GO
DROP TABLE POS_CODEVALUE;
December 14, 2016 at 2:05 pm
Lynn Pettis (12/14/2016)
Jacob Wilkins (12/14/2016)
The problem is that when StartDate is not null, you convert EndDate to VARCHAR and then concatenate it with a bunch of strings without checking whether it is null or not.When StartDate is not null and EndDate is null, you'll get the result you're describing.
This shows up even in your test script, where the second SELECT returns a NULL.
Cheers!
EDIT: Lynn beat me to it 🙂
Actually, if startDate is not null and endDate is null you get null.
Yes, which is the result he's describing...I must be missing the alleged disconnect 🙂
EDIT: Darn, and now Luis beats me to the recommendation to get rid of CASE. Getting old and slow... 🙂
December 14, 2016 at 2:07 pm
Jacob Wilkins (12/14/2016)
Lynn Pettis (12/14/2016)
Jacob Wilkins (12/14/2016)
The problem is that when StartDate is not null, you convert EndDate to VARCHAR and then concatenate it with a bunch of strings without checking whether it is null or not.When StartDate is not null and EndDate is null, you'll get the result you're describing.
This shows up even in your test script, where the second SELECT returns a NULL.
Cheers!
EDIT: Lynn beat me to it 🙂
Actually, if startDate is not null and endDate is null you get null.
Yes, which is the result he's describing...I must be missing the alleged disconnect 🙂
EDIT: Darn, and now Luis beats me to the recommendation to get rid of CASE. Getting old and slow... 🙂
Actually, I beat him and the quote bug hit me updating my post when I realized I misread what you wrote.
I just wrote simple code to demonstrate the use of ISNULL.
December 14, 2016 at 2:09 pm
Well, the fact that you beat him doesn't change the fact that he beat me 🙂
Just means two people beat me, so I'm even older and slower than I feared.
Cheers!
December 14, 2016 at 2:11 pm
Jacob Wilkins (12/14/2016)
Well, the fact that you beat him doesn't change the fact that he beat me 🙂Just means two people beat me, so I'm even older and slower than I feared.
Cheers!
Not necessarily, being older I mean. Where were you when the original Star Wars hit the theaters?
December 14, 2016 at 2:15 pm
Oh, I wasn't even any kind of thought at that point.
It's less the number of earth-round-the-sun circuits completed and more my lack of any sort of graceful biological aging that concerns me 🙂
Cheers!
December 14, 2016 at 2:18 pm
Jacob Wilkins (12/14/2016)
Oh, I wasn't even any kind of thought at that point.It's less the number of earth-round-the-sun circuits completed and more my lack of any sort of graceful biological aging that concerns me 🙂
Cheers!
I saw it while in California visiting a friend after graduating high school, therefore you aren't old.
December 14, 2016 at 2:25 pm
I'll offer a "template" approach just as a possible alternative. Often I find it easier because I get a better visualization of the desired result up front than from concatenating all results, and less of the formatting is repeated, which can cause errors if the output pattern is changed.
SELECT
[description],
REPLACE(REPLACE(REPLACE('(''$description$'', ''$startDate$'', ''$endDate$'')',
'$description$', ISNULL([description], 'NULL')),
'$startDate$', ISNULL(CONVERT(VARCHAR(8), startDate, 112), 'NULL')),
'$endDate$', ISNULL(CONVERT(VARCHAR(8), endDate, 112), 'NULL'))
FROM dbo.POS_CODEVALUE
WHERE CODETABLE_FK = 12;
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".
December 14, 2016 at 2:29 pm
This is what make SSC so great. The multiple solutions offered for a single problem.
December 14, 2016 at 2:38 pm
Just a word of caution.
Handle the quotes correctly on Scott's and Lynn's examples. You might end with strings with the value 'NULL' instead of NULLs for the columns. Or you could end with an arithmetic overflow from converting int to datetime.
If you're inserting to a varchar column, you won't get errors. But if you're using proper data types, the code will fail.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply