Invalid column name - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Invalid column name

Author

Topic

DesLavender
Starting Member

4 Posts

Posted - 2009-01-23 : 10:48:58

Good afternoon. SOOO new to SQL - just been on a 2-day course;
now trying to put theory into practice. The following code works just fine:-

 USE KerridgeExports
SELECT  SUM (CostOfSale001) AS 'CostOfSale001', SUM(SalesValue001)AS 'SALES',SUM (SalesValue001 - CostOfSale001) AS 'PROFIT',(SUM (SalesValue001 - CostOfSale001)/SUM(SalesValue001))*100 AS 'PROFIT MARGIN'
FROM OSBufferODBC 
WHERE InvoiceAccount = '00120482' AND DateRaised >= '01/01/2008' 
GO

I'm trying to create variables just to make things look tidier, but when I try this

 USE KerridgeExports
DECLARE @SALES AS varchar (20);
SET @SALES = (SUM(SalesValue001));
SELECT  @SALES AS 'SALES'
FROM OSBufferODBC 
WHERE InvoiceAccount = '00120482' AND DateRaised >= '01/01/2008' 
GO

I get the following error message

 Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'SalesValue001'.

I'm sure this must be a 101 (or I D ten T!) error but I'm just starting out
so any assistance would be received gratefully. Thanks.

Des Lavender

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-23 : 10:55:01

Thats because SQl has no reference to SalesValue001 You need to use a select statement in this case to provide that reference.
Make sure you use a quotation mark as I've shown.

declare @sales varchar(200)
set @sales = 'select sum(SalesValue001) from salesTable'

By the way this method is called dynamic sql. I think most people might prefer to do it a little differently.
This particular example isn't too bad but you can get into some bad habits if you are not careful.

May I recommend you read this link so that you get off to a good start?
[url]http://www.sommarskog.se/dynamic_sql.html#Common_cases[/url]

hope that helps !

r&r

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-01-23 : 11:01:52

You appear to be trying to set that value of a variable to the definition of a formula, which is not possible.
The best way to clear up your code is through effective use of white space. I'd write your query like this:

USE	KerridgeExports

SELECT	SUM (CostOfSale001) AS 'CostOfSale001',
		SUM(SalesValue001)AS 'SALES',
		SUM (SalesValue001) - sum(CostOfSale001) AS 'PROFIT',
		((SUM (SalesValue001) - sum(CostOfSale001))/SUM(SalesValue001)) * 100 AS 'PROFIT MARGIN'
FROM		OSBufferODBC 
WHERE		InvoiceAccount = '00120482'
		AND DateRaised >= '01/01/2008' 
GO

Also, beware of div-zero errors when calculating profit and margin...

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-01-23 : 11:03:49

Do NOT use dynamic SQL for this! If you are trying to simplify your code, dynamic SQL will have the complete opposite effect.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

DesLavender
Starting Member

4 Posts

Posted - 2009-01-23 : 11:49:45

Thanks. I couldn't get a handle on your solution revdnrdy. Blindman, I agree with the setting out to make it clearer - our lecturer was quite obsessed with this! I had only started with 1 variable as I knew that if 1 didn't work there was no point in trying to do them all. I'm actually quite disappointed that you can't assign the result of a SUM to a variable. Oh well, life's full of disappointments I guess!!

Des Lavender

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 11:57:38

quote:
Originally posted by DesLavender

Thanks. I couldn't get a handle on your solution revdnrdy. Blindman, I agree with the setting out to make it clearer - our lecturer was quite obsessed with this! I had only started with 1 variable as I knew that if 1 didn't work there was no point in trying to do them all. I'm actually quite disappointed that you can't assign the result of a SUM to a variable. Oh well, life's full of disappointments I guess!!

Des Lavender

you can. just use


DECLARE @CostOfSale001 int,@SALES int,@PROFIT int,@PROFIT_MARGIN int
SELECT @CostOfSale001=SUM (CostOfSale001),
	@SALES=	SUM(SalesValue001),
	@PROFIT=SUM (SalesValue001) - sum(CostOfSale001),
	@PROFIT_MARGIN=((SUM (SalesValue001) - sum(CostOfSale001))/SUM(SalesValue001)) * 100
FROM		OSBufferODBC 
WHERE		InvoiceAccount = '00120482'
		AND DateRaised >= '01/01/2008'

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-01-23 : 13:23:36

You can, of course, assign the result of a sum to a variable.
What you can't do is assign the result of multiple aggregations to a single scalar variable, and in SQL we typically deal in sets of records, not single records.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

DesLavender
Starting Member

4 Posts

Posted - 2009-01-26 : 06:31:57

Hi guys. I suppose the question I have to ask myself now is, "Why would I want to use these Variables when all I was trying to do was tidy things up & (maybe) cut down on the typing." I've checked both versions and my original pans out at 308 characters and the one with variables is 358 characters. I guess I'll just leave it. Thanks all anyway.

Des Lavender

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:29:06

what were you trying to tidy up?

DesLavender
Starting Member

4 Posts

Posted - 2009-01-26 : 09:01:16

Just this:-
USE KerridgeExports
SELECT SUM (CostOfSale001) AS 'CostOfSale001'
,SUM(SalesValue001)AS 'Sales'
,SUM (SalesValue001 - CostOfSale001) AS 'Profit'
,(SUM (SalesValue001 - CostOfSale001)/SUM(SalesValue001))*100 AS 'Profit Margin'
FROM OSBufferODBC
WHERE InvoiceAccount = '00120482'
AND DateRaised >= '01/01/2008'
GO

I just thought I wanted to change the SUMs to make it look more
readable - like when you name cells in Excel then use those names
in a formula - but now I can see that it looks fine with the column headings that I've put in.

Des Lavender

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 09:04:46

oh...ok...so it was just a matter of aliases.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources