Solved

Autonumber Rows in a SELECT Statement - Microsoft SQL 2000

Posted on 2007-12-05
24
741 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 6
  • 6
  • +2
24 Comments
 
LVL 27

Accepted Solution

by:
ptjcb earned 400 total points
ID: 20413912
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
ID: 20414045


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
ID: 20414113
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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 2

Author Comment

by:bilalaha
ID: 20414551
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
ID: 20414617
dqmq:
I copied your solution and I a getting "Ambigious column name "CompanyID'
Thanks,

Bill
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 20414800
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
ID: 20415141
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
ID: 20415202
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
ID: 20416634
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
ID: 20417697
>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
ID: 20419308
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
ID: 20420215
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 2

Author Comment

by:bilalaha
ID: 20420870
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
ID: 20422443
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
ID: 20422837
bilalaha: have you looked at my solution above?
0
 
LVL 27

Expert Comment

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

Expert Comment

by:imitchie
ID: 20422931
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
ID: 20423765
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
ID: 20423828
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
ID: 20423861
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
ID: 20423884
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
ID: 20423910
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
ID: 20423914
imitchie:
Thanks for trying.

Bill
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20423928
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

734 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