Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Unanswered: Help needed on update statement

    Hi all, new to this so go easy on me!

    Trying to update an invoice header record using the sum of the invoice lines but the only way I can get it to work is as follows:-

    update invoice i set
    i.Net =(select sum(isnull(il.Net,0)) from invoicelines il where invno = @InvNo),
    i.VAT=(select sum(isnull(il.VAT,0)) from invoicelines il where invno = @InvNo),
    i.CalcVAT=(select sum(isnull(il.VAT,0)) from invoicelines il where invno = @InvNo),
    i.Gross=(select sum(isnull(il.Gross,0)) from invoicelines il where invno = @InvNo)
    where i.invno = @InvNo

    Can anyone shed some light as to how I can come up with a more elegant solution or is this the only way I can do it?

    Cheers
    Dave

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You say it works. Which Sybase product?
    update invoice i set ...
    on ASE: Incorrect syntax near 'i'

  3. #3
    Join Date
    Feb 2009
    Posts
    3
    Product is ASA 9. This is the current version and yes, it works.

    update invoice i set
    i.Net =(select sum(isnull(il.Net,0)) from invoicelines il where il.invno = i.InvNo),
    i.VAT=(select sum(isnull(il.VAT,0)) from invoicelines il where il.invno = i.InvNo),
    i.CalcVAT=(select sum(isnull(il.VAT,0)) from invoicelines il where il.invno = i.InvNo),
    i.Gross=(select sum(isnull(il.Gross,0)) from invoicelines il where il.invno = i.InvNo)
    where i.invno = @InvNo

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Wouldn't you do better just having a view? Something a bit like :

    Code:
    create view invoice
       (InvNo, Net, VAT, CalcVAT, Gross) 
    as select     InvNo,
                  sum( isnull( Net,0 ) ),
                  sum( isnull( VAT,0 ) ),
                  sum( isnull( CalcVAT,0 ) ),
                  sum( isnull( Gross,0 ) ),
       from   invoicelines
       group by InvNo
    This way you don't have to calculate the figures before reading them and you'll never get differences between your totals and the data that makes up the total.

    A better naming standard would also be worth considering ie invoicelines could be InvoiceLines. It would also be better if your database/coding standardised the field names as well ie invno and InvNo.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Use the from clause.
    You might have to join to an actual view if asa doesn't allow a derived table
    Untested - I don't have ASA
    Code:
    update invoice set 
    Net=view1.Net,
    VAT=view1.VAT,
    CalcVAT=view1.CalcVAT,
    Gross=view1.Gross,
    from invoice i 
    join 
    (select   InvNo,
                sum( isnull( Net,0 ) ),
                sum( isnull( VAT,0 ) ),
                sum( isnull( CalcVAT,0 ) ),
                sum( isnull( Gross,0 ) )
       from   invoicelines
       group by InvNo
    )view1(InvNo, Net, VAT, CalcVAT, Gross)
      on i.InvNo=view1.InvNo

  6. #6
    Join Date
    Feb 2009
    Posts
    3
    Thanks guys

    I like the idea of a view but what are the pros and cons? I'm not sure I understand ".... you'll never get differences between your totals and the data that makes up the total." Can you shed some light please? Sorry to be a bit dense, am I missing something obvious!

  7. #7
    Join Date
    Feb 2009
    Posts
    19

    Sybase SQL Anywhere

    What are the advantages of using sybase sql anywhere over other database systems

  8. #8
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by r1dave
    I like the idea of a view but what are the pros and cons?
    Once you've created the view then you can see the totals for any invoice simply by selecting from the view ie you don't need to update anything to get your totals. Because the data is calculated on the fly then the totals will always be correct while if an invoice line gets added after you call the update then your total will be wrong. It may be marginally slower but I doubt you'll notice any difference. You could also just select the same data from the original table directly and not bother with an update or a view but I guess you want easy access to these totals.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •