Updating a colum with serials using SQL 3

[Rajesh Karunakaran]

Dear Team,

I have an invoice line items table in which I want to update a column ('LineSeq') for an invoice (ie there could be multiple records in this table for a single invoice) with serials numbers from 1, ie 1,2,3,4... and so on till the last record for that invoice. Usually, we can do this using a loop.

However, is there a way to accomplish this using an SQL statement?
I think, there is a way in MSSQL. But, what about visual foxpro?

Thanks in advance
Rajesh

 

[Mike Lewis]

An interesting question. Off-hand, I can't think of a solution. But there are people in the forum who are cleverer than me, so with luck someone will have a suggestion.

One thought: Do you require the serial numbers to always start from 1 and to always be consecutive? Or is it enough for each number to be unique within the table? If the latter, you could simply set the number to equal the record number:

[tt]UPDATE Invoices SET Serial = RECNO()[/tt]

But I can see that that probably wouldn't meet your needs.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Rajesh Karunakaran]

Thank you for your thoughts dear Mike.

Yes, basically we want the number to start from 1.

Generally, RECNO() should help as the column is used to save in which order the records were added in the invoice and to display them later in the same sequence. But sometimes, the sequence need to be generated according to an index tag. So, RECNO() may not be an option
for me.

This has not become a show stopper. I know we can do this with a loop.
However, I am curious to know if there is way through SQL. That will help me at a time when I need to re-write the application in Dot Net platform.

Rajesh

 

[Olaf Doschke]

There are no such things as MSSQL ROW_NUMBER() OVER PARTITION BY... ORDER BY... in VFP SQL. I also don't see a way to do this with SQL, but even if, this mechanism already depends on being able to define an order of line items, a line sequence, to start with (the ORDER BY clause of the T-SQL partitioning). So it's unfortunate if you don't set these LineSeq numbers to start with.

I would perhaps [tt]SELECT RECNO() as iRecno FROM invoicelineitems WHERE invoiceid=x INTO Cursor crsLineitemrecords ORDER BY 1 ASC[/tt] and then visit these record numbers via GOTO RECORD in the given order to set the LineSeq from 1 to N. You could also SET ORDER TO the index on invoiceid in lineitems (which you should have for referential integrity rule of foreignkey constraint anyway), then SET KEY TO the invoiceid and scan through all related records to set their LineSeq. That very likely also scans the records in recno() order.

Bye, Olaf.

 

[Rajesh Karunakaran]

Thank you Olaf.
So, there seems to be no direct way in VFP.
Still, it's not a problem for me to go the LOOP way which is decent, straight and simple!

Rajesh

 

[Olaf Doschke]

When you give each record an id, which you should, you could also order lineitems by it, though it makes this depend on integer keys rather than random keys.

When you do such numbering in MSSQL you'd use a sequence, if not handling this outside of the database in the creation of invoice lineitems by the client or business logic. Needing to post process numbering is a bad concept itself. Notice the Row_Number() mechanism in T-SQL also doesn't attach that number permanently on each lineitem, it's always doing a temporal numbering just valid in the query result. As said it depends on something else to order by anyway.

I can understand you want a permanent LineSeq number if customers should be able to refer to a line item by its number and you want to uniquely identify this lineitem by the invoice number and LineSeq, so it's not a wrong demand, but it should be assigned while creating line items.

If your user interface allows editing of invoices before they go into the state of finally being sent to customers, you could have to rearrange line items or insert them not just at last position, but then it's all done in loaded data, which in case of VFP is typically a cursor. It's okay to do that without SQL even eyeing with the aspect of being able to migrate data into a non VFP DBF/DBC format because as long as VFP is used as frontend you have that data in cursors when you work on the frontend with it.

A recommendation to do things the SQL way is not strongly necessary at this level. It's only a culprit when you do such things in the data access and data persisting layer of your application.

In PHP for example, you'd read data into associated arrays and then also work on them with array addressing, not with SQL, so the native ways to access already loaded data are always okay, don't frustrate yourself by only allowing use of SQL in a stage you already have done the main transfer and decoupling from backend to frontend, even when you have a three-tier architecture in mind and want to be able to dynamically switch layers like the database used or the frontend used.

Bye, Olaf.

 

[TamarGranor]

You can do it with a query that has subqueries, something like this:

[pre]
UPDATE LineItems ;
SET LineSeq = OrderedItems.nLineNo ;
FROM (SELECT RECNO() AS nLineNo, iPK ;
FROM (SELECT iPK, RECNO() as nSeq FROM LineItems ;
WHERE iInvPK = m.iTheInvoicePK
ORDER BY nSeq) InvItems) OrderedItems ;
WHERE LineItems.iPK = OrderedItems.iPK
[/pre]

Tamar

 

[Rajesh Karunakaran]

Thank you very much Tamar. I will check this soon.
Btw, by 'iPK' you mean whatever Primary Key expression I have in the table? Am I correct?

Rajesh

 

[Olaf Doschke]

Impressive in the ways it makes use of subqueries and recno() of them.

Still, I'd prefer to do things in time.

1. Have a form for editing an invoice/listing the invoice line items, which are loaded (buffered) into a cursor crsLineItems
2. Before saving crsLineItems into the database set all LinSeq from 1 to RECCOUNT() in display order, which means in whatever order crsLineItems is currently.

LOCAL lnLinSeq
lnLinSeq = 1
SELECT crsLineItems
SCAN
   IF LinSeq <> m.lnLinSeq
      REPLACE LinSeq WITH m.lnLinSeq 
   ENDIF
   lnLinSeq =  m.lnLinSeq +1
ENDSCAN

Then do the TableUpdate().

Because your crsLineItems only has line items of the currently created/edited invoice, you don't need any complicated SQL.

You just have to know in buffering mode new records have negative RECNO(), so you can't just use RECNO() as LinSeq and do REPLACE ALL LinSeq WITH RECNO() FOR LinSeq<>RECNO(), you have to take a simple counter variable and use a scan loop.

If you wanted to use Tamars SQL on the crsLineItems loaded buffered, you have to take special care to make use of VFP9s WITH (buffering=.t.) clause, to go through all records of crsLineItems. So Tamars update-SQL only is good for the sequential numbering after the fact of storing line items, or for populating LinSeq after introducing the LinSeq column.

Bye, Olaf.

 

[Olaf Doschke]

If you establish a database table rule for uniqueness of (invoiceid,linseq), to never have double line sequence numbers for an invoice stored, even not just temporary, you get an update problem.

When the table has stored an invoice with two line items and you introduce a third in the editing, but sort that third item into second position, you could get in trouble, if you save the new record with LinSeq=2, before updating the old line item with LinSeq=2 to its new LinSeq=3 position.

The solution for that is to store negative LinSeq numbers with each save and then do an UPDATE turning all negative LinSeq positive in an AfterSave method. That way you never have an intermediate situation with double LinSeq. Also, even if that crashes in the wrong moment you know how to interpret negative LinSeq as their ABS() value and if LineItems of an invoice have both positive and negative LinSeq know that invoice was in an intermediate state when your application or network or file server crashed.

Bye, Olaf.

 

[TamarGranor]

Yes, iPK means whatever your primary key is.

Tamar