SQL Update The multi-part identifier "sa.SMFixed" could not be bound.

Question

Post reply

SQL Update The multi-part identifier "sa.SMFixed" could not be bound.

tinku99

Valued Member

Points: 67
More actions
May 27, 2010 at 3:29 pm

#219521

Create proc p_soallocate( @FiscalYear int, @driverTimeFrameVal varchar(3), @Periodvalue Varchar(3))
as
Begin
CREATE TABLE #AllocationRecords
(
AllocationType VARCHAR(15),
Division VARCHAR(10),
BodyType VARCHAR(10),
CostType VARCHAR(10),
ProductLine VARCHAR(10),
totalValue numeric(15,2),
disbursementValue numeric(15,2),
)
INSERT INTO #AllocationRecords (AllocationType, Division, BodyType,CostType,ProductLine,totalValue)
SELECT 'S&M', gla.catcodea, gla.catcodeb, gla.catcodec, gla.catcoded,
CASE am.ReverseSign
WHEN 1 THEN
CASE @periodValue
WHEN '1' THEN sum(period01) * -1
WHEN '2' THEN sum(period02) * -1
WHEN '3' THEN sum(period03) * -1
WHEN '4' THEN sum(period04) * -1
WHEN '5' THEN sum(period05) * -1
WHEN '6' THEN sum(period06) * -1
WHEN '7' THEN sum(period07) * -1
WHEN '8' THEN sum(period08) * -1
WHEN '9' THEN sum(period09) * -1
WHEN '10' THEN sum(period10) * -1
WHEN '11' THEN sum(period11) * -1
WHEN '12' THEN sum(period12) * -1
END
ELSE
CASE @periodValue
WHEN '1' THEN sum(period01)
WHEN '2' THEN sum(period02)
WHEN '3' THEN sum(period03)
WHEN '4' THEN sum(period04)
WHEN '5' THEN sum(period05)
WHEN '6' THEN sum(period06)
WHEN '7' THEN sum(period07)
WHEN '8' THEN sum(period08)
WHEN '9' THEN sum(period09)
WHEN '10' THEN sum(period10)
WHEN '11' THEN sum(period11)
WHEN '12' THEN sum(period12)
END
END
FROM AccountMaster am, GLAllocation gla, SOStagingCategoryLookup sscl,SOStagingTable sos
WHERE
am.AccountID = gla.AccountID AND
gla.DriverTimeFrame = @driverTimeFrameVal AND
am.SOStagingCategory = sscl.ID AND
sscl.Code = 'S&M' AND
gla.Tier = 4 AND
gla.FiscalYear = @fiscalYear AND
sos.WgAmp = gla.catcodea
GROUP BY am.ReverseSign, gla.catcodea, gla.catcodeb, gla.CatCodec,gla.catcoded
Update #AllocationRecords
SET disbursementValue=totalvalue
UPDATE SOAllocation
SET sa.SMFixed = (ar.disbursementValue/(Select c.counts
FROM temp1 c
Where c.counts=114))
From
SOAllocation sa INNER JOIN
SOStagingTable ss ON sa.SO = ss.SO AND sa.FiscalYear = ss.FiscalYear
AND sa.DriverTimeFrame = ss.DriverTimeFrame AND sa.Period = ss.Period INNER JOIN
ItemCrossReference itm ON ss.SecondItem = itm.ItemNumber INNER JOIN
#AllocationRecords ar ON itm.FGType = ar.BodyType AND sa.WgAmp = ar.Division, temp1 c
WHERE
ar.Division = 'WG' AND
ar.AllocationType = 'S&M' AND
ar.CostType = 'FIX' AND
ss.FiscalYear = @fiscalYear AND
ss.period = @periodvalue
Drop table #AllocationRecords
End
When try to execute this I am getting error as The multi-part identifier "sa.SMFixed" could not be bound. inputs are 9, p01, p01

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Can't see what you see from here. Could you post the DDL for your tables?

Atif-ullah Sheikh SSChampion Points: 12495 More actions · Answer 2

I think problem is here;

UPDATE SOAllocation

SET sa.SMFixed = (ar.disbursementValue/(Select c.counts

FROM temp1 c

Where c.counts=114))

From

SOAllocation sa INNER JOIN

SOStagingTable ss ON sa.SO = ss.SO AND sa.FiscalYear = ss.FiscalYear

AND sa.DriverTimeFrame = ss.DriverTimeFrame AND sa.Period = ss.Period INNER JOIN

ItemCrossReference itm ON ss.SecondItem = itm.ItemNumber INNER JOIN

#AllocationRecords ar ON itm.FGType = ar.BodyType AND sa.WgAmp = ar.Division, temp1 c

WHERE

ar.Division = 'WG' AND

ar.AllocationType = 'S&M' AND

ar.CostType = 'FIX' AND

ss.FiscalYear = @fiscalYear AND

ss.period = @periodvalue

remove sa. from update statement.