On Wed, Sep 14, 2011 at 9:03 PM, Sam Carleton <scarle...@miltonstreet.com>wrote:
> Forgive me, fore I have forgotten the term used to describe the behavior if > a C if statement where it stops executing on the first false statement, > but... Does coalesce do that? > "Short-circuit evaluation" is the usual term applied to this kind of thing, and yes, COALESCE() does short-circuit evaluation. If you say "coalesce(A,B)" and A is not NULL than B is never evaluated, which can be a significant performance win if, for example, B is a complex subquery. > > I have to put together a query that has a coalesce such that if the row > from > the table is null, it then does a non-trival query to calculate the value > for the row: It is for an invoice total and will only be used while the > user is building the invoice. Once the invoice is finalized, the values > will be placed into the invoice row. The reason I need this logic is > because the prices of invoice items can change on the fly, each time the > user displays an invoice that is being built, it needs to show the current > prices/totals, thus it looks like this: > > select > coalesce( sub_total, (select round(sum( (select pli.PRICE from > PRICE_LIST_ITEM pli where pli.EVENT_PRICE_LIST_ID = (select ITEMVALUE from > DBLOOKUP where CATEGORY = 'SalesCenter' and ITEMNAME = 'PriceListId') AND > pli.PRODUCT_ID = PRODUCT_ID) * QTY) from EventDB.INVOICE_ITEM where > INVOICE_ID = @invoiceId)) as sub_total, > coalesce( tax, (select round(sum( (select pli.PRICE from PRICE_LIST_ITEM > pli where pli.EVENT_PRICE_LIST_ID = (select ITEMVALUE from DBLOOKUP where > CATEGORY = 'SalesCenter' and ITEMNAME = 'PriceListId') AND pli.PRODUCT_ID > = > PRODUCT_ID) * QTY) / (1 + (@tax / 100)) - .005, 2) * ( @tax / 100) + .005, > 2) from EventDB.INVOICE_ITEM where INVOICE_ID = @invoiceId)) as tax, > coalesce( total, (select round(sum( (select pli.PRICE from PRICE_LIST_ITEM > pli where pli.EVENT_PRICE_LIST_ID = (select ITEMVALUE from DBLOOKUP where > CATEGORY = 'SalesCenter' and ITEMNAME = 'PriceListId') AND pli.PRODUCT_ID > = > PRODUCT_ID) * QTY) / (1 + (@tax / 100)) - .005, 2) from > EventDB.INVOICE_ITEM where INVOICE_ID = @invoiceId)) as total > where > INVOICE_ID = @invoiceId > > I tend to favor creating larger select statements and letting the DB do the > work rather than adding lots of conditional code in my C code, so is this a > good time to break it into two statements or should I be fine with the > above > code? > > Sam > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
