I am running into an error when writing these multiple 'case when' statements in Excel VBA. The scripting runs perfectly in SQL Server, but I am having trouble formatting it correctly in Excel VBA so that it can run correctly. Please help?!
6
-
3How exactly did you try this in VBA? Please edit your question.BigBen– BigBen2020-06-10 13:58:01 +00:00Commented Jun 10, 2020 at 13:58
-
1Instead, try creating a Tally table and joining on it. Who would really write a case statement like that.Cetin Basoz– Cetin Basoz2020-06-10 14:03:34 +00:00Commented Jun 10, 2020 at 14:03
-
And BTW, if all you are bothered is how to write that in VBA, then try writing it as multiple strings no longer than 255 characters and concatenate. ie: dim sql as string = "select" & _ " case when ..." & _ " when ...".Cetin Basoz– Cetin Basoz2020-06-10 14:09:25 +00:00Commented Jun 10, 2020 at 14:09
-
@CetinBasoz can you elaborate a little more? I attempted to concatenate, but I think may have done it incorrectly.jb_swole2013– jb_swole20132020-06-10 14:42:52 +00:00Commented Jun 10, 2020 at 14:42
-
@CetinBasoz could you start it off for me? I keep getting error messages. I must be missing something here.jb_swole2013– jb_swole20132020-06-11 10:48:54 +00:00Commented Jun 11, 2020 at 10:48
|
Show 1 more comment
1 Answer
This is how you would write a long string in VBA:
StrQuery = "Select" & _
" case when srvprovid like '01%' then 'AL'" & _
" WHEN srvprovid like '02%' then 'AK'" & _
" WHEN srvprovid like '03%' then 'AR'" & _
" ..."
And this is what I suggest you to do instead (just a sample showing the idea, didn't write full code):
DECLARE @sample TABLE (srvProvId VARCHAR(10), id INT IDENTITY);
INSERT INTO @sample (srvProvId) VALUES
('010'),
('01'),
('020'),
('030'),
('040');
DECLARE @states VARCHAR(1000) = 'AL,AK,AR,AZ,CA,CO,CT'; -- DE,DC,FL...
DECLARE @tally TABLE (id INT IDENTITY, abrv CHAR(2));
INSERT @tally (abrv)
SELECT value FROM STRING_SPLIT(@states, ',') ;
SELECT *
FROM @sample t1
LEFT JOIN @tally t2 ON t1.srvProvId LIKE RIGHT('0'+CAST(t2.id AS VARCHAR(2)),2)+'%';
