At 10:35 AM 3/10/2009, Myles Wakeham wrote:

>I have a query like this:
>
>SELECT SUM(INVOICE.TOT_OWING)
> FROM INVOICE
> WHERE INVOICE.FK_ACCOUNT_ID = :A_ACCOUNT_ID AND
> (((:V_CURRENT_DATE - INVOICE.INVOICE_DATE) >=31) AND ((CAST('NOW' AS DATE) - INVOICE.INVOICE_DATE) <=60)) AND
> INVOICE.IS_PROCESSED = 'Y'
> INTO :V_30_INV_TOTAL;
>
>It works great when there is data in the range that is being searched. But when there is no data in the range, rather than returning a 0 (being the sum of nothing), its returning NULL.

There is no such thing as the "sum of nothing".

A SUM() operation will always return zero of the appropriate data type *if* it has a set to work on: one or more rows where the targeted field is null throughout the set. You can depend on that. But a set like that is NOT the same a a set that doesn't exist. Sum() (and other aggregation operations) are just no-op if there is no set to operate on.

>Unfortunately this query (and others like it) are run in a larger stored procedure, and the results are there added together, which is causing a problem trying to add anything to NULL.

Pay more attention to the effects of NULLs and code to cover all of the cases. Mechanisms like initialising variables and applying COALESCE to results are available to you. Be careful though. When NULL means literally that there is *no* result, be sure that you fully understand the implications of using COALESCE (or even a straight assignment) to treat one fact (the state of a set, in this case) as though it were a numeric value.

>So I need to force a Decimal(15,2) return type from this

You can't cast or coalesce something that doesn't exist. You *can* test your INTO variable, though, and assign to it. Local variables and output parameters always start NULL at the beginning of the procedure (unless they are declared with a starting value) and stay null until they are assigned to. Then, they keep the last value they had until assigned to again. They never bounce magically back to the starting value.

Always control the before and after values of ALL your variables, including those that will pass back to the client as output parameters. After the SELECT, test your variables and figure out what, if anything, happened. Based on that, you can assign a value (or not) before proceeding.

Graphical components and UI admin tools can trap us into thinking that an "empty result set" (a row of blank fields in a grid or "dataset" structure) is a row of nulls. Of course it's no such thing, but such perceptions can delude us when we visit PSQL-Land.

./heLen