SQL WHERE statement optional Topic is solved

[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.

[Uzlander]

I think there's a problem with retrieving records thus priorly saved. I mean having saved the record, then reopen it (either browse or edit purposes) it just replaces the aforesaved value with the first one it manages to get from the database field, cos (seemingly) there is no #KOMPLEKS# hash exists at that moment. So effectively the value is lost.
And i need to retrieve the existing value if there was any saved before, and only in case the field's originally empty or i'm dealing with new record it should fire the WHERE filter above.
I can publish short screencast if needed..

[steven]

Uzlander,

If you break the SQL statement on purpose eg. 1WHERE ......

You will get an error in nuDebug and it will show the actual SQL that is being run with its WHERE clause.


Steven

[Uzlander]

This is not about an error. I basically want to implement dependent select lists (dropdown select - feels fitting). Using conditional WHERE statement in SQL i get the second list filtered if HasgCookie's set, and the whole stack if no HasCookie.
I played a bit and figured it does its job good until saved, but after re-opening the same record it doesn't always display what's expected.
https://jumpshare.com/share/7A0SuAOT5FNzr0Wp2y4v

[Uzlander]

So the first two elements are select dropdowns (the second one depends on the first) and the third one is display type, which in turn doesn't behave consistently. The Link -> https://i.postimg.cc/vBYBK7Jy/dogov-draft2.jpg
I can post an sql database backup if required, there's no real data in it yet

[kev1n]

Please post your db dump.

[Uzlander]

Dump/backup attach

So i think it boils down to simpler question: what is the recommended way to implement dependent select fields (either lookup or dropdown), i couldn't find it in the docs ?
Thanks a lot !:)

[kev1n]

In your second select object (dogov_kvart), you can simply reference the first one like this:

Code: Select all

SELECT
 kvart.kvart_hid, 
 UPPER(kvart.kvart_hid)
FROM
    kvart
WHERE
    (
      (LEFT('#dogov_blok#',1) = '#') 
      OR 
      (kvart.blok_dd = '#dogov_blok#')
)

I don't see the need to set a separate hash cookie in dogov_blok.


There’s also a demo showing this approach here:
https://demo.nubuilder.cloud/

[Uzlander]

Oh, before i check the code above in action, how is it gonna work? I mean there isn't such HashCookie as '#dogov_blok#' user set, or does it work as the actual field's value holder variable by default ?