Jyoti Seth wrote:
Hi,
I have a the following procedure
CREATE OR REPLACE FUNCTION getfunctionaries(p_statecd integer)
SELECT f.functionaryid, f.category,f.description
FROM functionaries f
where f.statecd=p_statecd
In the functionaries table statecd is a null field. When I pass some integer
value to the above procedure it works correctly but if I pass null value in
p_statecd it doesn't show anything whereas it has values and if I write the
select statement separately it gives values
You can't be getting results from a query WHERE statecd = NULL, because
NULL = NULL returns NULL, which is equivalent to false in a WHERE clause.
If you want to check for NULL you need to use statecd IS NULL, but if
you are treating it like a value then you're using it incorrectly.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
