The multi-part identifier "p0.TextVal" could not be bound.

[tgtfranz]

I am not sure why I am getting this error. Everything ran fine until I added the DECLARE and the SET statements for @GD.

Please advise

Thanks.


DECLARE @InvDate datetime
DECLARE @DVar int
DECLARE @GD money
SET DATEFIRST 1
SET @DVar =
CASE
WHEN DATEPART(dw, GETDATE())= 1 THEN -1
ELSE -1
END

SET @InvDate = convert(datetime,CONVERT(CHAR(10),getdate()+@DVar,110))
SET @GD = convert(money, SUM((convert(decimal(3,2), p0.TextVal)) * m0.CountedCopies))


SELECT DISTINCT
c0.BillGroupID
,o0.SNCount
,a0.InvoiceID
,a0.InvoiceNumber
,b0.CustomerID
,b0.BillToID
,b0.LocationID
,SUBSTRING(b0.remarks, (CHARINDEX('#', b0.remarks)+1),(len(b0.remarks)-CHARINDEX('#', b0.remarks))) StoreNumber
,b0.CustomerNumber
,b0.CustomerName
,b0.Address
,b0.City
,b0.State
,b0.Zip
,a0.Date AS InvoiceDate
,DATEADD(day, 30, a0.Date) AS DueDate
,f0.SerialNumber
,d0.ContractMeterGroup
,d0.Description
,k0.BaseAmount
,d0.CoveredCopies
,m0.BeginMeterActual
,m0.BeginMeterDate
,m0.EndMeterActual
,m0.EndMeterDate
,m0.DifferenceCopies
,m0.CreditCopies
,m0.CountedCopies
,p0.ShAttributeID
,convert(decimal(3,2), p0.TextVal) AS CopyPrice
,convert(money, SUM((convert(decimal(3,2), p0.TextVal)) * m0.CountedCopies)) AS GrossDue
,e0.OverageDetailID
,CASE
WHEN e0.RangeEnding = 2000000000
THEN 'And Above'
ELSE CONVERT(varchar, e0.RangeEnding)
END AS RangeEnding
,e0.Rate
,SUM(m0.CountedCopies - d0.CoveredCopies) AS OverageCopies
,n0.EffectiveRate
,RateDiscount =
CASE p0.TextVal
WHEN null THEN 0.00
WHEN '0.00' THEN 0.00
ELSE ((convert(decimal(4,4),p0.TextVal)-(convert(decimal(4,4),e0.Rate)))/(convert(decimal(4,4),p0.TextVal)))*100
END
,EffectiveRateDiscount =
CASE p0.TextVal
WHEN null THEN 0.00
WHEN '0.00' THEN 0.00
ELSE ((convert(decimal(4,4),p0.TextVal)-(convert(decimal(4,4),n0.EffectiveRate)))/(convert(decimal(4,4),p0.TextVal)))*100
END
,k0.OverageAmount
,CASE
WHEN SUM(h0.Amount) IS NULL THEN 0
ELSE SUM(h0.Amount)
END AS Payments
,(b0.Invoices - b0.Unapplied - i0.Amount- a0.Due +
CASE
WHEN SUM(h0.Amount) IS NULL THEN 0
ELSE SUM(h0.Amount)
END ) AS PreviousBalance
,b0.Unapplied
,a0.Tax
,a0.Due AS InvoiceDue
,i0.Amount AS FinanceCharge
,b0.Invoices AS AllDue
,SUM(@GD - a0.Due) AS YourProfit


FROM
ARInvoices a0
LEFT JOIN
ARCustomers b0
ON
a0.CustomerID = b0.CustomerID
INNER JOIN
SCContracts c0
ON
b0.CustomerID = c0.CustomerID
INNER JOIN
SCContractMeterGroups d0
ON
c0.ContractID = d0.ContractID
INNER JOIN
SCContractMeterGroupOverages e0
ON
d0.ContractMeterGroupID = e0.ContractMeterGroupID
INNER JOIN
SCEquipments f0
ON
b0.BillToID = f0.BillToID
LEFT JOIN
ICModels g0
ON
f0.ModelID = g0.ModelID
LEFT JOIN
ARReceipts h0
ON
b0.CustomerID = h0.CustomerID
AND
a0.DatePeriod = h0.DatePeriod
LEFT JOIN
ARFinChargeDetails i0
ON
b0.CustomerID = i0.CustomerID
LEFT JOIN
ARFinCharges j0
ON
i0.FinChargeID = j0.FinChargeID
INNER JOIN
SCBillingContracts k0
ON
a0.InvoiceID = k0.InvoiceID
AND
c0.ContractID = k0.ContractID

INNER JOIN
SCBillingMeterGroups l0
ON
a0.InvoiceID = l0.InvoiceID
AND
k0.ContractDetailID = l0.ContractDetailID
INNER JOIN
SCBillingMeters m0
ON
l0.BillingMeterGroupID = m0.BillingMeterGroupID
AND
f0.EquipmentID = m0.EquipmentID
INNER JOIN
v_SCBillingMeterGroups n0
ON
m0.BillingMeterGroupID = n0.BillingMeterGroupID
AND
e0.ContractMeterGroupID = n0.ContractMeterGroupID
LEFT JOIN
tblSNCount o0
ON
b0.BillToID = o0.BillToID
INNER JOIN
ARCustomerCustomProperties p0
ON
f0.CustomerID = p0.CustomerID

WHERE
a0.TransactionTypeID = 9
AND
g0.IsHost = 1
AND
a0.Date = @InvDate
--AND
-- p0.ShAttributeID = '2002'
AND
c0.BillGroupID NOT IN
(
'131',
'132',
'501',
'474',
'104',
'200',
'198',
'199',
'500',
'141',
'434',
'416',
'153',
'154',
'294',
'124',
'137',
'327',
'469',
'470',
'467',
'468',
'475',
'477',
'471',
'102',
'472',
'473',
'115',
'409',
'30',
'292',
'499',
'503'
)

GROUP BY
c0.BillGroupID
,a0.InvoiceID
,a0.InvoiceNumber
,b0.CustomerID
,b0.BillToID
,b0.LocationID
,b0.CustomerNumber
,b0.CustomerName
,b0.remarks
,b0.Address
,b0.City
,b0.State
,b0.Zip
,a0.Date
,DueDate
,f0.SerialNumber
,d0.ContractMeterGroup
,d0.Description
,k0.BaseAmount
,d0.CoveredCopies
,m0.BeginMeterActual
,m0.BeginMeterDate
,m0.EndMeterActual
,m0.EndMeterDate
,m0.DifferenceCopies
,m0.CreditCopies
,m0.CountedCopies
,p0.ShAttributeID
,p0.TextVal
,e0.OverageDetailID
,e0.RangeEnding
,e0.Rate
,n0.EffectiveRate
,k0.OverageAmount
,b0.Unapplied
,a0.Tax
,a0.Due
,i0.Amount
,b0.Invoices
,o0.SNCount

 

[gmmastros]

This line:

SET @GD = convert(money, SUM((convert(decimal(3,2), p0.TextVal)) * m0.CountedCopies))

does not use any tables, yet you are referencing a table alias. What are you trying to do with the @GD variable?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tgtfranz]

Sorry yes, there are alias that are assigned. That is what the p0 and the m0 are used for. You can see what these are in my joins.

 

[SQLSister]

But the joins are not inthe set statement is what George is trying to tell you.

Questions about posting. See faq183-874

 

[tgtfranz]

I am new to this whole set function. How would I place the joins into the set function?

 

[mrdenny]

To use a table alias in a set like you are you have to include the from and the joins as well as a select statement.

declare @var1 int
set @var1 = (select max(a.dbid) from sysdatabases a)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com