| Author |
 Topic  |
|
|
staticbob
Yak Posting Veteran
United Kingdom
99 Posts |
 Posted - 02/06/2006 : 07:12:40
|
Guys,
AS well as all the fields I am showing from the required tables, I would also like to return a field "LineItemID" that returns the line number of each row in the view ? How can I do this.
Thanks
Bob
"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com |
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 02/06/2006 : 07:22:13
|
You will need to clculate it.
Couold insert into a temp table with an identity and select from that. Depends what you relly need.
Why not do it in the client.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
Edited by - nr on 02/06/2006 07:24:12 |
 |
|
|
staticbob
Yak Posting Veteran
United Kingdom
99 Posts |
Posted - 02/06/2006 : 07:47:31
|
Thanks for the response nr,
I can't do it in the Client because the reporting tool that I'm using is crap. I can just show fields from a datasource, not add calculated ones.
Bob
"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com |
|
|
|
nr
SQLTeam MVY
United Kingdom
12543 Posts |
Posted - 02/06/2006 : 07:51:13
|
In that case it's probably best to show the code you have.
You should be use a stored procedure for the interface so that you can deliver whatever is needed.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3833 Posts |
Posted - 02/06/2006 : 07:59:37
|
Try looking up IDENTITY in books online. They have a sample that might help. A quick example using the Pubs db:
SELECT
IDENTITY(int, 1, 1) AS num,
emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last
INTO employees
FROM employee
|
|
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 02/06/2006 : 08:10:43
|
I think all reporting tools support RecordNumber feature that you can make use of 
Madhivanan
Failing to plan is Planning to fail |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 02/06/2006 : 08:16:05
|
As Lamprey says, but with a slight modification:
SELECT
IDENTITY(int, 1, 1) AS num,
emp_id AS emp_num,
fname AS first,
minit AS middle,
lname AS last
INTO #MyTempTable
FROM employee
ORDER BY MySortColumn1, MySortColumn2 ...
SELECT *
FROM #MyTempTable
ORDER BY [num]
Ideally pre-create the #MyTempTable and name the columns instead of using SELECT *
Kristen |
Edited by - Kristen on 02/06/2006 08:41:19 |
|
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 02/06/2006 : 08:23:35
|
quote:
Originally posted by staticbob
Thanks for the response nr,
I can't do it in the Client because the reporting tool that I'm using is crap. I can just show fields from a datasource, not add calculated ones.
Bob
"I dislilke 7am. If &am were a person, I would kick 7am in the biscuits." - Paul Ryan, dailyramblings.com
What reporting tool are you using?
And, remember, by defintion a view has no ordering -- it is a set of rows to be returned that the caller can order any way that it wants. For those reasons, a View should never return a row # column. |
|
|
|
Frank Kalis
Constraint Violating Yak Guru
Switzerland
413 Posts |
|
| |
Topic |
|
Simple - Return line number in view
Forum Locked
