SQL WHERE statement optional

[Uzlander]

Hi people! Probably this is a beginner level question but i couldn't make the WHERE statement optional.
Context: The second (select type )input gets filtered based on the first input, but if the first left blank (empty|null|undefined) then no filter for the second input should work.
So

Code: Select all

  WHERE
    ((rblocks.kompleks = '#KOMPLEKS#'))

clause somehow doesn't make it really optional and filter's working anyway, whereas i need WHERE eliminated if '#KOMPLEKS#' equals null and all records should show up

[kev1n]

Maybe like this?

Code: Select all

WHERE (
   IFNULL('#KOMPLEKS#','') = ''
   OR LEFT('#KOMPLEKS#',1) = '#'
   OR rblocks.kompleks = '#KOMPLEKS#'
)

Explanation line by line

1. IFNULL('#KOMPLEKS#','') = ''
    – If #KOMPLEKS# is NULL or empty, the condition is true.
    – Meaning: Don’t filter, show all records.
2. LEFT('#KOMPLEKS#',1) = '#'
    – If the first character of #KOMPLEKS# is #, the condition is true.
    – This usually indicates the hash cookie placeholder wasn’t replaced.
    – Meaning: Ignore filter, show all records.
3. rblocks.kompleks = '#KOMPLEKS#'
    – If #KOMPLEKS# contains a valid value, only rows where rblocks.kompleks matches it will be returned.