bilalaha
asked on
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_I D IS NULL))
ORDER BY dbo.Profit.Project_ID
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_I
ORDER BY dbo.Profit.Project_ID
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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!
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!
ASKER
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
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
ASKER
dqmq:
I copied your solution and I a getting "Ambigious column name "CompanyID'
Thanks,
Bill
I copied your solution and I a getting "Ambigious column name "CompanyID'
Thanks,
Bill
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
http://databases.aspfaq.com/database/which-tool-should-i-use-enterprise-manager-or-query-analyzer.html
ASKER
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
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
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_I D 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.
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_I
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.
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
>I copied your solution and I a getting "Ambigious column name "CompanyID'
Sorry, that should be C.CompanyID on line 6
Sorry, that should be C.CompanyID on line 6
ASKER
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_I D 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
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_I
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
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_I D 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
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_I
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
ASKER
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_I D 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
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_I
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
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.
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.
bilalaha: have you looked at my solution above?
imitchie - your solution has the same problem. You are using a temporary table in a view definition.
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.
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.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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'.
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'.
ASKER
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
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
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
ASKER
imitchie:
Thanks for trying.
Bill
Thanks for trying.
Bill
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_I D IS NULL))
ORDER BY dbo.Profit.Project_ID
RETURN
END
GO
CREATE VIEW dbo.myViewOfProfit
as
SELECT * FROM dbo.myProfitTable()
GO
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_I
ORDER BY dbo.Profit.Project_ID
RETURN
END
GO
CREATE VIEW dbo.myViewOfProfit
as
SELECT * FROM dbo.myProfitTable()
GO
Open in new window