Inserting into Temp Table CASE Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Inserting into Temp Table CASE Statement

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2009-06-02 : 11:45:13

I am trying to insert into the #bindAndUnbind table and I want the third column (finalamount) to be the value from the case statement. How can I do this?

insert into #bindAndUnbind(facility,description,finalamount)
select facilityid, description,
(select case when reservequantity > onhandquantity then (sum(reservequantity * onhandquantity))
else sum(reservequantity * amount)
end from #bindTable)
from facilities

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2009-06-02 : 11:46:51

Here is my updated query:

insert into #bindAndUnbind(facility,description,finalamount)
select facilityid, description,
(select case when reservequantity > onhandquantity then
sum(reservequantity * onhandquantity)
else sum(reservequantity * amount)
end from #bindTable group by reservequantity, onhandquantity)
from facilities

Mohammad Azam
www.azamsharp.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:00:29

how are #bindTable and facilities related? as of above, it gives same value for finalamount for all records as there's no connection

azamsharp
Posting Yak Master

201 Posts

Posted - 2009-06-02 : 12:02:52

There is no relation:

create table facilities
(
facilityid int identity(1,1),
description varchar(200)

)

create table #bindTable
(
ReserveQuantity int,
OnHandQuantity int,
Amount int
)

create table #bindAndUnbind
(
Facility int,
Description varchar(200),
FinalAmount int
)

Mohammad Azam
www.azamsharp.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:03:09

anyways correct form of posted query is:-


insert into #bindAndUnbind(facility,description,finalamount) 
select facilityid, description, 
(select 
sum(reservequantity * case when reservequantity > onhandquantity then onhandquantity else amount end) 
 from #bindTable group by reservequantity, onhandquantity)
from facilities

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:04:11

quote:
Originally posted by azamsharp

There is no relation:

create table facilities
(
facilityid int identity(1,1),
description varchar(200)

)

create table #bindTable
(
ReserveQuantity int,
OnHandQuantity int,
Amount int
)

create table #bindAndUnbind
(
Facility int,
Description varchar(200),
FinalAmount int
)

Mohammad Azam
www.azamsharp.net

so you want just the sum of all those record products from #bindTable along with each record of facilities ?

azamsharp
Posting Yak Master

201 Posts

Posted - 2009-06-02 : 12:04:53

I ran the query and got the following result:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

Mohammad Azam
www.azamsharp.net

azamsharp
Posting Yak Master

201 Posts

Posted - 2009-06-02 : 12:05:29

>> so you want just the sum of all those record products from #bindTable along with each record of facilities ?

yes sir!

Mohammad Azam
www.azamsharp.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:07:21

quote:
Originally posted by azamsharp

>> so you want just the sum of all those record products from #bindTable along with each record of facilities ?

yes sir!

Mohammad Azam
www.azamsharp.net

then it should be


insert into #bindAndUnbind(facility,description,finalamount) 
select facilityid, description, 
(select 
sum(reservequantity * case when reservequantity > onhandquantity then onhandquantity else amount end) 
 from #bindTable)
from facilities

azamsharp
Posting Yak Master

201 Posts

Posted - 2009-06-02 : 12:10:51

Thanks! Can I write as a different case statement. I mean something like this:

case when reservequantity > onhandquantity then sum(reservequantity * onhandquantity)
else sum(reservequantity * amount) end

Mohammad Azam
www.azamsharp.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-02 : 12:16:42

quote:
Originally posted by azamsharp

Thanks! Can I write as a different case statement. I mean something like this:

case when reservequantity > onhandquantity then sum(reservequantity * onhandquantity)
else sum(reservequantity * amount) end

Mohammad Azam
www.azamsharp.net

why repeat sum()operator? is it better to do summation only once?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources