October 24, 2019 at 3:20 pm
I have a simple case: I have to assign value to a variable depending on type.
Here is my simplified table:
create table #t1
(type int,
amt int)
insert into #t1 values
(1,877),
(2,922)
go
This is what I am trying to do:
declare
@v1 int,
@v2 int
select @v1 = case when type = 1 then amt end,
@v2 = case when type = 2 then amt end
from #t1
print @v1
print @v2
But it assigns only one value, not both. Am I doing something wrong?
Thanks
October 24, 2019 at 3:28 pm
There are two rows in your table, yet you are trying to assign single values to scalar variables by selecting from that table. You cannot condense multiple values from your source table in this way.
What do you want your resultset to look like?
This works, for example ... is it what you are after?
SELECT t.type
,t.amt
,v1 = IIF(t.type = 1, t.amt, NULL)
,v2 = IIF(t.type = 2, t.amt, NULL)
FROM #t1 t;
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 24, 2019 at 3:40 pm
My goal is not a resultset.
What I have now is two queries which separately assign values to two single variables:
select @v1 = amt
from #t1
where type = 1;
select @v2 = amt
from #t1
where type = 2;
print @v1
print @v2
And I wanted to combine them into one.
October 24, 2019 at 3:51 pm
I think something like this is what you need:
select @v1 = max(case when type = 1 then amt end),
@v2 = max(case when type = 2 then amt end)
from #t1
print @v1
print @v2
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".
October 24, 2019 at 3:59 pm
No, it is assigning BOTH. It assigns values based on the last row processed. (I believe it's also smart enough to know it only needs to process one row.) One of the values it assigns is NULL, because you haven't supplied an ELSE clause in your CASE expression. What you want is.
declare
@v1 int,
@v2 int
select @v1 = MAX(case when type = 1 then amt end),
@v2 = MAX(case when type = 2 then amt end)
from #t1
print @v1
print @v2
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 24, 2019 at 6:01 pm
Thanks both, it worked.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply