Page 1 of 1
SQL of select object in edit form
Posted: Tue May 02, 2023 6:27 pm
by luca.ongaro
Hi,
is it possible to change the SQL code of a select object of an edit form, according if it is a new record or an existing one?
If yes, which is the best way?
Thanx
Re: SQL of select object in edit form
Posted: Wed May 03, 2023 2:03 am
by kev1n
Hi,
Depending on how different the SQL statements are, here are two possibilities.
1. Using UNION:
Code: Select all
SELECT some_column FROM your_table WHERE '#RECORD_ID#' = '-1'
UNION
SELECT another_column FROM your_table WHERE '#RECORD_ID#' <> '-1'
In the first SQL statement, a UNION is used to combine the result sets of two SELECT statements. The first SELECT statement returns the value of the 'some_column' where '#RECORD_ID#' is equal to '-1'. The second SELECT statement returns the value of the 'another_column' where '#RECORD_ID#' is not equal to '-1'. This method can be useful when the two SELECT statements are significantly different and cannot be combined using an IF statement.
2. USING iF:
Code: Select all
SELECT IF('#RECORD_ID#' = '-1', `some_column`, `another_column`) FROM your_table
In the second SQL statement, an IF statement is used to determine which column to select based on the value of '#RECORD_ID#'. If '#RECORD_ID#' is equal to '-1', then 'some_column' is selected, and if it is not equal to '-1', then 'another_column' is selected. This method can be useful when the two columns are very similar, and it is more efficient to use an IF statement rather than two separate SELECT statements.
#RECORD_ID# is a Hash Cookie and will be replaced with the current record ID. -1 means it's a new record.
Re: SQL of select object in edit form
Posted: Wed May 03, 2023 8:35 am
by luca.ongaro
Easy...
Thank you kev1n, I love nuBuilder
Re: SQL of select object in edit form
Posted: Wed May 03, 2023 9:57 am
by luca.ongaro
Update.
I found another working solution, using:
Code: Select all
CASE WHEN '#RECORD_ID#' = '-1'
THEN things to do if it is a new record
ELSE things to do if it is an existing record
END
Re: SQL of select object in edit form
Posted: Wed May 03, 2023 10:00 am
by kev1n
Yes, that works as well.
In MySQL, both CASE WHEN and IF() are used to conditionally execute code. However, they differ in syntax and usage:
CASE WHEN is used for complex conditional expressions with multiple conditions. It is similar to a switch statement in other programming languages.
while IF() is used for simple conditional expressions with only two outcomes.
Re: SQL of select object in edit form
Posted: Wed May 03, 2023 10:08 am
by luca.ongaro
