1 (edited by Step-in 2024-10-11 08:39:39)

Topic: SQL query for numerical values

Greetings to all. I have a button with SQL query. The problem is when to specify a search on a field with numbers.

WHERE                                            
    (
        ({Edit3} = '' OR PReg.PSum >= {Edit3}) AND
        ({Edit4} = '' OR PReg.PSum <= {Edit4})
    )

constant error that the syntax is not valid for MySQL.

i tried

(
        (IFNULL({Edit3}, '') = '' OR PReg.PSum >= IFNULL({Edit3}, 0))
        AND 
        (IFNULL({Edit4}, '') = '' OR PReg.PSum <= IFNULL({Edit4}, 999999999999))
    )

but the same thing.

Maybe someone has an example of a SQL query using a request for numeric (more and/or less), dates (more and/or less), checkboxes. Because when I don't have certain fields filled in on the search form, the request gives a constant error about incorrect syntax.

Or maybe it is better to do it through a script in the program to additionally control the conditions. But how is it correct to edit the selected record after filling the table?

Post's attachments

Attachment icon examp.zip 335.16 kb, 31 downloads since 2024-10-11 

Re: SQL query for numerical values

Your troubles are primarily due to a lack of understanding of how SQL queries are composed and how variables are passed to the MVD.
Here is a part of your query that demonstrates this.

AND (Pstr.id_Type = {ComboBox2} OR {ComboBox2} IS NULL)
 (
 ({Edit3} = '' OR Preg.PSum >= {Edit3})
 AND
 ({Edit4} = '' OR Preg.PSum <= {Edit4})
 )
 AND (DATE(PReg.PB) >= DATE({DateTimePicker1}) OR PReg.PB IS NULL)

There is an error in the WHERE conditions and the processing of MVD fields.
It is up to you to find the first error.
Let's try to understand what conditions you are trying to check and where the error is.

({Edit3} = '' OR PReg.PSum >= {Edit3}).

When executing your query, the system returns an error and you can see your query. The error indicates the first error before the bracket, and the second error can be seen by yourself. Here it is.

( = '' OR Preg.PSum >= )

Something is missing here.
The first part, if written correctly, will simply return true or false. How will this help your search? It doesn't. It is not an error, but just an unnecessary check.
The second part will help the search if written correctly.
The correct spelling of the second part will be PReg.PSum >= '{Edit3}'. This means that if Edit3 is empty, it will not pass anything to the query, and in the query you will get '' and not the emptiness that SQL complains about.
For these checks, it is better to use the SQL CASE function.
You will find examples on the Internet.
And you can find examples of use on the forum.
By the way, the query can also be made through a script. Look at the dbSQL property for TableGrid.