SELECTing from a selection?

Question

Post reply

SELECTing from a selection?

bellammj

SSC Rookie

Points: 32
More actions
July 25, 2008 at 7:07 am

#188583

I have created the following script to try and create a view, and select from it all at once. Here is my code:

[font="Courier New"]create view sptype_spcode as

select count (*) from

(select all_tree_2008.plot_numbe, all_tree_2008.species_co, a3a_species.species_ty, a3a_species.species_co from all_tree_2008, a3a_species where all_tree_2008.species_co=a3a_species.species_co);

where all_tree_2008.plot_numbe=1 AND a3a_species.species_co='S';

order by sptype_spcode.plot_numbe, sptype_spcode.sample_yea;[/font]

I want to count how many species code types of 'S' fall within plot number 1 across all years (1965-present). I get this error:

ORA-00904:invalid identifier

Any ideas? Thanks so much, I really appreciate it.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Are you sure you're in the right forum? This is an Oracle error

http://ora-00904.ora-code.com/

indicating that a column in the SELECT list doesn't exist.

Having said that, it's good practice to check that a query works before creating a view out of it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bellammj SSC Rookie Points: 32 More actions · Answer 2

Thanks for your response Chris. I may well be in the wrong forum. I literally just started using Oracle/SQL yesterday and am feeling a little lost! Do you know of a forum where it would be more appropriate to post my questions? As far as I can tell, all the columns I specified should exist... but I know I must be doing something wrong.

Thanks again for replying,

M.

Chuck Rivel SSCrazy Points: 2961 More actions · Answer 3

a couple of things I noticed in this view you are creating. the first being the error, it looks like you are creating this view in ORACLE, is that correct?

if it is SQL Server view you are creating (and I am just reading the error wrong), here would be the problems:

you cannot have an order by in a view (caveat, if the query consists of a TOP clause, you can put an order by in the view but you are not doing that here)

you have an order by on a count(*) query, the column would have to be contained in the select and part of the group by to order by it (outside of the view creation)

you have not named your inner query of the from clause, that would need an alias

you have not named your count(*) column within the view

now, if it ORACLE, i am not sure what you can and cannot get away with from the above pieces that sql would flag as being in error

hth

bellammj SSC Rookie Points: 32 More actions · Answer 4

bellammj

SSC Rookie

Points: 32

July 25, 2008 at 7:31 am

#848372

Yes, I am using SQL developer for Oracle. My apologies, I should have mentioned that!

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

bellammj (7/25/2008)

Thanks for your response Chris. I may well be in the wrong forum. I literally just started using Oracle/SQL yesterday and am feeling a little lost! Do you know of a forum where it would be more appropriate to post my questions? As far as I can tell, all the columns I specified should exist... but I know I must be doing something wrong.

Thanks again for replying,

M.

Sorry, M, I don't use the Oracle forums. As consolation, here's a bit of SQL which may give the result you want:

SELECT a.species_co, s.species_ty, COUNT(*)



FROM all_tree_2008 a

INNER JOIN a3a_species s

	ON a.species_co = s.species_co

WHERE a.plot_numbe = 1 AND a.species_co = 'S'

GROUP BY a.species_co, s.species_ty

ORDER BY a.species_co, s.species_ty

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden