HELP... Need to set an auto increment in a View

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

HELP... Need to set an auto increment in a View

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2009-11-16 : 13:06:47

Hello Ladies & Gents

I was hoping you could assist me and/or point me in the right direction. Here is my issue

I have a basic SQL view which links 2 tables by a common field (e.g. Cust No.) and displays the resulting rows/columns of the View when executed based on the Cust No.

I need to auto increment the rows with a unique number (like an id field) when the view is generated.

Would you Ladies & Gentlemen be so kind as to help me with this issue

Thanks in advance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-16 : 13:13:06

use windows ranking function: ROW_NUMBER()

ie:


select row_number() over (order by <some column(s)> ) as rowno
       .<other columns>
from   ....

Be One with the Optimizer
TG

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2009-11-16 : 13:23:42

Hello TG

First thanks so much for your assistance, however I am not extremly familiar with SQL.

Would it be possible to further explain your solution (for instance, I have Table-A & Table-B in my view.
I inner join Table-A to Table-B by the [cust no.] field. Which when the View is executed, it displays all of
the rows in which [cust no]. in Table-A matches [cust no.] in Table-B.

I need to auto increment the resulting view so that each row has a unique number. Given this, how do I
customize your solution to achieve this ?

Thanks again

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-16 : 13:39:38

read about row_number() here.
you simply add that row_number() line in my example as another item in your SELECT list. you just need to replace "<some columns(s)" with whatever columns you want to use to define the sequence of your auto increment value. If you're still confused then post your view. Use the [ code ] tags so you don't loose the formatting.

Be One with the Optimizer
TG

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2009-11-16 : 13:47:04

Hi TG

Again thanks so much for your assistance, i'm still a bit confused. So i've added an example of one of my views.
In this example i'm inner joining [Salesperson Code] and need to display a column with an auto incremented unique
number for each row displayed in the view

SELECT dbo.[TransNet Corporation$Customer].No_ AS [Cust No], dbo.[TransNet Corporation$Customer].Name AS [Company Name],
dbo.[TransNet Corporation$Customer].Address, dbo.[TransNet Corporation$Customer].[Address 2], dbo.[TransNet Corporation$Customer].City,
dbo.[TransNet Corporation$Customer].County AS State, dbo.[TransNet Corporation$Customer].[Post Code] AS ZipCode,
dbo.[TransNet Corporation$Customer].[Phone No_] AS Phone, dbo.[TransNet Corporation$Customer].[Fax No_] AS Fax,
dbo.[TransNet Corporation$Salesperson_Purchaser].Name AS [Inside Salesperson], dbo.[TransNet Corporation$Customer].[Salesperson Code]
FROM dbo.[TransNet Corporation$Customer] INNER JOIN
dbo.[TransNet Corporation$Salesperson_Purchaser] ON
dbo.[TransNet Corporation$Customer].[Inside Salesperson] = dbo.[TransNet Corporation$Salesperson_Purchaser].Code

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-16 : 14:06:33

[code]
SELECT row_number() over (order by dbo.[TransNet Corporation$Customer].No) as RowNumber
,dbo.[TransNet Corporation$Customer].No_ AS [Cust No]
,dbo.[TransNet Corporation$Customer].Name AS [Company Name]
,dbo.[TransNet Corporation$Customer].Address
,dbo.[TransNet Corporation$Customer].[Address 2]
,dbo.[TransNet Corporation$Customer].City
,dbo.[TransNet Corporation$Customer].County AS State
,dbo.[TransNet Corporation$Customer].[Post Code] AS ZipCode
,dbo.[TransNet Corporation$Customer].[Phone No_] AS Phone
,dbo.[TransNet Corporation$Customer].[Fax No_] AS Fax
,dbo.[TransNet Corporation$Salesperson_Purchaser].Name AS [Inside Salesperson]
,dbo.[TransNet Corporation$Customer].[Salesperson Code]
FROM dbo.[TransNet Corporation$Customer]
INNER JOIN dbo.[TransNet Corporation$Salesperson_Purchaser]
ON dbo.[TransNet Corporation$Customer].[Inside Salesperson] = dbo.[TransNet Corporation$Salesperson_Purchaser].Code
[/code]

Be One with the Optimizer
TG

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2009-11-16 : 14:10:22

Hi TG

I get the following error when i try to run in SQL Management Studio

Msg 195, Level 15, State 10, Line 1
'row_number' is not a recognized function name.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-16 : 14:13:41

I assume you are using sql server 2005 since you've posted this in a sql 2005 forum. check the database compatibility of this specific database. (database properties | options)

Be One with the Optimizer
TG

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2009-11-16 : 14:18:04

Oh my... My appologies TG

I always use SQL Management Studio 2005 (for all my queries & testing views etc.)
but I just checked and verified that the actual Database itself is SQL 2000. DAMN...

Is there a similiar function which will run in SQL 2000, I hope so

Thanks again for your assistance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-16 : 14:21:50

No there isn't. There are, however, some less efficient ways to generate the same effect - but what do you need this for? Can you just use cust_no? Another alternative is to add an identity column to the customer table then just include that column in the view.

EDIT:
another alternative is if the server itself is 2005 but the database was a restore from a 2000 box then maybe you can simply set the compatibility level to 90.

Be One with the Optimizer
TG

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2009-11-16 : 14:24:06

I guess I can just add an ID column to the Cust table, but how do I get it to auto increment?
and is there a way to set a starting number?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-11-16 : 14:37:15

in sql server and "auto increment" column is an "identity" column. you can specify that when you add the column. In t-sql code to add an identity column called [rownumber] which starts at 52 and increments by 1:

alter table [TransNet Corporation$Customer] add rowNumber int identity(52, 1)

Be One with the Optimizer
TG

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2009-11-16 : 14:43:14

I will try that,

Thanks again TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-17 : 02:27:40

Alternatively, if you want to show data in front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources