Re: NULLs in primary keys
Andy,
The following is what I use when I want to fields to be equal OR
both fields to be null. This same select should work in both cases.
I belive this will work in SPL, 4GL and SQL.
SELECT things
FROM charges_table
WHERE service_type = p_service_type
AND ( cost_centre = p_cost_centre
OR ( cost_centre is NULL AND p_cost_centre s NULL ))
Both cost_center and p_service_type must be equal or both must
be NULL. I hope this helps.
Regards - Lester
} akent@cix.compulink.co.uk (Andy Kent) said:
} 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)
} -------------------------------------
}
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Grant group privileges for Informix databases with DB Privileges #
#############################################################################