Re: NULLs in primary keys

Andy Kent (akent@cix.compulink.co.uk) wrote: : My customer has a table of services charges, keyed on the type of service : and the cost centre. Some services have different charges for each cost : centre, others are global. At present the global ones have a cost centre : of NULL. : I have a stored procedure containing a query something like: : SELECT things : FROM charges_table : WHERE service_type = p_service_type : AND cost_centre = p_cost_centre : If the row we're after has a NULL cost_centre and therefore p_cost_centre : has been set NULL, the row is never found. The client has OnLine 5.00. : Now I can understand why nulls in a primary key are generally not a very : nice idea, but this must be a common enough business requirement. : Questions: does anyone know off the top of their heads whether this would : work in 4GL (which they don't have), ESQL/C or a later version of SPL? : Has anyone had experience of getting around a similar problem? (I've : tried getting them to use blank instead of null in the interim and this : works but I don't like it) : Thanks in anticipation. : akent@cix.compulink.co.uk (Andy Kent) : ------------------------------------- Yeah, this is normal behavior for Informix products. NULL is treated like FALSE in an SQL predicate. You could test explicitly for NULL: SELECT things FROM charges_table WHERE service_type = p_service_type AND (cost_centre = p_cost_centre OR cost_centre IS NULL) I did something very similar once and spent what seemed like days arguing about why NULL's are good/bad/whatever. There are very strong opinions out there about NULL. One person insisted that we use a magic value like "XX" to represent "any" instead of NULL. I couldn't see how that improved the situation. It bothers me that Informix treats "blank" differently than NULL. For one thing, you can't see the difference when you PRINT or DISPLAY them but they have very different effects on a query. If I had my way there would be no blank character variables other than NULL. -- Jeff Sturm