Informix Forum

Re: Why does this set values to null?

Posted By: Jonathan Leffler
Date: Sunday, 26 March 2006, at 3:57 a.m.

On 3/25/06, informix-forum@iiug.org wrote:
> #77: Why does this set values to null? (JOHN SAYRE)
> <http://www.iiug.org/forums/informix-forum/index.cgi?rev=77>

>
> I am attempting to add current day information to ytd and mtd fields in a
> table of customers. I have defaulted the numeric fields to 0 in the table
> definition.
>
> If I run query one, those customers with sales that day will have values in
> the
> ty_sls field. But this will update the rest of the customers who have no sales
> that day back to null
>
> update loy_cust_report set ty_sls = nvl(ty_sls,0) + ((select
> nvl(sum(transaction_amount),0)
> from loy_trans_header
> where loy_cust_report.customer_id = loy_trans_header.customer_id
> and date(loy_trans_header.transaction_date) = today - 1
> group by loy_cust_report.customer_id))
> ;
>
> This will flip-flop and return the nulls to legitimate values and make any
> custs with no sales two days ago null
[...]
> As far as I can see, the query should only find the customers where there is a
> match between the two tables on the day and ignore the rest.
>
> What am I missing?

A WHERE clause on the UPDATE, as opposed to the sub-select.

You didn't specify which rows you wanted updated, so it applies the
update to every row in the table - as is routine and expected. For
those where the sub-select does not find matching data, you get null.
It's a standard trap, in both senses of the term: people fall into it
regularly, and it is the behaviour required by the SQL standard.

So, add a WHERE clause to the UPDATE that identifies correctly those
rows that need to be updated. Something along the lines of:

WHERE customer.id IN (SELECT h,customer_id FROM loy_trans_header AS h

WHERE DATE(h.transaction_date) = TODAY - 1)

I'm left wondering what the type of
'loy_trans_header.transaction_date' is that you need to convert it to
DATE; I guess it is a DATETIME field that includes a time component.
The code also gives you problems if you ever have a machine crash that
means you miss one day's processing and you need to retrospectively
run the update. I'd look to replace the TODAY - 1 part with a
reference date as a parameter, and set that date parameter to TODAY -
1 for the normal case, but to whatever value was necessary to run it
retrospectively.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

Informix Forum is maintained by Administrator with WebBBS 5.12.