asked on

Invalid column name

I'm not able to get rid of this error:

Msg 207, Level 16, State 1, Line 11
Invalid column name 'PD2_Amount'.

SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount
FROM
(

SELECT pd.Amount as pd1_Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdc pd ON pd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer

WHERE pd.Active = 1
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pd.Deposit IS NOT NULL
--AND pd.OnHold IS NULL
AND c.customer <> '9999999'

UNION

SELECT pdd.Amount as PD2_Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer

WHERE pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pdd.Entered <> '1900-01-01 00:00:00.000'
AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pdd.Deposit IS NOT NULL
--AND pdd.OnHold IS NULL
AND c.customer <> '9999999'
) as PDC_Main

Jim Horn

If you are going to UNION two different SQL statements, then they have to line up exactly, meaning same data type and same name.

Looks here like you are trying to UNION pd1_Amount in one sql statement with pd2_Amount in another SQL statement, which won't work.

Depending on what you're trying to do here, you'll want to either rename pd2_Amount to pd1_Amount, to match the first SQL statement, or create dummy values so that PD1_Amount is separate from PD2_Amount.

Hope this helps.
-Jim

dba123

ASKER

ok, so then how in the hell can I do the Addition that I want to do??

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dba123

ASKER

something like this then? This errors out

SELECT (ISNULL(pd1.Amount, 0) + ISNULL(pd2.Amount, 0)) as Amount
FROM
(

(SELECT pd.Amount as Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdc pd ON pd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer

WHERE pd.Active = 1
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pd.Deposit IS NOT NULL
--AND pd.OnHold IS NULL
AND c.customer <> '9999999') as pd1

UNION

(SELECT pdd.Amount as Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer

WHERE pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pdd.Entered <> '1900-01-01 00:00:00.000'
AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pdd.Deposit IS NOT NULL
--AND pdd.OnHold IS NULL
AND c.customer <> '9999999') as pd2
) as PDC_Main

Jim Horn

Give this a shot. If this doesn't work I'll step back and encourage other experts to comment.

SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount
FROM
(

SELECT pd.Amount as pd1_Amount, 0 as pd2_Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdc pd ON pd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer

WHERE pd.Active = 1
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pd.Deposit IS NOT NULL
--AND pd.OnHold IS NULL
AND c.customer <> '9999999'

UNION

SELECT 0 as pd1_Amount, pdd.Amount as PD2_Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer

WHERE pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pdd.Entered <> '1900-01-01 00:00:00.000'
AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pdd.Deposit IS NOT NULL
--AND pdd.OnHold IS NULL
AND c.customer <> '9999999'
) as PDC_Main

dba123

ASKER

wow, I don't see how this retains it, can you explain?

SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount
FROM
(
SELECT pd.Amount as pd1_Amount, 0 as pd2_Amount
'your SQL

UNION

SELECT 0 as pd1_Amount, pdd.Amount as PD2_Amount
'your SQL

dba123

ASKER

ok, jim, your last post worked, however I should only be getting one value back..not multiple rows...I only want one amount

dba123

ASKER

So in other words I'm getting

Amount
----------
10.17
14.67
25.00
25.23
25.34
....

rather than the sum of all these

Aneesh

Put a SUM() something like this

SELECT SUM(Amount) Amount
FROM (

Paste ur current Query here

) AS T

dba123

ASKER

Looks like this will work:

SELECT SUM(PDC_All.Amount)
FROM (
SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount
FROM
(

SELECT pd.Amount as pd1_Amount, 0 as pd2_Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdc pd ON pd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer

WHERE pd.Active = 1
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pd.Deposit IS NOT NULL
--AND pd.OnHold IS NULL
AND c.customer <> '9999999'

UNION

SELECT 0 as pd1_Amount, pdd.Amount as PD2_Amount
FROM Master m (NOLOCK)
LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number
INNER JOIN dbo.Customer c ON c.Customer = m.Customer

WHERE pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
AND pdd.Entered <> '1900-01-01 00:00:00.000'
AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate))
--AND pdd.Deposit IS NOT NULL
--AND pdd.OnHold IS NULL
AND c.customer <> '9999999'
) as PDC_Main
) AS PDC_All

Jim Horn

Thanks for the grade. Good luck with your project. -Jim

Also, a bunch of the more experienced SQL developers are around during normal US business hours, so that would be a great time to ask questions.

dba123

ASKER

thanks, yes, this SQL is getting super complicated now.