Menu
I have a stored procedure with a parameter name which I want to use in a where clause to match the value of a column i.e. something like
where col1 = name
Now of course this fails to match null to null because of the way null works. Do I need to do
where ((name is null and col1 is null) or col1 = name)
in situations like this or is there a more concise way of doing it?
881
You can't set impromptu to use a NULL value as the default prompt value, but you can work around this issue by setting the default value to a nonsensical value that would not be entered by a user normally, and then modfiying the stored procedure to change the parameter to NULL if it encounters that nonsensical value.
The following three rules hold for null values: A null is never equal to anything else. None of the following IF statements can ever evaluate to TRUE: my_string := ' '; IF my_string = NULL THEN ...--This will never be true. max_salary := 0; IF max_salary = NULL THEN ...--This will never be true.
You can use the NVL function to return a value when a null occurs. For example, the expression NVL(commission_pct,0) returns 0 if commission_pct is null or the value of commission_pct if it is not null.
You can use decode function in the following fashion:
where decode(col1, name, 0) is not null
Cite from SQL reference:
In a DECODE function, Oracle considers two nulls to be equivalent.
133
I think your own suggestion is the best way to do it.
44
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
