Not getting values in temp table

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)

Not getting values in temp table

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-11-29 : 12:18:07

Trying to update a temp table but not getting any values inserted into table. The code work when I do insert.

update     #ReceiptTable
set        #ReceiptTable.grossqtythismonth = grossqtythismonth,
           #ReceiptTable.netqtythismonth   = netqtythismonth 
select     SUM(CASE WHEN i.mode = 'J' and p.petroEXcode = 'Y2' THEN convert(decimal(15,2),i.gross) ELSE convert(decimal(15,2),i.gross) * 42 END) as grossqtythismonth,
           SUM(CASE WHEN i.mode = 'J' and p.petroEXcode = 'Y2'THEN convert(decimal(15,2),i.net) ELSE convert(decimal(15,2),i.net) * 42 END) as netqtythismonth
from       [SFM-TP6000-1].TP6000.dbo.Product as p
inner join [SFM-TP6000-1].TP6000.dbo.TankArchive as t on t.basecode = p.base 
inner join [SFM-TP6000-1].TP6000.dbo.Inventory as i on (i.tank = t.tank and
                                                        i.period = t.period)
where      i.TicketNo <> ' ' and 
           i.Carrier <> ' '  and
           i.Type = '4' and
           i.NEWDATE >= @DateFromCurrMonth and
           i.NEWDATE < @DateToCurrMonth

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 12:24:28

There doesn't seem to be a join to the temp table.
In fact it's two statements - an update followed by a select - the update is setting columns to the same value so no effect.
The select will return a resultset.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 12:30:15

maybe

update #ReceiptTable
set #ReceiptTable.grossqtythismonth = a.grossqtythismonth,
#ReceiptTable.netqtythismonth = a.netqtythismonth
from #ReceiptTable tmp
join
(
select SUM(CASE WHEN i.mode = 'J' and p.petroEXcode = 'Y2' THEN convert(decimal(15,2),i.gross) ELSE convert(decimal(15,2),i.gross) * 42 END) as grossqtythismonth,
SUM(CASE WHEN i.mode = 'J' and p.petroEXcode = 'Y2'THEN convert(decimal(15,2),i.net) ELSE convert(decimal(15,2),i.net) * 42 END) as netqtythismonth
from [SFM-TP6000-1].TP6000.dbo.Product as p
inner join [SFM-TP6000-1].TP6000.dbo.TankArchive as t on t.basecode = p.base
inner join [SFM-TP6000-1].TP6000.dbo.Inventory as i on (i.tank = t.tank and
i.period = t.period)
where i.TicketNo <> ' ' and
i.Carrier <> ' ' and
i.Type = '4' and
i.NEWDATE >= @DateFromCurrMonth and
i.NEWDATE < @DateToCurrMonth
) a
on tmp.DateFromCurrMonth = @DateFromCurrMonth
and tmp.DateToCurrMonth = @DateToCurrMonth

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources