Which to use : Multiple Joins or a Cursor ? 
Author Message
 Which to use : Multiple Joins or a Cursor ?

Hi,
I am relatively new to sql and currently using ASA 8.  I need to convert an
internal program orientated update to a sql stored procedure.

The update involves reading all the rows from a table (DisburseTable -
defined Global Temporary) and updating a number of columns in 2 other tables
(Debtors and Invoice) for each row in the first table (DisburseTable).  I
expect there will only ever be a small number of rows in the DisburseTable
table (<50 and usually < 20).

The question is, should I use multiple joins and updates or use a cursor to
process the DisburseTable.  The 2 concerns I have are for efficiency and
readability. For readability it seems a cursor would allow a neater layout
but I assume would have a greater overhead ?  The update statement using
multiple joins looks like this :

    Update Debtors Join Invoice Join DisburseTable on Debtors.Sysid =
    Invoice.Deb_SysId And Invoice.InvoiceNo = DisburseTable.InvoiceNo
    Set Debtors.BalanceCurrent = (If INVoice.Age = 0 Then
    Debtors.BalanceCurrent - (DisburseTable.Received + (If Invoice.BalanceOS
    - DisburseTable.Received < 0 Then  Invoice.BalanceOS -
    DisburseTable.Received  Else 0 EndIf)) Else Debtors.BalanceCurrent EndIf
    ),
    Debtors.BalanceAged1 = (If INVoice.Age = 1 Then Debtors.BalanceAged1-
    (DisburseTable.Received + (If Invoice.BalanceOS - DisburseTable.Received
    < 0 Then  Invoice.BalanceOS - DisburseTable.Received  Else 0 EndIf))
    Else Debtors.BalanceAged1 EndIf ),
    Debtors.BalanceAged2 = (If INVoice.Age = 2 Then Debtors.BalanceAged2-
    (DisburseTable.Received + (If Invoice.BalanceOS - DisburseTable.Received
    < 0 Then  Invoice.BalanceOS - DisburseTable.Received  Else 0 EndIf))
    Else Debtors.BalanceAged2 EndIf ),
    Debtors.BalanceAged3 = (If INVoice.Age > 2 Then Debtors.BalanceAged3-
    (DisburseTable.Received + (If Invoice.BalanceOS - DisburseTable.Received
    < 0 Then  Invoice.BalanceOS - DisburseTable.Received  Else 0 EndIf))
    Else Debtors.BalanceAged3 EndIf ),
    Debtors.BalanceTtl = Debtors.BalanceTtl - (DisburseTable.Received + (If
    Invoice.BalanceOS - DisburseTable.Received < 0 Then  Invoice.BalanceOS -
    DisburseTable.Received  Else 0 EndIf)),
    Debtors.MTD = Debtors.MTD - (DisburseTable.Received + (If
    Invoice.BalanceOS - DisburseTable.Received < 0 Then  Invoice.BalanceOS -
    DisburseTable.Received  Else 0 EndIf)),
    Debtors.YTD = Debtors.YTD - (DisburseTable.Received + (If
    Invoice.BalanceOS - DisburseTable.Received < 0 Then  Invoice.BalanceOS -
    DisburseTable.Received  Else 0 EndIf)),
    Invoice.CrNoteAmt = Invoice.CrNoteAmt +DisburseTable.Received +( If
    Invoice.BalanceOS - DisburseTable.Received < 0 Then  Invoice.BalanceOS -
    DisburseTable.Received  Else 0 EndIf),
    Invoice.BalanceOS = (If Invoice.BalanceOS - DisburseTable.Received < 0
    Then  0 Else Invoice.BalanceOS - DisburseTable.Received EndIf);

As you can see it is quite a convoluted statement and I am not sure if this
is the norm for sql.
In this statement I would also like to add other tables which would also
need to be updated. Is there a limit ?

Other tables would also need records inserted based on the rows in the
DisburseTable.  So I imagine the DisburseTable would need to be read at
least another time.

Apologies for the length and complexity of the question but the sql books
don't seem to cover this.

Any help appreciated.

Regards,
Paul P.



Tue, 16 Nov 2004 09:05:23 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Joining multiple queries into a single cursor

2. Joining multiple queries into a single cursor

3. Question:Updates to joined tables using cursor in v6.0

4. multiple inner joins using derived tables

5. update using join on multiple tables

6. querying using multiple joins with tables of size 200M+ records

7. Multiple Outer Joins using MS Jet SQL

8. Multiple Outer Joins using MS Jet SQL

9. Join Multiple Databases' table using TQuery

10. Using PL/SQL to collect data from multiple tables and returning this as a cursor (resultset)

11. SELECT Multiple Rows of a Table INTO a PL/Sql TableRecord Without Using Cursor

12. Joining Tables from Multiple Databases using ADO


 
Powered by phpBB® Forum Software