problem referencing aliased field/table names 1

[belovedcej]

I'm having difficulty with the below query (actually just a portion of a much large one - thus the structure.)

I am told "invalid identifier" at line 2, allV.AllVoters. However, if I swap the fields, I get the error at line 2 again, this time on allV.AllDate.

Any ideas what is wrong?

SELECT  allV.AllDate,
        allV.AllVoters
from   (select count(id) as "AllVoters", 
         trunc(modified_date) as "AllDate" 
          from voters
          where trunc(modified_date) > '01-sep-08
                and vst_id != 6
         group by trunc(modified_date)) allV;

 

[SantaMufasa]

Dearly Beloved,

There are a couple of issues in your code that will cause you errors:[ul][li]In Oracle, always avoid using double quotes to surround table- or column-name references. The only time one ever needs double quotes around such is if one creates the name with double quotes...And then forever after you need double quotes around the name and must continue to use exact case on all characters in the name. Therefore, if you never use double quotes, then you never need them.[/li][li]You are missing a closing single quote around your literal date. It is always advisable to force Oracle to transform non-DATE expressions into DATE expression (for the sake of comparison) using the TO_DATE function.[/li][/ul]Let us know your results following these remediations.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[belovedcej]

Thanks for the explanation and tips. It turns out I just missed the apostrophe when I copied from SQLPlus, so that part was ok. But I changed everything else.

But now I'm getting a different error on line 3:
ORA-00923: FROM keyword not found where expected

I've run stuff like this on SQLServer a million times, but Oracle is driving me bonkers!

SELECT  allV.AllDate,
	allV.AllVoters
from 	(select count(id) as 'AllVoters', 
	trunc(modified_date) as 'AllDate' 
	from voters
	where trunc(modified_date) > to_Date('01-sep-08') 
		and vst_id != 6
group by trunc(modified_date)) allV

 

[SantaMufasa]

Yank the single quotes, as well, from around your "as ..." labels.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[belovedcej]

thank you so much! perfect. and it fixed ALL my errors in the larger query.