Select statement error

Question

0.00/5 (No votes)

See more:

Why am I getting the error message: Invalid column name 'theField' with the following query?

SQL

Declare @aField varchar
Set @aField = 'nameFirst'

Select 
	Case ISNULL(@aField,'Null') 
	When 'Null' Then 'Null'
	Else 'NotNull'
	End as theField,
	COUNT(*)
From
	tblCustomer
Group By 
	theField

Posted 9-Oct-15 2:46am

dhMac

Updated 9-Oct-15 3:24am

v3

Add a Solution

Comments

dhMac 9-Oct-15 9:03am

Instead of putting a new field name in the query each time I just replace the 'nameFirst' with a new new name. I want the query to just tell me how many row are Null and how many aren't Null.

PIEBALDconsult 9-Oct-15 9:11am

The ISNULL isn't doing anything useful for you.
You might also try SELECT COUNT(*) [Total] , COUNT([nameFirst]) [NotNull] FROM [tblCustomer] and then calculate how many are null.

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-10-09T02:59:00

Because there is no column on tblCustomer called theField
Sometimes you can get around this by doing something like

SQL

Select
 Case ISNULL(@aField,'Null')
 When 'Null' Then 'Null'
 Else 'NotNull'
 End as theField,
 COUNT(*)
 From
 tblCustomer
 Group By 1

but in this case you will get another error

Quote:
Each GROUP BY expression must contain at least one column that is not an outer reference.

Again because you are trying to use Group By with something that does not use columns on the table.

It's not entirely clear what you are trying to do with @afield

Anupam Shukla 2009 · Answer 2 · 2015-10-09T03:13:00

You can't use alias column name with GROUP BY OR WHERE clause. You can use Common Table Expression to solve the issue.

Declare @aField varchar
Set @aField = 'nameFirst'

;WITH CTE AS(
Select Case ISNULL(@aField,'Null') When 'Null' Then 'Null' Else 'NotNull' End as theField
From tblCustomer)
SELECT COUNT(*) FROM CTE
Group By
theField

W∴ Balboos, *GHB* · Answer 3 · 2015-10-09T05:09:00

Solution 4

This will get what you want without any group-by:

SQL

Declare @aField varchar
Set @aField = 'sort_order'
 
Select
  'Null', COUNT(@aField) from EDIT_code
 where @aField is null
UNION
 select 
 'NotNull', COUNT(@aField) from EDIT_code
 where @aField is not null

Posted 9-Oct-15 5:09am

W∴ Balboos, GHB

Updated 9-Oct-15 5:14am

v2

Select statement error

3 solutions

Solution 1

Solution 2

Solution 4

Add your solution here

Preview 0