Solved

SQL: Add incrementing Counter to View output

Posted on 2008-10-30
5
393 Views
Last Modified: 2009-02-26
Additonal  request poseted under new question
*** Note this is a SQL 2000 DB

The application we run calls a view, the view output is used in a labeling program. I can modify the view, but cannot modify anything within the application that calls it.

I am looking to add a counter to the views output. Each time the view is called, each row needs to have an incrementing number assigned. The current output of the view is grouped by a field (Order_ID). On each change of this Order ID within the view result, the counter needs to reset to 1


Order_ID    Counter    Carton_ID
100               1             5000
100               2             5001
100               3             5002
101               1             5003
101               2             5004
102               1             5004

As I mentioned, I cannot touch the application, only the view. Is there anyway to accomplish this ??
0
Comment
Question by:JDCam
  • 3
  • 2
5 Comments
 
LVL 9

Expert Comment

by:jamesgu
ID: 22843670
select order_id, (select count(*) from <table> b where b.order_id = a.order_id and b.carton_id >= a.carton_id) as counter, carton_id
from <table> a

0
 

Author Comment

by:JDCam
ID: 22843806
I am not following..
wouldn't that always produce the value of 1 ?
0
 
LVL 9

Accepted Solution

by:
jamesgu earned 500 total points
ID: 22843854
no, the sub-query will be evaluated for each row of the outer table (a)
0
 

Author Comment

by:JDCam
ID: 22844140
If I sustitute:
(a) with Order   (header level order table)
(b) with Carton  (detail level order table

I can match the Order_ID between both tables, but not the carton_id. Carton_id exists only in the table carton.
 
0
 

Author Comment

by:JDCam
ID: 22844475
let me clarify further.......
forget about the order by order_id, and need to reset on change of order_id. The application calls the view by order_id, thus reteiving cartons for only one order at a time.

It is a many to one relation. For each record in Order there may be multiple records in Carton.  If the view returns 7 rows (7 carton IDs) for a specific order_id, I need to number them 1 thru 7 accordingly.

Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

914 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now