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?
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
