I'm using the following where condition in a fixed sql query:
and ([NC_DATA].WORK_CENTER_BO LIKE '%[Param.4]%' OR '[Param.4]' IS NULL)
and its working fine when I enter a value for Param.4, but when I don't use Param.4 it's not giving the NULL paramter only data with values. How can I achieve that I get data with values and also data = NULL
Thanks for helping!
What I tried:
and ([NC_DATA].WORK_CENTER_BO LIKE '%[Param.4]%' OR '[Param.4]' IS NULL)
I get:
| Column 1 | WORK_CENTER_BO |
|---|---|
| ABC | 123 |
| DEF | 456 |
I expecting:
| Column 1 | WORK_CENTER_BO |
|---|---|
| ABC | 123 |
| DEF | 456 |
| GHI | NULL |
CodePudding user response：
You can try using the COALESCE function as follows:
and (COALESCE([NC_DATA].WORK_CENTER_BO, '[Param.4]') LIKE '%[Param.4]%')
If your "WORK_CENTER_BO" is null, it will be replaced with [Param.4] to match the condition.
CodePudding user response：
You could try like this:
AND (([NC_DATA].WORK_CENTER_BO LIKE '%'||NVL('[Param.4]',[NC_DATA].WORK_CENTER_BO)||'%') OR [NC_DATA].WORK_CENTER_BO IS NULL)
a rule of a thumb: All comparisons with NULL resolve to false.