We help IT Professionals succeed at work.

Invalid column name

I want to sum the 2 select statements in the inner cursor loop.   I get this error: Invalid column name 'TotalDebits'.

I looked at example online and this looks correct. I also tried using the + operator. I removed the second "select" as a test and ran it but still the same error.

I've marked it where I get the error:

Declare @rYear int
Declare @rMonth int
Declare reportCursor Cursor
	for Select year, month from #ReportDateTable
Open reportCursor;
Fetch Next From reportCursor into @rYear, @rMonth
while @@FETCH_STATUS = 0
	Begin
		Declare @aYear int
		Declare @aMonth int
		Declare activationCursor Cursor
			for Select year, month from #ActivateDateTable
			Open activationCursor
			Fetch Next From activationCursor into @aYear, @aMonth
			while @@FETCH_STATUS = 0
				Begin
					-- Do your stuff and insert into @Usage
              -- INSERT INTO #usage (reportdate,activationdate,usage)
			       SELECT CAST(@rYear AS VARCHAR(4)) + '.' + CAST(@rMonth AS VARCHAR(2)), 
				          CAST(@aYear AS VARCHAR(4)) + '.' + CAST(@aMonth AS VARCHAR(2)),
				          SUM(TotalDebits)  ---- ******* I get the error here **************
				        (
					    	
							SELECT 
							   
								   SUM(dcp.[Cash Debit] * -1) TotalDebits 

							......
							 
                            
						UNION ***** I tried the + operator here 
						SELECT SUM([CashBalance]) TotalDebits
						...
                       )   -- union statement
							
					Fetch Next from activationCursor into @aYear, @aMonth
				End
			Close activationCursor
			DEALLOCATE activationCursor
		 --Insert into Usage table
		 
		 Fetch Next from reportCursor into @rYear, @rMonth	
	End
	Close reportCursor
	DEALLOCATE reportCursor

Open in new window

Comment
Watch Question

Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
It looks like we don't have the full query. One thing I see missing is the ALIAS for the sub-query.

Also, have you tried running just you internal statements first?

 (
					    	
							SELECT 
							   
								   SUM(dcp.[Cash Debit] * -1) TotalDebits 

							......
							 
                            
						UNION ***** I tried the + operator here 
						SELECT SUM([CashBalance]) TotalDebits
						...
                       ) AS YourAliasForInternalStatements

Open in new window

CERTIFIED EXPERT

Author

Commented:
I added an alias. Let me try again
CERTIFIED EXPERT

Author

Commented:
Now, I added an alias but get an error ...incorrect syntax near As.

I'm posting the entire SQL...Marked where the error is below

Declare reportCursor Cursor
	for Select year, month from #ReportDateTable
Open reportCursor;
Fetch Next From reportCursor into @rYear, @rMonth
while @@FETCH_STATUS = 0
	Begin
		Declare @aYear int
		Declare @aMonth int
		Declare activationCursor Cursor
			for Select year, month from #ActivateDateTable
			Open activationCursor
			Fetch Next From activationCursor into @aYear, @aMonth
			while @@FETCH_STATUS = 0
				Begin
					-- Do your stuff and insert into @Usage
              -- INSERT INTO #usage (reportdate,activationdate,usage)
			       SELECT CAST(@rYear AS VARCHAR(4)) + '.' + CAST(@rMonth AS VARCHAR(2)), 
				          CAST(@aYear AS VARCHAR(4)) + '.' + CAST(@aMonth AS VARCHAR(2)),
				          SUM(TotalDebits) 
				        (
					    	
							SELECT 
							   
								   SUM(dcp.[Cash Debit] * -1) TotalDebits 

							FROM [DebitCard_Data].[dbo].[DC_DebitCard_Payment] dcp
								INNER JOIN DC_Balance db
									ON db.Account = dcp.Account --*** added
								INNER JOIN #TransIDs tid
									ON dcp.[Transaction ID] = tid.[Transaction ID]
							WHERE YEAR(tid.[BusinessDate]) = @rYear
								  AND MONTH(tid.[BusinessDate]) = @rMonth

								  -- This is the down chink
								  AND YEAR(db.ActivationDate) = @aYear --*** use activation date
								  AND MONTH(db.ActivationDate) = @aMonth
							 --first sum
                            
						UNION 
						SELECT SUM([CashBalance]) TotalDebits
						FROM [DebitCard_Data].[dbo].[DC_ClearCardHistory] dc
							INNER JOIN DC_Balance db
								ON db.Account = dc.Account 
							
							--INNER JOIN #TransIDs tid
							--	ON dcp.[Transaction ID] = tid.[Transaction ID]
						WHERE
							-- This is the down chink  
							YEAR(db.ActivationDate) >= @aYear
							AND MONTH(db.ActivationDate) <= @aMonth

							-- This is the across chink
							AND YEAR([LT_DateTime]) > DATEADD(HOUR, 7, @rYear)
							AND MONTH([LT_DateTime]) < DATEADD(HOUR, 7, @rMonth)
							 
                       ) AS myAliastest  -- **** getting error here **********
							
					Fetch Next from activationCursor into @aYear, @aMonth
				End
			Close activationCursor
			DEALLOCATE activationCursor
		 --Insert into Usage table
		 
		 Fetch Next from reportCursor into @rYear, @rMonth	
	End
	Close reportCursor
	DEALLOCATE reportCursor

Open in new window

Éric MoreauSenior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016

Commented:
which error?
CERTIFIED EXPERT

Author

Commented:
I have it above:  Now, I added an alias but get an error ...incorrect syntax near As.

I've marked it in the SQL.  This can't be that hard! not sure what I'm missing here.
Senior .Net Consultant
CERTIFIED EXPERT
Top Expert 2016
Commented:
You are missing the FROM in front of your sub select:

SELECT CAST(@rYear AS VARCHAR(4)) + '.' + CAST(@rMonth AS VARCHAR(2)), 
				          CAST(@aYear AS VARCHAR(4)) + '.' + CAST(@aMonth AS VARCHAR(2)),
				          SUM(TotalDebits) 
						  FROM  -- MISSING KEYWORD
				        (
					    	
							SELECT 

Open in new window

CERTIFIED EXPERT

Author

Commented:
oh Geez, yes, thanks. See, it wasn't that hard! :)