SDAC optimization issue (ISNULL)
Posted: Mon 15 Nov 2004 09:10
Although this is not a bug it might cause some problems.
The following constuction won't work well in SDAC if you pass NULL to the parameter "param" (whicxh is varchar(xx)):
isnull(:Param,'Something') = 'Something'
if you pass NULL to param SDAC will optimize the SQL and pass it to SQL server as varchar(1) and ISNULL(Varchar(1),'Something') will return VARCHAR(1) which in this case will be 'S'. 'S' = 'Something' will then result to false.
The solution is to simply CAST :Param to VARCHAR(xx) where xx is your desired length and this will solve the problem.
This is not a bug report, just thought that it would be helpful.
The following constuction won't work well in SDAC if you pass NULL to the parameter "param" (whicxh is varchar(xx)):
isnull(:Param,'Something') = 'Something'
if you pass NULL to param SDAC will optimize the SQL and pass it to SQL server as varchar(1) and ISNULL(Varchar(1),'Something') will return VARCHAR(1) which in this case will be 'S'. 'S' = 'Something' will then result to false.
The solution is to simply CAST :Param to VARCHAR(xx) where xx is your desired length and this will solve the problem.
This is not a bug report, just thought that it would be helpful.