Solved

Autonumber Rows in a SELECT Statement - Microsoft SQL 2000

Posted on 2007-12-05
24
733 Views
Last Modified: 2008-02-01
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
0
Comment
Question by:bilalaha
  • 9
  • 6
  • 6
  • +2
24 Comments
 
LVL 27

Accepted Solution

by:
ptjcb earned 400 total points
Comment Utility
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
 
LVL 42

Expert Comment

by:dqmq
Comment Utility


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
 
LVL 22

Expert Comment

by:dportas
Comment Utility
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
 
LVL 2

Author Comment

by:bilalaha
Comment Utility
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
 
LVL 2

Author Comment

by:bilalaha
Comment Utility
dqmq:
I copied your solution and I a getting "Ambigious column name "CompanyID'
Thanks,

Bill
0
 
LVL 27

Expert Comment

by:ptjcb
Comment Utility
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
 
LVL 2

Author Comment

by:bilalaha
Comment Utility
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
 
LVL 27

Expert Comment

by:ptjcb
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 
LVL 42

Expert Comment

by:dqmq
Comment Utility
>I copied your solution and I a getting "Ambigious column name "CompanyID'


Sorry, that should be C.CompanyID on line 6
0
 
LVL 2

Author Comment

by:bilalaha
Comment Utility
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
 
LVL 27

Expert Comment

by:ptjcb
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Author Comment

by:bilalaha
Comment Utility
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
 
LVL 27

Expert Comment

by:ptjcb
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
bilalaha: have you looked at my solution above?
0
 
LVL 27

Expert Comment

by:ptjcb
Comment Utility
imitchie - your solution has the same problem. You are using a temporary table in a view definition.
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 
LVL 2

Author Comment

by:bilalaha
Comment Utility
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
 
LVL 25

Assisted Solution

by:imitchie
imitchie earned 100 total points
Comment Utility
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
 
LVL 2

Author Comment

by:bilalaha
Comment Utility
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
 
LVL 2

Author Comment

by:bilalaha
Comment Utility
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
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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
 
LVL 2

Author Comment

by:bilalaha
Comment Utility
imitchie:
Thanks for trying.

Bill
0
 
LVL 25

Expert Comment

by:imitchie
Comment Utility
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now