Re: NULL problem
On Feb 24, 5:10pm, Ti Lian Hwang wrote:
} Subject: NULL problem
} {
} Anyone knows how to resolve this ?
}
} If the value of b in the
}
} select * from test where a = b
}
} is a NULL , the select statment will not find anything, even though the
} relevent record exists. See example below.
}
} The select works if
}
} select * from test where a is NULL
}
} But, if the value of b is a variable in a 4GL, you can't code everything as
}
} IF b is null then
} select statment 1
} else
} select statment 2
} end if
}
} Is this a bug or a feature of 4GL :-)
} }
} ----------------------------------
} Ti Lian Hwang - DHL Singapore
} email : tilh@sin-co.sin-ro.dhl.com
} ----------------------------------
} }
}-- End of excerpt from Ti Lian Hwang
Try
select * from test where (b is not null and a is not null and a = b)
or (a is null and b is null)
this may give a performance problem due to the use of the OR statement in the
query. If it does it can be rephrased as a UNION for example
select * from test where (b is not null and a is not null and a = b)
UNION
select * from test where (a is null and b is null)
which should cure much of the peformance issue.
Cheers - Jim
--
-----------------------------------------------------------------------------
Jim Gordon DHL Airways Inc. jgordon@us.dhl.com
-----------------------------------------------------------------------------