Autonumber Rows in a SELECT Statement - Microsoft SQL 2000

I have a view created to pull data from multiple tables.  I need to add a field to the selected field that will autonumber rows returned.  The number for each row must be unique.   Below is my Statement.
Any help is appreciated.
Bill
SELECT     TOP 100 PERCENT dbo.Profit.Company_ID, dbo.Profit.Company, dbo.Profit.Project_ID, dbo.Profit.Project, dbo.Profit.Revenue AS Commission, dbo.Profit.DateTime, dbo.Company.SLX_Company_ID
FROM         dbo.Profit INNER JOIN
                      dbo.Company ON dbo.Profit.Company_ID = dbo.Company.Company_ID
WHERE     (dbo.Profit.CommExpense = 1) AND (dbo.Profit.Revenue < 0) AND (NOT (dbo.Company.SLX_Company_ID IS NULL))
ORDER BY dbo.Profit.Project_ID
LVL 2
bilalahaAsked:
Who is Participating?
 
ptjcbConnect With a Mentor Commented:
The easiest way is to select into a temporary table.

SELECT     TOP 100 PERCENT ident = IDENTITY(int, 1, 1),  dbo.Profit.Company_ID, dbo.Profit.Company, dbo.Profit.Project_ID, dbo.Profit.Project, dbo.Profit.Revenue AS Commission, dbo.Profit.DateTime, dbo.Company.SLX_Company_ID
INTO #tempProfit
FROM         dbo.Profit INNER JOIN
                      dbo.Company ON dbo.Profit.Company_ID = dbo.Company.Company_ID
WHERE   (dbo.Profit.CommExpense = 1) AND (dbo.Profit.Revenue < 0) AND (NOT (dbo.Company.SLX_Company_ID IS NULL))


SELECT * FROM #tempProfit ORDER BY Project_ID
0
 
dqmqCommented:


SELECT   TOP 100 PERCENT dbo.Profit.Company_ID, dbo.Profit.Company, dbo.Profit.Project_ID, dbo.Profit.Project, dbo.Profit.Revenue AS Commission, dbo.Profit.DateTime, dbo.Company.SLX_Company_ID
 
--this addition produces the RowNUM
, (Select count(*) from 
  dbo.Profit P INNER JOIN
  dbo.Company C ON P.Company_ID = Company_ID
  WHERE     (P.CommExpense = 1) AND (P.Revenue < 0) 
      AND    (NOT (C.SLX_Company_ID IS NULL))
--the following condition assigns rownum by counting rows that are ordered before this one and assumes ProjectID provides a unique ordering.
--If not, then the condition must be expanded to count rows based on 
--enough additional columns to provide a unique order.
      AND P.ProjectID <= dbo.Profit.ProjectID   
 ) as RowNum
--end of rownum
 
FROM         dbo.Profit INNER JOIN
                      dbo.Company ON dbo.Profit.Company_ID = dbo.Company.Company_ID
WHERE     (dbo.Profit.CommExpense = 1) AND (dbo.Profit.Revenue < 0) AND (NOT (dbo.Company.SLX_Company_ID IS NULL))
ORDER BY dbo.Profit.Project_ID

Open in new window

0
 
dportasCommented:
ptjcb's solution I suspect will be the best method in SQL Server 2000. In 2005 you can use the ROW_NUMBER function.

