Rank() Emulator in Sql2000 Errror 2

[PWise]

Select FirstName into #TestRank 
From (
      Select 'Tom' FirstName 
      Union 
      Select 'Dick'
      Union 
      Select 'Harry'
      )Students
order by  FirstName 
Alter table #TestRank add Ctr int IDENTITY 
--Why does it Work if I use Select *
Select * from #TestRank
-- When I use This
Select Ctr, Studentfirstname from #TestRank
-- I get this Error Invalid column name 'Ctr'
drop table #TestRank

--not this

 

[gmmastros]

Select Ctr, Studentfirstname [!]FirstName[/!] from #TestRank

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

Declare your table first

CREATE TABLE #TestRank ( 
Ctr INT IDENTITY,
FirstName VARCHAR(100))

INSERT INTO #TestRank
Select FirstName 
From (
      Select 'Tom' FirstName
      Union
      Select 'Dick'
      Union
      Select 'Harry'
      )Students

Select Ctr, firstname from #TestRank ORDER BY FirstName

 

[PWise]

thanks for the reply
note the error is for ctr not firstname
I corrected my typing error and i still get the error

 

[gmmastros]

This is a deferred name resolution issue. For example... if you put go's in to the script (and run the whole thing as a batch) it works the way you expect it to.

Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.

When you mash the F5 key, the query is first parsed for errors. it recognizes the temp table (without the additional column) and validates the sql. The * version is fine, but the one listing the Ctr column (which doesn't exist when the table is created) causes an error.

RiverGuy's advice is the best way to deal with this.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

Thank both of you
Have Stars
River guy:
shouln't the order by be on the insert into statments
George: if I put in go's i can not run it with pramesaters

 

[gmmastros]

George: if I put in go's i can not run it with pramesaters

I know. I mentioned the go's as part of my explanation. If a table exists, and you reference a column.... that column must exist. If you reference a column in a table that does NOT exist, the query will parse (but not run).

For example, open a query window and copy/paste this:

Select Booger From Nose

Now.... press CTRL-F5, you'll see...

Command(s) completed successfully.

CTRL-F5 will parse your code only (not actually run it). If you press F5, you'll parse and run. Parse will succeed but run will fail because the table does not exist.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

River guy:
shouln't the order by be on the insert into statments

No. The order in which the rows exist in the table should be meaningless. It's best practice IMO to do the order by in the select.

 

[gmmastros]

Except in this case, where you want to simulate a rank column. The order by should be during the insert. However.... it should also be on the select since that is the only way you can be guaranteed to have the data in the correct order.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PWise]

then it should be

order by ctr[/ctr]