Auto Number field in a view

[erics55]

Hi
I need to craete an auto number field in a view does anyone know how?

Thanks in advance

 

[ptheriault]

you could try something like this although, I'm not sure it would be the best way but I know it will work.

SELECT IDENTITY(int,1,1) AS ID
,Other fields from talbe
INTO #tmp
FROM yourTable

SELECT * FROM #tmp

- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

Sorry, I didn't read you post properly. You can't use a temp table in a view.


- Paul
- Database performance looks fine, it must be the Network!

 

[ptheriault]

your view would look like this.

CREATE VIEW myView
AS
SELECT $IDENTITY
       ,Field1
FROM Table

- Paul
- Database performance looks fine, it must be the Network!

 

[erics55]

Hi I am getting the error

Incorrect syntax near the keyword 'Identity'.

 

[ptheriault]

I should have asked, what verion of SQL is this?

- Paul
- Database performance looks fine, it must be the Network!

 

[erics55]

2000

Thanks

 

[ptheriault]

Try this.

--Assume the primary key is a composite of Col1 and Col2.
Select
 RowNum=(Select Count(*) From TableName
         Where Col1<=t1.Col1 And Col2 <=t1.Col2)
 Col1, Col2, Col3, Col4
From TableName t1
Order By Col1, Col2

- Paul
- Database performance looks fine, it must be the Network!

 

[erics55]

Hi Paul
Thanks for you time

This gives me the same value in ever row

 

[ptheriault]

Can you post your code.

- Paul
- Database performance looks fine, it must be the Network!

 

[erics55]

actually having looked again there are 10 rows in the view and 8 are the same and 2 are different in the RowNum Field

Thanks again

alter view dbo.vw_User_Matrix_Check
as
select RowNum=(Select Count(*) From vw_User_Matrix Where [S_A.CPIC]<=t1.[S_A.CPIC] And [Z:IPCREMOT]<=t1.[Z:IPCREMOT]), [S_A.CPIC], [Z:IPCREMOT], [BASE+XXXXV], [BASE:SA40V], [J5H_MONITOR], [SAP_ALL], [XXXX@PIV], [S_CSMREG]
From vw_User_Matrix t1
Group by [S_A.CPIC], [Z:IPCREMOT], [BASE+XXXXV], [BASE:SA40V], [J5H_MONITOR], [SAP_ALL], [XXXX@PIV], [S_CSMREG]

 

[ptheriault]

you are using a group by clause without any aggregate functions. Try changing that to an order by clause.

- Paul
- Database performance looks fine, it must be the Network!

 

[erics55]

It wouldnt do what i need it to do

 

[ptheriault]

check out this post
thread183-1306393
SQLBill has added some usefull stuff.
There is also an FAQ on this subject.
I wish I could be more usefull to you.

- Paul
- Database performance looks fine, it must be the Network!

 

[erics55]

You have helped, your rownum idea was useful and will help in the future just perhaps not in this instance

thanks for the information