Another piece of advice. Don't ever use "TOP 100 PERCENT ... ORDER BY" in a view. It achieves nothing useful because views are not ordered. What it does do is lead to the sloppy and unreliable habit of not including ORDER BY in queries that reference the view. If you do that you are almost certain to hit problems in future because the ordering of such queries without ORDER BY is not guaranteed. I'm aware that Enterprise Manager allows it and even seems to encourage you to do the "TOP 100 PERCENT" thing - that doesn't mean it's a good idea!
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
bilalahaAuthor Commented:
ptjcb:
I copied your solution and when I run it, I( get an error:
The Query Designer does not support the IDENTITY SQL construct.
Thanks,
Bill
0
 
bilalahaAuthor Commented:
dqmq:
I copied your solution and I a getting "Ambigious column name "CompanyID'
Thanks,

Bill
0
 
ptjcbCommented:
Do not use Enterprise Manager to run queries. Use the Query Analyzer.

http://databases.aspfaq.com/database/which-tool-should-i-use-enterprise-manager-or-query-analyzer.html

0
 
bilalahaAuthor Commented:
ptjcb:
It works great in the query analizer, but I need a view crearted so that I will be able to select from a different application and pull the data.
Bill
0
 
ptjcbCommented:
Create the view in QA. Avoid using Query Designer and View Designer in EM. They are known to have quirks (see the link that I sent earlier for a few of them).

SELECT     TOP 100 PERCENT ident = IDENTITY(int, 1, 1),  dbo.Profit.Company_ID, dbo.Profit.Company, dbo.Profit.Project_ID, dbo.Profit.Project, dbo.Profit.Revenue AS Commission, dbo.Profit.DateTime, dbo.Company.SLX_Company_ID
INTO #tempProfit
FROM         dbo.Profit INNER JOIN
                      dbo.Company ON dbo.Profit.Company_ID = dbo.Company.Company_ID
WHERE   (dbo.Profit.CommExpense = 1) AND (dbo.Profit.Revenue < 0) AND (NOT (dbo.Company.SLX_Company_ID IS NULL))

CREATE VIEW dbo.vw_Profit (ident, Company_id, Company, Project_Id, Project, Commission, DateTime, SLX_Company_ID)
as
SELECT ident, Company_id, Company, Project_Id, Project, Commission, DateTime, SLX_Company_ID FROM #tempProfit

Use the ORDER BY clause when you select from the view, not in the view creation.




0
 
imitchieCommented:
This will create the view for you ordered the way you want it
CREATE VIEW dbo.myViewOfProfit
as
SELECT     TOP 100 PERCENT dbo.Profit.Company_ID, dbo.Profit.Company, dbo.Profit.Project_ID, dbo.Profit.Project, dbo.Profit.Revenue AS Commission, dbo.Profit.DateTime, dbo.Company.SLX_Company_ID
into #x
FROM         dbo.Profit INNER JOIN
                      dbo.Company ON dbo.Profit.Company_ID = dbo.Company.Company_ID
WHERE     (dbo.Profit.CommExpense = 1) AND (dbo.Profit.Revenue < 0) AND (NOT (dbo.Company.SLX_Company_ID IS NULL))
ORDER BY dbo.Profit.Project_ID
 
alter table #x add AutoNum int identity(1,1)
 
select * from #x
GO

Open in new window

0
 
dqmqCommented:
>I copied your solution and I a getting "Ambigious column name "CompanyID'


Sorry, that should be C.CompanyID on line 6
0
 
bilalahaAuthor Commented:
PTJCB:
I tried the query below in the query analyzer and I am getting the following error:

Server: Msg 111, Level 15, State 1, Line 7
'CREATE VIEW' must be the first statement in a query batch.

SELECT     TOP 100 PERCENT ident = IDENTITY(int, 1, 1),  dbo.Profit.Company_ID, dbo.Profit.Company, dbo.Profit.Project_ID, dbo.Profit.Project, dbo.Profit.Revenue AS Commission, dbo.Profit.DateTime, dbo.Company.SLX_Company_ID
INTO #tempProfit
FROM         dbo.Profit INNER JOIN
                      dbo.Company ON dbo.Profit.Company_ID = dbo.Company.Company_ID
WHERE   (dbo.Profit.CommExpense = 1) AND (dbo.Profit.Revenue < 0) AND (NOT (dbo.Company.SLX_Company_ID IS NULL))

CREATE VIEW dbo.vw_Profit (ident, Company_id, Company, Project_Id, Project, Commission, DateTime, SLX_Company_ID)
as
SELECT ident, Company_id, Company, Project_Id, Project, Commission, DateTime, SLX_Company_ID FROM #tempProfit
0
 
ptjcbCommented:
SELECT     TOP 100 PERCENT ident = IDENTITY(int, 1, 1),  dbo.Profit.Company_ID, dbo.Profit.Company, dbo.Profit.Project_ID, dbo.Profit.Project, dbo.Profit.Revenue AS Commission, dbo.Profit.DateTime, dbo.Company.SLX_Company_ID
INTO #tempProfit
FROM         dbo.Profit INNER JOIN
                      dbo.Company ON dbo.Profit.Company_ID = dbo.Company.Company_ID
WHERE   (dbo.Profit.CommExpense = 1) AND (dbo.Profit.Revenue < 0) AND (NOT (dbo.Company.SLX_Company_ID IS NULL))

GO


CREATE VIEW dbo.vw_Profit (ident, Company_id, Company, Project_Id, Project, Commission, DateTime, SLX_Company_ID)
as
SELECT ident, Company_id, Company, Project_Id, Project, Commission, DateTime, SLX_Company_ID FROM #tempProfit
0
 
bilalahaAuthor Commented:
ptjcb:
When I try to run the the query below in the query analyzer I get the following error message.  I tried to run everything at once and the message below is what I get.  

Warning: Null value is eliminated by an aggregate or other SET operation.
(1676 row(s) affected)
Server: Msg 4508, Level 16, State 1, Procedure vw_Profit, Line 5
Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.

Could you please give some instruction since I am new at this.

Thanks,

Bill
------------------------------------------------------------------------------
SELECT     TOP 100 PERCENT ident = IDENTITY(int, 1, 1),  dbo.Profit.Company_ID, dbo.Profit.Company, dbo.Profit.Project_ID, dbo.Profit.Project, dbo.Profit.Revenue AS Commission, dbo.Profit.DateTime, dbo.Company.SLX_Company_ID
INTO #tempProfit
FROM         dbo.Profit INNER JOIN
                      dbo.Company ON dbo.Profit.Company_ID = dbo.Company.Company_ID
WHERE   (dbo.Profit.CommExpense = 1) AND (dbo.Profit.Revenue < 0) AND (NOT (dbo.Company.SLX_Company_ID IS NULL))

GO


CREATE VIEW dbo.vw_Profit (ident, Company_id, Company, Project_Id, Project, Commission, DateTime, SLX_Company_ID)
as
SELECT ident, Company_id, Company, Project_Id, Project, Commission, DateTime, SLX_Company_ID FROM #tempProfit
0
 
ptjcbCommented:
Sorry, you and I have been chasing our tail.

In a view definition, avoid TOP 100 Percent

http://jagbarcelo.blogspot.com/2006/08/using-top-100-percent-in-view-sql-2000.html

You cannot create views from temporary tables or table variables.

You can create views from other views or base tables. My original solution does not work because you are creating a view definition, not selecting from the view.

What you want is a view definition with an autonumber column. That's a problem because of the limitations on views. If you could use a stored procedure then the original solution works (procedures can use temporary tables).

To do this in the view definition, you could either:
create a sequence table (a table with a series of numbers) and add the column to the view definition
use the count(*) function in the view definition ( http://support.microsoft.com/kb/186133 )
add a IDENTITY column to one of the base tables
Create and populate a base table with the values that you want, instead of using a temporary table. Drop the base table when you are finished

The quickest solution, if you cannot use a stored procedure, would be to create a base table, populate it with the data, create the view from the base table and then drop the base table.







0
 
imitchieCommented:
bilalaha: have you looked at my solution above?
0
 
ptjcbCommented:
imitchie - your solution has the same problem. You are using a temporary table in a view definition.
0
 
imitchieCommented:
bilalaha: but have you tried it?
I assure you it will not cause this error

Server: Msg 4508, Level 16, State 1, Procedure vw_Profit, Line 5
Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.
0
 
bilalahaAuthor Commented:
imitchie:
I tried your solution and got other errors
Server: Msg 156, Level 15, State 1, Procedure myViewOfProfit, Line 4
Incorrect syntax near the keyword 'into'.
Server: Msg 4508, Level 16, State 1, Procedure myViewOfProfit, Line 10
Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.
Server: Msg 4508, Level 16, State 1, Procedure myViewOfProfit, Line 12
Views or functions are not allowed on temporary tables. Table names that begin with '#' denote temporary tables.
0
 
imitchieConnect With a Mentor Commented:
Embarrasing. Sorry, please try this instead.
I took a stab at the field types, please modify to suit.
CREATE Function dbo.myProfitTable()
returns @Result Table (
	AutoNum int identity(1,1),
	Company_ID int, Company varchar(50),
	Project_ID int, Project varchar(100), Commission money, 
	[DateTime] datetime)
AS
BEGIN
insert into @Result
SELECT     TOP 100 PERCENT
dbo.Profit.Company_ID, 
dbo.Profit.Company, 
dbo.Profit.Project_ID, 
dbo.Profit.Project, 
dbo.Profit.Revenue AS Commission, 
dbo.Profit.DateTime, 
dbo.Company.SLX_Company_ID
FROM         dbo.Profit INNER JOIN
                      dbo.Company ON dbo.Profit.Company_ID = dbo.Company.Company_ID
WHERE     (dbo.Profit.CommExpense = 1) AND (dbo.Profit.Revenue < 0) AND (NOT (dbo.Company.SLX_Company_ID IS NULL))
ORDER BY dbo.Profit.Project_ID
 
RETURN
END
GO
 
 
CREATE VIEW dbo.myViewOfProfit
as
SELECT * FROM dbo.myProfitTable()
GO

Open in new window

0
 
bilalahaAuthor Commented:
imitchie:
Server: Msg 8101, Level 16, State 1, Procedure myProfitTable, Line 9
An explicit value for the identity column in table '@Result' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Server: Msg 208, Level 16, State 1, Procedure myViewOfProfit, Line 5
Invalid object name 'dbo.myProfitTable'.
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'in'.
0
 
bilalahaAuthor Commented:
ptjcb:
I used your first qury that ran in the query analizer, but there was no view created in an application and pulled the results I needed, so It saved me from having to go thru all this.  I did not have to create a view after all.

Thanks for all your help.

Bill
0
 
imitchieCommented:
for completeness's sake, here's the corrected version. Which you can use a a view to join to other tables etc
CREATE Function dbo.myProfitTable()
returns @Result Table (
        AutoNum int identity(1,1),
        Company_ID int, Company varchar(50),
        Project_ID int, Project varchar(100), Commission money, 
        [DateTime] datetime)
AS
BEGIN
insert into @Result (Company_ID, Company, Project_ID, Project, Commission, [DateTime])
SELECT     TOP 100 PERCENT
dbo.Profit.Company_ID, 
dbo.Profit.Company, 
dbo.Profit.Project_ID, 
dbo.Profit.Project, 
dbo.Profit.Revenue AS Commission, 
dbo.Profit.DateTime, 
dbo.Company.SLX_Company_ID
FROM         dbo.Profit INNER JOIN
                      dbo.Company ON dbo.Profit.Company_ID = dbo.Company.Company_ID
WHERE     (dbo.Profit.CommExpense = 1) AND (dbo.Profit.Revenue < 0) AND (NOT (dbo.Company.SLX_Company_ID IS NULL))
ORDER BY dbo.Profit.Project_ID
 
RETURN
END
GO
 
 
CREATE VIEW dbo.myViewOfProfit
as
SELECT * FROM dbo.myProfitTable()
GO

Open in new window

0
 
bilalahaAuthor Commented:
imitchie:
Thanks for trying.

Bill
0
 
imitchieCommented:
CREATE Function dbo.myProfitTable()
returns @Result Table (
        AutoNum int identity(1,1),
        Company_ID int, Company varchar(50),
        Project_ID int, Project varchar(100), Commission money,
        [DateTime] datetime)
AS
BEGIN
insert into @Result
  (Company_ID, Company, Project_ID, Project, Commission, [DateTime])
SELECT     TOP 100 PERCENT
dbo.Profit.Company_ID,
dbo.Profit.Company,
dbo.Profit.Project_ID,
dbo.Profit.Project,
dbo.Profit.Revenue AS Commission,
dbo.Profit.DateTime,
dbo.Company.SLX_Company_ID
FROM         dbo.Profit INNER JOIN
                      dbo.Company ON dbo.Profit.Company_ID = dbo.Company.Company_ID
WHERE     (dbo.Profit.CommExpense = 1) AND (dbo.Profit.Revenue < 0) AND (NOT (dbo.Company.SLX_Company_ID IS NULL))
ORDER BY dbo.Profit.Project_ID
 
RETURN
END
GO
 
 
CREATE VIEW dbo.myViewOfProfit
as
SELECT * FROM dbo.myProfitTable()
GO
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.