SQL of select object in edit form

[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

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

[luca.ongaro]

Easy...
Thank you kev1n, I love nuBuilder

[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

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

[luca.ongaro]