Re: NULLs in primary keys

->Subject: NULLs in primary keys ->Date: Thu, 15 Sep 1994 21:10:08 GMT ->Reply-To: akent@cix.compulink.co.uk ("Andy Kent") ->Organization: Andy Kent Associates Limited -> ->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. Rewrite your query as: SELECT things FROM charges_table WHERE service_type = p_service_type AND ( cost_centre = p_cost_centre OR cost_centre IS NULL ) -> ->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) Actually, this is the correct approach. My preference would be to use "Common" or "General" in place of blank in the cost_centre, but the important thing is to have an actual, unique value for the primary key. ->Thanks in anticipation. -> ->akent@cix.compulink.co.uk (Andy Kent) ->------------------------------------- Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\